Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jayram
Starting Member
47 Posts |
 Posted - 2014-01-27 : 15:01:41
|
| i have a table with 3 columnsID, Name and Value. It has like 1000 recordsID Name Value1, DG1, 560001, m_DG1, invalid2, DG1, 67892, DG2, 78902, m_DG1, Valid2, m_DG2, invalidi am trying to transpose and want to select and display likeID DG1 m_DG1 DG2 m_DG21 56000 invalid NULL NULL2 6789 Valid 7890 Invalidor like this where the order of columns are like belowID DG1 DG2 m_DG1 m_DG2is it possible to transpose this.Thanks |
|
|
sqlsaga
Yak Posting Veteran
93 Posts |
Posted - 2014-01-28 : 01:18:08
|
| [code]DECLARE @TABLE TABLE(ID INT,NAME VARCHAR(20),VALUE VARCHAR(20))INSERT INTO @TABLE VALUES(1,'DG1', '56000'), (1,'m_DG1', 'invalid'),(2,'DG1', '6789'),(2,'DG2', '7890'), (2,'m_DG1', 'Valid'), (2,'m_DG2', 'invalid')SELECT *FROM (SELECT * FROM @TABLE) aPIVOT (MAX(Value) FOR name in ([DG1], [m_DG1], [DG2], [m_DG2]))pvt[/code]You have to put all the possible column names in the IN clause to get them as columns.Visit www.sqlsaga.com for more t-sql snippets and BI related how to's. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
jayram
Starting Member
47 Posts |
Posted - 2014-01-28 : 16:50:39
|
| Thank you both. This is helpful and i also used http://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql |
|
|
|
|
|
|
|
|
Pivot or transpose