how to get data from a table all in one result row

• Denise McMillan

  SSCertifiable

  Points: 6216

  More actions

  September 10, 2013 at 3:11 pm

  #395454

  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.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  September 10, 2013 at 4:13 pm

  #1649052

  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.
  General Disclaimer:
  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?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Denise McMillan

  SSCertifiable

  Points: 6216

  More actions

  September 11, 2013 at 6:23 am

  #1649238

  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.