Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to pivot but to keep one column unchanged Expand / Collapse
Author
Message
Posted Monday, January 27, 2014 2:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 2:34 PM
Points: 5, Visits: 42
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
Post #1535204
Posted Monday, January 27, 2014 3:53 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:33 PM
Points: 3,325, Visits: 7,173
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.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1535242
Posted Tuesday, January 28, 2014 7:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 2:34 PM
Points: 5, Visits: 42
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
Post #1535452
Posted Tuesday, January 28, 2014 8:52 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:33 PM
Points: 3,325, Visits: 7,173
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.




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1535509
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse