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 get data from a table all in one result row Expand / Collapse
Author
Message
Posted Tuesday, September 10, 2013 3:11 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 2, 2014 1:40 PM
Points: 201, Visits: 422
I'm need help with a query.
The table has an id, a name, and a type. Sample:
ID Name Type
123 description black
123 technique breaking
456 findings nice
456 technique splitting

How can I select to get this result:
123 black breaking
456 nice splitting

Thanks very much for any help.
Post #1493370
Posted Tuesday, September 10, 2013 4:13 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:04 PM
Points: 3,749, Visits: 8,415
You can use cross tabs to accomplish this. To read more about this technique, read the following article.
http://www.sqlservercentral.com/articles/T-SQL/63681/
SELECT ID,
MAX( CASE WHEN Name = 'Description' THEN Type) AS Description,
MAX( CASE WHEN Name = 'technique ' THEN Type) AS Technique
FROM MyTable
GROUP BY ID




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 #1493387
Posted Wednesday, September 11, 2013 6:23 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, October 2, 2014 1:40 PM
Points: 201, Visits: 422
This worked, I had to add an else and end and it worked like I needed.
SELECT ID,
MAX( CASE WHEN Name = 'Description' THEN Type else null end) AS Description,
MAX( CASE WHEN Name = 'technique ' THEN Type else null end) AS Technique
FROM MyTable
GROUP BY ID

Thanks very much.


Post #1493614
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse