CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlpivot
ratanmalko
ratanmalko

Reputation: 471

TSQL-Select - Turn multiple Rows into single Row

I have to deal with the following table structure where columns are stored as rows:

CREATE TABLE Test 
    (
     ID varchar(10), 
     RowNumber int, 
     Column1 varchar(200), -- sheet name
     Column2 varchar(200), -- column name
     Column3 varchar(200)  -- column cell value
    );

INSERT INTO Test
(ID, RowNumber,Column1,Column2, Column3)
VALUES
('ID0001', 1, 'sheet abc','InternalNumber','2016_2923_2321'),
('ID0001', 2, 'sheet abc','Customer','ACME'),
('ID0001', 3, 'sheet abc','Project','ABC_Project'),
('ID0001', 4, 'another sheet name','Subject','New Model'),
('ID0001', 5, 'another sheet name','SOP','2016'),
('ID0001', 6, 'another sheet name','EOP','2022')

I managed to turn the rows into proper columns (see fiddle at the bottom) but instead of getting multiple rows I want to put the data into a single row as each value in Column2 is a unique name.

In below picture at the top is the input table, the second one is what I have and the third is my desired output I cannot get to work:

Table

See also my fiddle

I hope you can help me solve this issue.

Thank you in advance.

Upvotes: 2

Views: 382

Answers (1)

gofr1
gofr1

Reputation: 15977

The basic way is to use PIVOT:

SELECT *
FROM (
    SELECT  ID,
            Column2, 
            Column3
    FROM #Test
) as t
PIVOT (
    MAX(Column3) FOR Column2 IN ([InternalNumber],[Customer],[Project],[Subject],[SOP],[EOP])
) as p

Output:

ID      InternalNumber  Customer    Project     Subject     SOP     EOP
ID0001  2016_2923_2321  ACME        ABC_Project New Model   2016    2022

If there are much more values in Column2 better use dynamic SQL.

Upvotes: 1

Related Questions