January 27, 2014 at 2:14 pm
i have a table with 3 columns like below
ID, Name and Value. It has like 1000 records
ID Name Value
1, DG1, 56000
1, m_DG1, invalid
2, DG1, 6789
2, DG2, 7890
2, m_DG1, Valid
2, m_DG2, invalid
i am trying to transpose and want to select and display like
ID DG1 m_DG1 DG2 m_DG2
1 56000 invalid NULL NULL
2 6789 Valid 7890 Invalid
or like this where the order of columns are like below
ID DG1 DG2 m_DG1 m_DG2
is it possible to transpose this.
Thanks
January 27, 2014 at 3:53 pm
You could easily use a cross tabs approach to this problem. You can read more about this method on the following article: http://www.sqlservercentral.com/articles/T-SQL/63681/
If you have any questions after reading the article, feel free to ask.
WITH SampleData( ID, Name, Value) AS(
SELECT
1, 'DG1', '56000' UNION ALL SELECT
1, 'm_DG1', 'invalid' UNION ALL SELECT
2, 'DG1', '6789' UNION ALL SELECT
2, 'DG2', '7890' UNION ALL SELECT
2, 'm_DG1', 'Valid' UNION ALL SELECT
2, 'm_DG2', 'invalid'
)
SELECT ID,
MAX( CASE WHEN Name = 'DG1' THEN Value END) AS DG1,
MAX( CASE WHEN Name = 'DG2' THEN Value END) AS DG2,
MAX( CASE WHEN Name = 'm_DG1' THEN Value END) AS m_DG1,
MAX( CASE WHEN Name = 'm_DG2' THEN Value END) AS m_DG2
FROM SampleData
GROUP BY ID
ORDER BY ID
Note: It would be nice if you post your sample data in a consumable format so we don't have to waste time preparing a scenario to test the code. You can read on how to do it in the article linked in my signature.
January 28, 2014 at 7:44 am
Thank you. After searching a bit more i found a similar solution like yours in here
http://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql
I will keep your note in mind
Thanks again
Jay
January 28, 2014 at 8:52 am
I'm glad that you got a solution. Be sure to understand it, that's why I inclued a link to an article that goes step by step on how the solution is made.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply