Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2003
    Posts
    33

    Unanswered: How to swap rows for columns?

    Hi,

    I need to re-org a result set so that column A is still the row number but so that the new column names are date, fname,lname,receipt, and transnum . Is there an easy way to do this in SQL?

    Code:
    A           B               C
    --------- ------------- -------------
    1          date           10/10/08
    1          fname          jon
    1          lname          doe
    1          receipt        99999
    1          transnum       123
    55         date           10/10/04
    55         fname          allen
    55         lname          smith
    55         transnum       345
    121        date           10/2/08
    121        fname          sandra
    121        lname          adams
    121        receipt        99998
    121        transnum       5465
    Transformed to:

    Code:
    1     10/10/08   jon    doe    99999    123
    55    10/10/04   allen  smith  NULL     345
    121   10/2/08    sandra adams  99998    5465
    Last edited by Pat Phelan; 07-13-09 at 17:27. Reason: Added code blocks to improve formatting

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My first thought is that this kind of manipulation needs to be performed on the client, not on the SQL Server.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    mozkill, Here is one way:
    Code:
    SELECT A
         , CASE B WHEN 'date'     THEN B ELSE ' ' END AS DATE_COL
         , CASE B WHEN 'fname'    THEN B ELSE ' ' END AS FNAME
         , CASE B WHEN 'lname'    THEN B ELSE ' ' END AS LNAME
         , CASE B WHEN 'receipt'  THEN B ELSE ' ' END AS RECEIPT
         , CASE B WHEN 'transnum' THEN B ELSE ' ' END AS TRANSNUM
    FROM table-name
    GROUP BY A
    Since your orginal column C was a Char (or Varchar) column, I left all the results as the same datatype.

    It may be possible to change the datatype of the output to match the actual data.

  4. #4
    Join Date
    Feb 2003
    Posts
    33
    Thank you very much. Without your help I wouldn't have figured this out.

    Stealth DBA, that solution didn't work for me but it helped :-)


    I solved it with this query (SQL Server 2008) :
    Code:
    SELECT DISTINCT A,
             (SELECT     C
               FROM          TEST AS a1
               WHERE      (a1.A = TEST.A) AND (B = 'date')) AS thedate,
             (SELECT     C
               FROM          TEST AS a2
               WHERE      (a2.A = TEST.A) AND (B = 'lname')) AS last,
              (SELECT     C
               FROM          TEST AS a3
               WHERE      (a3.A = TEST.A) AND (B = 'fname')) AS first,
               (SELECT     C
              FROM          TEST AS a4
               WHERE      (a4.A = TEST.A) AND (B = 'receipt')) AS rec,
              (SELECT     C
              FROM          TEST AS a5
              WHERE      (a5.A = TEST.A) AND (B = 'transnum')) AS num
    FROM         ( select A,B,C From Table  ) AS TEST
    ORDER BY last, first
    Last edited by mozkill; 07-13-09 at 18:28.

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Your are right mozkill, I had a typo (and replicated it with copy/paste).

    All of the Case statements should have THEN C instead of B like this:
    Code:
    CASE B WHEN 'date'     THEN C ELSE ' ' END AS DATE_COL
    (replace all the THEN B with C).
    It should perform better because it only goes through the table once.
    ==========
    PS I must have been brain-dead yesterday. I forgot the key component to the method I tried to show you. That is the MAX function (so you can get the MAX value and filter out the Spaces). Here is the corrected SQL:
    Code:
    SELECT A
         , MAX(CASE B WHEN 'date'     THEN C ELSE ' ' END) AS DATE_COL
         , MAX(CASE B WHEN 'fname'    THEN C ELSE ' ' END) AS FNAME
         , MAX(CASE B WHEN 'lname'    THEN C ELSE ' ' END) AS LNAME
         , MAX(CASE B WHEN 'receipt'  THEN C ELSE ' ' END) AS RECEIPT
         , MAX(CASE B WHEN 'transnum' THEN C ELSE ' ' END) AS TRANSNUM
    FROM table-name
    GROUP BY A
    You can get a NULL returned by leaving off the ELSE part of the CASE expression (or, I belive, by using ELSE NULL).
    Last edited by Stealth_DBA; 07-14-09 at 03:46.

  6. #6
    Join Date
    Feb 2003
    Posts
    33
    StealthDBA, I just tried that and it worked!

    Interestingly , we both came up with 2 different ways to solve this issue. What can you say about your method that is better than MY method? Isn't my method easier to understand? Isn't using the "MAX" keyword a little bit hard for a SQL beginner (like me) to grasp?

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    For one, your solution will cause performance problems on large recordsets. Those select queries will each be run for each row of output.

  8. #8
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    I believe mCrowley is correct (I am more familiar with how DB2 processes a query than SQL Server though they should be similar).

    With Management Studio, you can view the Execution plan (one of the Menu options). Do that for each query and you should see (I think):

    The MAX(CASE) just reads the table one with a table space scan (no where clause) and then a sort to do the Group By.

    The one you have will probably access the table 5 or 6 times (probably with table space scans but since you have a Where clause, some index may be used).

    Ultimately, just run both (larger sets give you a better idea or performance) and see which one runs the fastest.

  9. #9
    Join Date
    Feb 2003
    Posts
    33
    Awesome. You guys are correct.

    There is 10,000 rows in my result set.

    SQL Management Studio tells me that my query has a subtree cost of 15.9 and the cost of StealthDBA's query is only 3.39.

    I guess that basically means that its 4x more efficient in this case.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •