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.
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