SQL server how to convert column values into rows

Question

I will try to explain my question.

My table structure:

name Code_1 Code_1_value Code_2 Code_2_value Code_3 Code_3_value
N1    ABC1        10      ABC2       15       ABC3        6
N2    ABC1        3       NULL      NULL      BAA1        10
N3    ABC4        5       ABC2       11       ADC3        6

and I would like to convert this values into new format:

name    code    value
N1      ABC1      10
N1      ABC2      15
N1      ABC3      6
N2      ABC1      3

I don't know how logically should be (pivot?)

Devart · Accepted Answer · 2016-01-28T08:32:09.613

Query -

DECLARE @t TABLE (
    name VARCHAR(50) PRIMARY KEY,
    Code_1 VARCHAR(50),
    Code_1_value INT,
    Code_2 VARCHAR(50),
    Code_2_value INT,
    Code_3 VARCHAR(50),
    Code_3_value INT
)
INSERT INTO @t
VALUES
    ('N1', 'ABC1', 10, 'ABC2', 15  , 'ABC3', 6),
    ('N2', 'ABC1', 3 , NULL,   NULL, 'BAA1', 10),
    ('N3', 'ABC4', 5 , 'ABC2', 11  , 'ADC3', 6)

SELECT name, code, value
FROM @t
CROSS APPLY (
    VALUES (Code_1, Code_1_value), (Code_2, Code_2_value),  (Code_3, Code_3_value)
) t2 (code, value)
WHERE code IS NOT NULL

Output -

name     code     value
-------- -------- -----------
N1       ABC1     10
N1       ABC2     15
N1       ABC3     6
N2       ABC1     3
N2       BAA1     10
N3       ABC4     5
N3       ABC2     11
N3       ADC3     6

Post about UNPIVOT -

http://blog.devart.com/is-unpivot-the-best-way-for-converting-columns-into-rows.html

If this answer helped you might want to [accept](http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work) it. This helps others who come after you and rewards Devart for his/her effort. — David Rushton, Jan 28 '16 at 09:07

score 2 · Answer 2 · answered Jan 28 '16 at 08:34

This can also be done with a UNION

SELECT name,code_1 as code,code_1_value as value from yourTable
UNION ALL
SELECT name,code_2 as code,code_2_value as value from yourTable
UNION ALL    
SELECT name,code_3 as code,code_3_value as value from yourTable

SQL server how to convert column values into rows

2 Answers2