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: Wednesday, November 12, 2014 10:41 AM
Points: 8, Visits: 52
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 @ 6:46 PM
Points: 3,919, Visits: 8,897
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.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

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: Wednesday, November 12, 2014 10:41 AM
Points: 8, Visits: 52
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 @ 6:46 PM
Points: 3,919, Visits: 8,897
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.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

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