I have a table with 8 columns
ALL,OPEN,CLOSED,RECOVERY,CLAIMS, PAYMENT1,PAYMENT2,PAYMENT3
I WOULD LIKE TO UNPIVOT THE COLUMNS INTO ROWS LIKE
ALL, PAYMENT1, PAYMENT2, PAYMENT3
OPEN
CLOSED
RECOVERY
CLAIMS
I have a table with 8 columns
ALL,OPEN,CLOSED,RECOVERY,CLAIMS, PAYMENT1,PAYMENT2,PAYMENT3
I WOULD LIKE TO UNPIVOT THE COLUMNS INTO ROWS LIKE
ALL, PAYMENT1, PAYMENT2, PAYMENT3
OPEN
CLOSED
RECOVERY
CLAIMS
Please post table scripts, sample data and expected output. See http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ for guidance.
CROSS JOIN is one rather easy way, like below:
SELECT
CASE row_type
WHEN 'ALL' THEN [ALL]
WHEN 'OPEN' THEN [OPEN]
WHEN 'CLOSED' THEN CLOSED
WHEN 'RECOVERY' THEN RECOVERY
WHEN 'CLAIMS' THEN CLAIMS
END AS row_type,
CASE WHEN row_type = 'ALL' THEN PAYMENT1 ELSE '' END AS PAYMENT1,
CASE WHEN row_type = 'ALL' THEN PAYMENT2 ELSE '' END AS PAYMENT2,
CASE WHEN row_type = 'ALL' THEN PAYMENT3 ELSE '' END AS PAYMENT3
FROM (
SELECT
'ALL' [ALL],
'OPEN' [OPEN],
'CLOSED' CLOSED,
'RECOVERY' RECOVERY,
'CLAIMS' CLAIMS,
'PAYMENT1' PAYMENT1,
'PAYMENT2' PAYMENT2,
'PAYMENT3' PAYMENT3
) AS table_name
CROSS JOIN (
VALUES('ALL'),('OPEN'),('CLOSED'),('RECOVERY'),('CLAIMS')
) AS row_types(row_type)