Regarding a sample query

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • cspek

    Regarding a sample query

    Hello,
    I am a very complex problem in front of me. Kindly help me out

    in acheiving the same.

    Say I have a table called InfoName with two columns Name and ID

    InfoName

    Name ID

    OS 1
    SP 2
    Driver 3
    fasdf **
    ** ***
    ** ****

    (I AM INTERESTED IN ONLY FIRST THREE ROWS )

    I have another table Infotxt which uses the ID of InfoName as

    foreign key. It stores the value of this ID as shown

    InFotxt

    ID Value UnitNAME

    1 Win 2000 raj
    2 SP 4 raj
    3 40 GB raj

    1 Win xp jay
    2 SP 2 jay
    3 20 GB jay

    NOw I need to present it with unitname's configuration of OS,

    Sp and disk capacity like below.

    name OS SP Drive
    Raj win2000 sp4 40 GB
    Jay winxp sp2 2o GB

    That is, the rows of the InfoName table (first 3 rows) should

    be the columns of my resultant query.

    How can I achieve the same.
    Please give me some ideas, and if the question is silly, I am

    very sorry, because I am new to database queries...

    Thanks,
    cspek


    cspek

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
  • David Portas

    #2
    Re: Regarding a sample query

    Try this:

    SELECT unitname,
    MIN(CASE WHEN id = 1 THEN value END) AS os,
    MIN(CASE WHEN id = 2 THEN value END) AS sp,
    MIN(CASE WHEN id = 3 THEN value END) AS drive
    FROM InfoText
    WHERE id BETWEEN 1 AND 3
    GROUP BY unitname

    You have to be more specific than "first three rows". Understand that
    tables in SQL are not ordered. There is no fixed concept of a first,
    second or Nth row.

    This is called a cross-tab report. There are other solutions for
    producing cross-tabs dynamically in SQL Server but many people would
    say that you should do this instead in your client application or
    reporting tool. See:



    --
    David Portas
    SQL Server MVP
    --

    Comment

    • cspek

      #3
      Re: Regarding a sample query


      Hello,
      Thanks...Wil look into it...

      cspek

      *** Sent via Developersdex http://www.developersdex.com ***
      Don't just participate in USENET...get rewarded for it!

      Comment

      Working...