0

I have a table with three columns and 18 rows. I want to change into the 18 columns with two rows. Here is the table

 Category   Bands   Points
 20_CAT1    21-22   10
 20_CAT2    23-25   20
 20_CAT3    26- 30
 30_CAT1    31-33   10
 30_CAT2    34-38   20
 30_CAT3    39- 30
 40_CAT1    41-44   5
 40_CAT2    45-50   15
 40_CAT3    51- 25
 50_CAT1    50-55   5
 50_CAT2    56-63   15
 50_CAT3    64- 25
 60_CAT1    61-66   5
 60_CAT2    67-75   20
 60_CAT3    76- 30
 70_CAT1    71-77   5
 70_CAT2    78-88   20
 70_CAT3    89- 30

The new table should have the "Category" As the Columns with the "Bands" and "Points" as the rows

I'm not sure how to use Cross Apply and Pivot to do this or even if this is the correct approach.

Thanks in advance.

Vignesh Kumar A
  • 27,863
  • 13
  • 63
  • 115
Mazhar
  • 3,797
  • 1
  • 12
  • 29
  • Looks like it would be redundant to have both Category and Bands columns since they have a 1-to-1 mapping. – shree.pat18 Apr 08 '14 at 10:28
  • possible duplicate of [How to pivot unknown number of columns & no aggregate in SQL Server?](http://stackoverflow.com/questions/22772481/how-to-pivot-unknown-number-of-columns-no-aggregate-in-sql-server) – M.Ali Apr 08 '14 at 11:10
  • Thanks M Ali, I'm, struggling with the syntax in your example and how to apply it to my problem. Any further assistance would be appreciated. – Mazhar Apr 08 '14 at 11:31

1 Answers1

1

I've managed to get the following to work without the use of a PIVOT.

        SELECT
        MAX(CASE WHEN Category = '20_CAT1' THEN Bands END) [20_CAT1_Bands],
        MAX(CASE WHEN Category = '20_CAT1' THEN Points END) [20_CAT1_Points],
        MAX(CASE WHEN Category = '20_CAT2' THEN Bands END) [20_CAT2_Bands],
        MAX(CASE WHEN Category = '20_CAT2' THEN Points END) [20_CAT2_Points],
        MAX(CASE WHEN Category = '20_CAT3' THEN Bands END) [20_CAT3_Bands],
        MAX(CASE WHEN Category = '20_CAT3' THEN Points END) [20_CAT3_Points],

        MAX(CASE WHEN Category = '30_CAT1' THEN Bands END) [30_CAT1_Bands],
        MAX(CASE WHEN Category = '30_CAT1' THEN Points END) [30_CAT1_Points],
        MAX(CASE WHEN Category = '30_CAT2' THEN Bands END) [30_CAT2_Bands],
        MAX(CASE WHEN Category = '30_CAT2' THEN Points END) [30_CAT2_Points],
        MAX(CASE WHEN Category = '30_CAT3' THEN Bands END) [30_CAT3_Bands],
        MAX(CASE WHEN Category = '30_CAT3' THEN Points END) [30_CAT3_Points],

        MAX(CASE WHEN Category = '40_CAT1' THEN Bands END) [40_CAT1_Bands],
        MAX(CASE WHEN Category = '40_CAT1' THEN Points END) [40_CAT1_Points],
        MAX(CASE WHEN Category = '40_CAT2' THEN Bands END) [40_CAT2_Bands],
        MAX(CASE WHEN Category = '40_CAT2' THEN Points END) [40_CAT2_Points],
        MAX(CASE WHEN Category = '40_CAT3' THEN Bands END) [40_CAT3_Bands],
        MAX(CASE WHEN Category = '40_CAT3' THEN Points END) [40_CAT3_Points],

        MAX(CASE WHEN Category = '50_CAT1' THEN Bands END) [50_CAT1_Bands],
        MAX(CASE WHEN Category = '50_CAT1' THEN Points END) [50_CAT1_Points],
        MAX(CASE WHEN Category = '50_CAT2' THEN Bands END) [50_CAT2_Bands],
        MAX(CASE WHEN Category = '50_CAT2' THEN Points END) [50_CAT2_Points],
        MAX(CASE WHEN Category = '50_CAT3' THEN Bands END) [50_CAT3_Bands],
        MAX(CASE WHEN Category = '50_CAT3' THEN Points END) [50_CAT3_Points],

        MAX(CASE WHEN Category = '60_CAT1' THEN Bands END) [60_CAT1_Bands],
        MAX(CASE WHEN Category = '60_CAT1' THEN Points END) [60_CAT1_Points],
        MAX(CASE WHEN Category = '60_CAT2' THEN Bands END) [60_CAT2_Bands],
        MAX(CASE WHEN Category = '60_CAT2' THEN Points END) [60_CAT2_Points],
        MAX(CASE WHEN Category = '60_CAT3' THEN Bands END) [60_CAT3_Bands],
        MAX(CASE WHEN Category = '60_CAT3' THEN Points END) [60_CAT3_Points],

        MAX(CASE WHEN Category = '70_CAT1' THEN Bands END) [70_CAT1_Bands],
        MAX(CASE WHEN Category = '70_CAT1' THEN Points END) [70_CAT1_Points],
        MAX(CASE WHEN Category = '70_CAT2' THEN Bands END) [70_CAT2_Bands],
        MAX(CASE WHEN Category = '70_CAT2' THEN Points END) [70_CAT2_Points],
        MAX(CASE WHEN Category = '70_CAT3' THEN Bands END) [70_CAT3_Bands],
        MAX(CASE WHEN Category = '70_CAT3' THEN Points END) [70_CAT3_Points]

    FROM @table
Mazhar
  • 3,797
  • 1
  • 12
  • 29