Conform query to present data in desired format

• rjjh78

  SSCrazy

  Points: 2784

  More actions

  October 18, 2018 at 10:40 am

  #416197

  Hello,

   

  I am trying to create a table that will be able to displayinformation dynamically.  The intent isto display a list of questions (for a survey if you will) and list the appropriateoptions following the question (is applicable). My goal would be to display information in the desired way withoutneeding work down at the application level. Below is my sample code and desired output.  Many thanks for any help in advance!

  CREATE TABLE #Questions (QID int, QFieldType varchar(25), Question varchar(1000), Sort int, Active int)
INSERT INTO  #Questions (QID, QFieldType, Question, Sort, Active) VALUES (1, 'Text', 'What is your name?', 1, 1)
INSERT INTO  #Questions (QID, QFieldType, Question, Sort, Active) VALUES (2, 'Checkbox', 'Please select your favorite colors', 2, 1)
INSERT INTO  #Questions (QID, QFieldType, Question, Sort, Active) VALUES (3, 'Checkbox', 'Please select your favorite Sports', 3, 1)


  CREATE TABLE #QOptions (QOID int IDENTITY (1,1), QID int, OptionDesc varchar(100), Sort int, Active int)
  INSERT INTO  #QOptions (QID, OptionDesc, Sort, Active) VALUES (2, 'Red'       , 1,1)
  INSERT INTO  #QOptions (QID, OptionDesc, Sort, Active) VALUES (2, 'Orange'    , 2,1)
  INSERT INTO  #QOptions (QID, OptionDesc, Sort, Active) VALUES (2, 'Green'     , 3,1)
  INSERT INTO  #QOptions (QID, OptionDesc, Sort, Active) VALUES (2, 'Blue'      , 4,0)
  INSERT INTO  #QOptions (QID, OptionDesc, Sort, Active) VALUES (2, 'Yellow'    , 5,1)
  INSERT INTO  #QOptions (QID, OptionDesc, Sort, Active) VALUES (3, 'Baseball'  , 1,1)
  INSERT INTO  #QOptions (QID, OptionDesc, Sort, Active) VALUES (3, 'Basketball', 2,1)
  INSERT INTO  #QOptions (QID, OptionDesc, Sort, Active) VALUES (3, 'Football'  , 3,1)
  INSERT INTO  #QOptions (QID, OptionDesc, Sort, Active) VALUES (3, 'Hockey'    , 4,1)
  INSERT INTO  #QOptions (QID, OptionDesc, Sort, Active) VALUES (3, 'Soccer'    , 5,1)
  INSERT INTO  #QOptions (QID, OptionDesc, Sort, Active) VALUES (3, 'Golf'      , 6,1)
  INSERT INTO  #QOptions (QID, OptionDesc, Sort, Active) VALUES (3, 'Tennis'    , 7,1)

  Desired Outcome:

  QID Question
  1 What is your name?
  2 Please select your favorite colors? 
   Red
   Orange
   Green
   Yellow
  3 Please select your favorite Sports
   Baseball
   Baseketball
   Football
   Hockey
   Soccer
   Golf
   Tennis