Converting a Column into multiple columns based on another coloumn (Newbie)

• santa326

  SSC Eights!

  Points: 925

  More actions

  June 29, 2012 at 5:23 am

  #263092

  Hi All,

  I have three Columns

  StudentName|Question|Answer

  StudentA |1 |some

  StudentA |2 |thing

  StudentA |3 |any

  StudentA |4 |thing

  StudentA |5 |every thing

  StudentA |6 |no

  StudentA |7 |thing

  StudentB |1 |This

  StudentB |2 |is

  StudentB |3 |Urgent

  StudentB |4 |Please

  StudentB |5 |Help

  StudentB |6 |Thank

  StudentB |7 |You

  I want the result to be

  StudentName |Ans1 |Ans2 |Ans3 |Ans4 |Ans5

  StudentA |Some |Thing |any |Thing |Every thing

  StudentB |This |is |Urgent |Please |Help

  Hope this is clear.

  Regards

  Ravi T

• Kingston Dhasian

  SSCoach

  Points: 19799

  More actions

  June 29, 2012 at 6:49 am

  #1507101

  Welcome to SQL Server Central

  Please check the link in my signature on how post your questions for faster and better replies

  Since this is your first post, I have done some work for you by preparing the DDL and sample data

  DECLARE@Student TABLE( StudentName VARCHAR(50), Question INT, Answer VARCHAR(50))

  INSERT@Student

  SELECT'StudentA' StudentName, 1 Question, 'some' Answer UNION ALL

  SELECT'StudentA', 2, 'thing' UNION ALL

  SELECT'StudentA', 3, 'any' UNION ALL

  SELECT'StudentA', 4, 'thing' UNION ALL

  SELECT'StudentA', 5, 'every thing' UNION ALL

  SELECT'StudentA', 6, 'no' UNION ALL

  SELECT'StudentA', 7, 'thing' UNION ALL

  SELECT'StudentB', 1, 'This' UNION ALL

  SELECT'StudentB', 2, 'is' UNION ALL

  SELECT'StudentB', 3, 'Urgent' UNION ALL

  SELECT'StudentB', 4, 'Please' UNION ALL

  SELECT'StudentB', 5, 'Help' UNION ALL

  SELECT'StudentB', 6, 'Thank' UNION ALL

  SELECT'StudentB', 7, 'You'

  SELECTStudentName,

  MAX( CASE WHEN Question = 1 THEN Answer ELSE '' END ) Ans1,

  MAX( CASE WHEN Question = 2 THEN Answer ELSE '' END ) Ans2,

  MAX( CASE WHEN Question = 3 THEN Answer ELSE '' END ) Ans3,

  MAX( CASE WHEN Question = 4 THEN Answer ELSE '' END ) Ans4,

  MAX( CASE WHEN Question = 5 THEN Answer ELSE '' END ) Ans5

  FROM@Student

  GROUP BY StudentName

  ---

  Kingston Dhasian

  How to post data/code on a forum to get the best help - Jeff Moden
  http://www.sqlservercentral.com/articles/Best+Practices/61537/

• santa326

  SSC Eights!

  Points: 925

  More actions

  June 29, 2012 at 7:05 am

  #1507108

  Hi

  Thanks ill go through your post.

  Ive got the result.

  Thanks

  Regards

  Ravi T

• santa326

  SSC Eights!

  Points: 925

  More actions

  June 30, 2012 at 4:53 am

  #1507436

  Hi,

  How do i query the above result by using where clause?..

  i want to use where Student Name like "Stu%"

  regards

  Ravi T

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  June 30, 2012 at 5:10 am

  #1507439

  santa326 (6/30/2012)

  ---

  Hi,

  How do i query the above result by using where clause?..

  i want to use where Student Name like "Stu%"

  regards

  Ravi T

  HAVING (StudentName like 'Stu%')

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• santa326

  SSC Eights!

  Points: 925

  More actions

  June 30, 2012 at 5:19 am

  #1507440

  J Livingston SQL (6/30/2012)

  ---

  santa326 (6/30/2012)

  ---

  Hi,

  How do i query the above result by using where clause?..

  i want to use where Student Name like "Stu%"

  regards

  Ravi T

  HAVING (StudentName like 'Stu%')

  DECLARE@Student TABLE( StudentName VARCHAR(50), Question INT, Answer VARCHAR(50))

  INSERT@Student

  SELECT'StudentA' StudentName, 1 Question, 'some' Answer UNION ALL

  SELECT'StudentA', 2, 'thing' UNION ALL

  SELECT'StudentA', 3, 'any' UNION ALL

  SELECT'StudentA', 4, 'thing' UNION ALL

  SELECT'StudentA', 5, 'every thing' UNION ALL

  SELECT'StudentA', 6, 'no' UNION ALL

  SELECT'StudentA', 7, 'thing' UNION ALL

  SELECT'StudentB', 1, 'This' UNION ALL

  SELECT'StudentB', 2, 'is' UNION ALL

  SELECT'StudentB', 3, 'Urgent' UNION ALL

  SELECT'StudentB', 4, 'Please' UNION ALL

  SELECT'StudentB', 5, 'Help' UNION ALL

  SELECT'StudentB', 6, 'Thank' UNION ALL

  SELECT'StudentB', 7, 'You'

  SELECTStudentName,

  MAX( CASE WHEN Question = 1 THEN Answer ELSE '' END ) Ans1,

  MAX( CASE WHEN Question = 2 THEN Answer ELSE '' END ) Ans2,

  MAX( CASE WHEN Question = 3 THEN Answer ELSE '' END ) Ans3,

  MAX( CASE WHEN Question = 4 THEN Answer ELSE '' END ) Ans4,

  MAX( CASE WHEN Question = 5 THEN Answer ELSE '' END ) Ans5

  FROM@Student

  having (Ans5 like 'help')

  GROUP BY StudentName

  This dont seem to work.Or am I placing it wrong

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  June 30, 2012 at 5:30 am

  #1507441

  ok...

  GROUP BY StudentName

  HAVING (MAX(CASE WHEN Question = 5 THEN Answer ELSE '' END) = 'help')

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day

• Mark Eckeard

  Hall of Fame

  Points: 3462

  More actions

  June 30, 2012 at 4:39 pm

  #1507503

  DECLARE@Student TABLE( StudentName VARCHAR(50), Question INT, Answer VARCHAR(50))

  INSERT@Student

  SELECT'StudentA' StudentName, 1 Question, 'some' Answer UNION ALL

  SELECT'StudentA', 2, 'thing' UNION ALL

  SELECT'StudentA', 3, 'any' UNION ALL

  SELECT'StudentA', 4, 'thing' UNION ALL

  SELECT'StudentA', 5, 'every thing' UNION ALL

  SELECT'StudentA', 6, 'no' UNION ALL

  SELECT'StudentA', 7, 'thing' UNION ALL

  SELECT'StudentB', 1, 'This' UNION ALL

  SELECT'StudentB', 2, 'is' UNION ALL

  SELECT'StudentB', 3, 'Urgent' UNION ALL

  SELECT'StudentB', 4, 'Please' UNION ALL

  SELECT'StudentB', 5, 'Help' UNION ALL

  SELECT'StudentB', 6, 'Thank' UNION ALL

  SELECT'StudentB', 7, 'You'

  SELECTStudentName,

  MAX( CASE WHEN Question = 1 THEN Answer ELSE '' END ) Ans1,

  MAX( CASE WHEN Question = 2 THEN Answer ELSE '' END ) Ans2,

  MAX( CASE WHEN Question = 3 THEN Answer ELSE '' END ) Ans3,

  MAX( CASE WHEN Question = 4 THEN Answer ELSE '' END ) Ans4,

  MAX( CASE WHEN Question = 5 THEN Answer ELSE '' END ) Ans5

  FROM@Student

  where StudentName like 'Stu%'

  GROUP BY StudentName

• santa326

  SSC Eights!

  Points: 925

  More actions

  July 6, 2012 at 5:14 am

  #1509604

  Hi all,

  I am having one more trouble with the query.I have one more column added to this query called "attempt".

  I want to show ans from the last attempt. attempt is highest.

  Eg.If a student had attempted question thrice.I want the ans from 3rd attempt.

  DECLARE@Student TABLE( StudentName VARCHAR(50), Question INT,Attempt INT, Answer VARCHAR(50))

  INSERT@Student

  SELECT'StudentA' StudentName, 1 Question,1 Attempt 'some' Answer UNION ALL

  SELECT'StudentA', 1, 2, 'thing' UNION ALL

  SELECT'StudentA', 2, 1,'thing' UNION ALL

  SELECT'StudentA', 2, 2,'thing2' UNION ALL

  SELECT'StudentA', 2, 3,'thing3' UNION ALL

  SELECT'StudentA', 3, 1,'any' UNION ALL

  SELECT'StudentA', 4, 1,'thing' UNION ALL

  SELECT'StudentA', 5, 1,'every thing' UNION ALL

  SELECT'StudentA', 5, 2,'thing52' UNION ALL

  SELECT'StudentA', 6, 1,'no' UNION ALL

  SELECT'StudentA', 6, 2,'thing62' UNION ALL

  SELECT'StudentA', 7, 1,'thing' UNION ALL

  SELECT'StudentA', 7, 2,'thing72' UNION ALL

  SELECT'StudentA', 7, 3,'thing73' UNION ALL

  SELECT'StudentB', 1, 1,'This' UNION ALL

  SELECT'StudentB', 1, 2,'This12' UNION ALL

  SELECT'StudentB', 1, 3,'This13' UNION ALL

  SELECT'StudentB', 2, 1,'is' UNION ALL

  SELECT'StudentB', 2, 2,'is22' UNION ALL

  SELECT'StudentB', 3, 1,'Urgent' UNION ALL

  SELECT'StudentB', 4, 1,'Please' UNION ALL

  SELECT'StudentB', 4, 2,'Please42' UNION ALL

  SELECT'StudentB', 4, 3,'Please43' UNION ALL

  SELECT'StudentB', 5, 1,'Help' UNION ALL

  SELECT'StudentB', 5, 2,'Help52' UNION ALL

  SELECT'StudentB', 6, 1,'Thank' UNION ALL

  SELECT'StudentB', 7, 1,'You'

  SELECTStudentName,

  MAX( CASE WHEN Question = 1 THEN Answer ELSE '' END ) Ans1,

  MAX( CASE WHEN Question = 2 THEN Answer ELSE '' END ) Ans2,

  MAX( CASE WHEN Question = 3 THEN Answer ELSE '' END ) Ans3,

  MAX( CASE WHEN Question = 4 THEN Answer ELSE '' END ) Ans4,

  MAX( CASE WHEN Question = 5 THEN Answer ELSE '' END ) Ans5

  FROM@Student

  GROUP BY StudentName

  Thanks in advance

  Regards

  Ravi T

• Kingston Dhasian

  SSCoach

  Points: 19799

  More actions

  July 6, 2012 at 5:39 am

  #1509615

  This should give you the desired result

  SELECTStudentName,

  MAX( CASE WHEN Question = 1 THEN Answer ELSE '' END ) Ans1,

  MAX( CASE WHEN Question = 2 THEN Answer ELSE '' END ) Ans2,

  MAX( CASE WHEN Question = 3 THEN Answer ELSE '' END ) Ans3,

  MAX( CASE WHEN Question = 4 THEN Answer ELSE '' END ) Ans4,

  MAX( CASE WHEN Question = 5 THEN Answer ELSE '' END ) Ans5

  FROM(

  SELECTROW_NUMBER() OVER( PARTITION BY StudentName, Question ORDER BY Attempt DESC ) RN, *

  FROM@Student

  ) S

  WHERERN = 1

  GROUP BY StudentName

  ---

  Kingston Dhasian

  How to post data/code on a forum to get the best help - Jeff Moden
  http://www.sqlservercentral.com/articles/Best+Practices/61537/