Rows to Columns and viceversa in SQL

Question

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

Table

And I need to convert to below result.

Answer 1

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