Pivoting

• suraj.app

  SSC-Addicted

  Points: 433

  More actions

  February 13, 2015 at 9:35 am

  #301256

  Hi Team,

  I have my table as below

  instanceid rownum QuestionText Answer Result

  1 1 Q1 A1 R1

  1 2 Q2 A2 R2

  2 1 Q3 A3 R3

  I want the output as

  1 Q1 A1 R1 Q2 A2 R2

  2 Q3 A3 R3

  I tried a lot by using pivot etc. But most of the example given used aggregate function. Hence, I could not achieve it. Please help.

  Thank you

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  February 13, 2015 at 10:53 am

  #1777588

  Take a look at Cross tabs which will make the pivoting of multiple columns a lot easier and faster using Copy-Paste-Edit.

  References in here:

  Part 1[/url]

  Part 2[/url]

  Example

  SELECT instanceid

  ,MAX( CASE WHEN rownum = 1 THEN QuestionText END) Q1

  ,MAX( CASE WHEN rownum = 1 THEN Answer END) A1

  ,MAX( CASE WHEN rownum = 1 THEN Result END) R1

  ,MAX( CASE WHEN rownum = 2 THEN QuestionText END) Q2

  ,MAX( CASE WHEN rownum = 2 THEN Answer END) A2

  ,MAX( CASE WHEN rownum = 2 THEN Result END) R2

  FROM MyTable

  GROUP BY instanceid

  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

• Nevyn

  SSChampion

  Points: 14210

  More actions

  February 13, 2015 at 2:04 pm

  #1777612

  suraj.app (2/13/2015)

  ---

  Hi Team,

  I have my table as below

  instanceid rownum QuestionText Answer Result

  1 1 Q1 A1 R1

  1 2 Q2 A2 R2

  2 1 Q3 A3 R3

  I want the output as

  1 Q1 A1 R1 Q2 A2 R2

  2 Q3 A3 R3

  I tried a lot by using pivot etc. But most of the example given used aggregate function. Hence, I could not achieve it. Please help.

  Thank you

  Tough to tell if you want a pivot or a concatenation based on that sample. Are there meant to be separate columns for the 2nd rows QuestionText, Answer, and Result? Can a instance have more than 2 rows? For instance 2 did you want null to appear under the new headings?