SQLTeam.com | Weblogs | Forums

Sql columns to rows


#1

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


#2

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.


#3

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)