CODEWITHSUNDEEP
sqlsql-servert-sqlunpivot
Padmanabhan Vijendran
Padmanabhan Vijendran

Reputation: 306

Rows to Columns and viceversa in SQL

I am very new to sql and I need a better approach for the below scenario.

Table

enter image description here

And I need to convert to below result.

enter image description here

Upvotes: 0

Views: 94

Answers (3)

neer
neer

Reputation: 4082

Also, you can do it as the below.

;WITH CTE
AS
(
    SELECT 
       B.ID, 
       B.yes,
       B.col
    FROM 
        (SELECT ID ,
                ISNULL(COL1, '') COL1 ,
                ISNULL(COL2, '') COL2,
                ISNULL(COL3, '') COL3 ,
                ISNULL(COL4, '') COL4
            FROM @Tbl) A
    UNPIVOT
    (
      col
      FOR yes in (col1, col2, col3, col4)
    ) B
) 


SELECT CTE.ID, CTE.yes FROM  CTE
WHERE  col = 'yes'
UNION ALL
SELECT DISTINCT  ID, '' FROM CTE A
WHERE NOT EXISTS
(
    SELECT 1 FROM CTE B WHERE 
        B.ID = A.ID AND
        B.col = 'yes'
)

Result:

ID          yes  
----------- -----
1           COL1
1           COL3
2           COL2
2           COL4
3           COL3
4           
5           COL2
5           COL3
6           
7           COL4

Upvotes: 1

StackUser
StackUser

Reputation: 5398

You can try using unpivot. Here is the sample demo with data.

DECLARE @Table TABLE (
        ID INT
        ,COL1 VARCHAR(3)
        ,COL2 VARCHAR(3)
        ,COL3 VARCHAR(3)
        ,COL4 VARCHAR(3)
        )
INSERT INTO @TABLE VALUES

(1,'yes',null,'yes',null)
,(2,null,'yes',null,'yes')
,(3,null,null,'yes',null)
,(4,null,null,null,null)
,(5,null,'yes','yes',null)
,(6,null,null,null,null)
,(7,null,null,null,'yes')


SELECT id
    ,yes
FROM (
    SELECT id
        ,col1
        ,col2
        ,col3
        ,col4
    FROM @TABLE
     where coalesce(col1, col2, col3, col4) is not null
    ) AS cp
UNPIVOT(yes FOR col IN (
            col1
            ,col2
            ,col3
            ,col4
            )) AS up
union
select id, null from @TABLE
    where coalesce(col1, col2, col3, col4) is null
order by id

Upvotes: 3

jarlh
jarlh

Reputation: 44766

Do a UNION ALL, one select for each coln, and finally one SELECT for rows without any yes at all.

select id, 'col1' from tablename where col1 = 'yes'
union all
select id, 'col2' from tablename where col2 = 'yes'
union all
select id, 'col3' from tablename where col3 = 'yes'
union all
select id, 'col4' from tablename where col4 = 'yes'
union all
select id, cast(null as char(4)) from tablename
    where 'yes' not in (coalesce(col1, 'x'),
                        coalesce(col2, 'x'),
                        coalesce(col3, 'x'),
                        coalesce(col4, 'x'))

If the only value is 'yes' (or NULL), then the last select can be done as

select id, cast(null as char(4)) from tablename
where coalesce(col1, col2, col3, col4) is null

Upvotes: 3

Related Questions