I have Data Table like this

how to be like this

CodePudding user response：

If you want more control, I would recommend using a cursor in a stored procedure. But if you just want to achieve your expected output, Akina's suggestion is quite straightforward.

select id , a as value 
from
    (select id, a from test
    union
    select id, b from test
    union
    select id, c from test) t
order by id
; -- union does not warrant the function of ORDER BY clause in it, so we should make the union statement  a derived table and have the parent query sort it out.

CodePudding user response：

Converting columns to rows is called unpivoting. Converting rows to columns is pivoting.

One approach to unpivoting data is to combine the apply operator with a table value constructor.

This example uses a common table expression (CTE) to return 3 sample records.

-- Unpivoting with apply and VALUES.        
WITH SampleData AS
    (
        /* This CTE returns 3 sample records.
         */             
        SELECT
            cte.*
        FROM
            (
                VALUES
                    (1, 'a', 'b', 'c'),
                    (2, 'd', 'e', 'f'),
                    (3, 'g', 'h', 'i')
            ) AS cte(Id, ColumnOne, ColumnTwo, ColumnThree)
    )
SELECT
    sd.Id,
    ca.*
FROM
    SampleData AS sd
        CROSS APPLY
            (
                VALUES
                    (ColumnOne),
                    (ColumnTwo),
                    (ColumnThree)
            ) AS ca (ColumnFour)
;