CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
user3583912
user3583912

Reputation: 1322

Rows into Columns in t-SQL?

I have a table like this:

ID  ColumnName
1   START_DTTM
1   END_DTTM
1   STATUS
2   START_DTTM
2   END_DTTM
2   status
3   START_DTTM
3   END_DTTM
3   status
3   Archive

I want result like this:

ID  ColumnName      ColumnName2     ColumnName3     ColumnName4 
1   START_DTTM      END_DTTM        status          NULL
2   START_DTTM      END_DTTM        status          NULL
3   START_DTTM      END_DTTM        status          Archive

Any help..Thanks in advance

Upvotes: 1

Views: 63

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

This produces the wanted result. But please be aware, that there is no implicit sort order! Without a specific ORDER BY you might get random result.

declare @tbl TABLE(ID INT,  ColumnName VARCHAR(100));
INSERT INTO @tbl VALUES
 (1,'START_DTTM')
,(1,'END_DTTM')
,(1,'STATUS')
,(2,'START_DTTM')
,(2,'END_DTTM')
,(2,'status')
,(3,'START_DTTM')
,(3,'END_DTTM')
,(3,'status')
,(3,'Archive');


SELECT p.*
FROM
(
    SELECT 'ColumnName' + CAST(ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS VARCHAR(10)) AS PivotColumn
          ,tbl.*
    FROM @tbl AS tbl
) AS x
PIVOT
(
    MIN(ColumnName) FOR PivotColumn IN(ColumnName1,ColumnName2,ColumnName3,ColumnName4)
) AS p

The result:

ID  ColumnName1 ColumnName2 ColumnName3 ColumnName4
1   START_DTTM  END_DTTM    STATUS      NULL
2   START_DTTM  END_DTTM    status      NULL
3   START_DTTM  END_DTTM    status      Archive

Upvotes: 4

Related Questions