MS SQL Server transpose vertical [Type] to horizontal Columns

In my select below... vertical data
You can see that the IndividualID may not have all of the "QuestionTypes
And... the Question Types are "Dynamic"... I will never know exactly how many "types" there are
But I need to "transpose" the dats so that an IndividualID is on one line
And The Questiontypes are horizontal as Column Headers

DECLARE @table TABLE(IndividualID BIGINT, OriginID, INT,QuestionType VARCHAR(50), AnswerText VARCHAR(100))

INSERT INTO @table ( IndividualID ,
                     OriginID,
                     QuestionType ,
                     AnswerText )
VALUES ( 2217655 ,101,'Age' , '25-34'),
	   ( 2217655 ,101,'Residence' , 'Yes'),
	   ( 2217655 ,101,'CardType' , 'MasterCard'),
	   ( 2217655 ,101,'Income' , '$40,000-$49,999'),
	   ( 2217655 ,'VacationActivity' , 'Shopping'),
	   ( 2217663 ,,101'Age' , '35-44'),
	   ( 2217663 ,101,'Residence' , 'No'),
	   ( 2217663 ,101,'VacationActivity' , 'Beachg')

SELECT  * FROM @table where OriginID = 101

Open in new window


This is the output
SP1
And this is (An example of the ) desired output
SP2
Larry Bristersr. DeveloperAsked:
Who is Participating?
 
Nitin SontakkeConnect With a Mentor DeveloperCommented:
Here is a sample:

DECLARE @table TABLE(IndividualID BIGINT, OriginID INT,QuestionType VARCHAR(50), AnswerText VARCHAR(100))

INSERT INTO @table ( IndividualID ,
                     OriginID,
                     QuestionType ,
                     AnswerText )
VALUES ( 2217655 ,101,'Age' , '25-34'),
	   ( 2217655 ,101,'Residence' , 'Yes'),
	   ( 2217655 ,101,'CardType' , 'MasterCard'),
	   ( 2217655 ,101,'Income' , '$40,000-$49,999'),
	   ( 2217655, 101,'VacationActivity' , 'Shopping'),
	   ( 2217663 ,101, 'Age' , '35-44'),
	   ( 2217663 ,101,'Residence' , 'No'),
	   ( 2217663 ,101,'VacationActivity' , 'Beachg')

--SELECT  * FROM @table where OriginID = 101

select [IndividualID], [Age], [Residence], [CardType], [Income], [VacationActivity]
from (
  select [IndividualID], [QuestionType], [AnswerText]
  from @table
  ) src
  pivot
  (
    max([AnswerText])
    for [QuestionType] in ([Age], [Residence], [CardType], [Income], [VacationActivity])
  )
as pt

Open in new window


Yes, you are right. But I would expect they would still be finite. You can resort to dynamic sql in that case. Get distinct QuestionText first and proceed to build the query dynamically.
0
 
Nitin SontakkeDeveloperCommented:
Just use Pivot. Plain and simple.
0
 
Larry Bristersr. DeveloperAuthor Commented:
Nitin
Don't you have to know the columns?  Like I said... they are dynamic and can be added to
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Larry Bristersr. DeveloperAuthor Commented:
Nitin...
  Like I said... I won't always know the exact Question Types... it will expand over time per OriginID

In any case... using your PIVOT... and marrying it with some xml... I have found a solution to this question
I will award you the points because you did provide AN answer based on the data provided
But I have an additional problem I will need to address in another

CREATE TABLE #table (IndividualID BIGINT, OriginID INT,QuestionType VARCHAR(50), AnswerText VARCHAR(100))

INSERT INTO #table ( IndividualID ,
                     OriginID,
                     QuestionType ,
                     AnswerText )
VALUES ( 2217655 ,101,'Age' , '25-34'),
	   ( 2217655 ,101,'Residence' , 'Yes'),
	   ( 2217655 ,101,'CardType' , 'MasterCard'),
	   ( 2217655 ,101,'Income' , '$40,000-$49,999'),
	   ( 2217655 ,101,'VacationActivity' , 'Shopping'),
	   ( 2217663 ,101,'Age' , '35-44'),
	   ( 2217663 ,101,'Residence' , 'No'),
	   ( 2217663 ,101,'VacationActivity' , 'Beachg')


DECLARE @cols NVARCHAR(MAX) ,
        @query NVARCHAR(MAX);

SET @cols = STUFF((   SELECT DISTINCT ',' + QUOTENAME([QuestionType])
                      FROM   #table
                      FOR XML PATH(''), TYPE ).value('.', 'nvarchar(max)') ,
                  1 ,
                  1 ,
                  '');
SET @query = 'SELECT CAST([IndividualID] AS VARCHAR(10)) IndividualID, ' + @cols + ' from (SELECT IndividualID,QuestionType,[AnswerText]
    FROM #table
    )x pivot (max(AnswerText) for QuestionType  in (' + @cols + ')) p';
EXECUTE ( @query );

Open in new window

0
 
Larry Bristersr. DeveloperAuthor Commented:
Thanks
0
 
Nitin SontakkeDeveloperCommented:
And there is another approach....now that I have already typed in Query Analyzer....

create table #table (IndividualID BIGINT, OriginID INT,QuestionType VARCHAR(50), AnswerText VARCHAR(100))

INSERT INTO #table ( IndividualID ,
                     OriginID,
                     QuestionType ,
                     AnswerText )
VALUES ( 2217655 ,101,'Age' , '25-34'),
	   ( 2217655 ,101,'Residence' , 'Yes'),
	   ( 2217655 ,101,'CardType' , 'MasterCard'),
	   ( 2217655 ,101,'Income' , '$40,000-$49,999'),
	   ( 2217655, 101,'VacationActivity' , 'Shopping'),
	   ( 2217663 ,101, 'Age' , '35-44'),
	   ( 2217663 ,101,'Residence' , 'No'),
	   ( 2217663 ,101,'VacationActivity' , 'Beachg')

declare @columns nvarchar(max) = ''

select @columns += iif(len(@columns) = 0, '', ', ') + '[' + [QuestionType] + ']'
from (
  select distinct [QuestionType]
  from #table
) t

declare @dynamicSQL nvarchar(max) = 'select [IndividualID], ' + @columns + '
from (
  select [IndividualID], [QuestionType], [AnswerText]
  from #table
  ) src
  pivot
  (
    max([AnswerText])
    for [QuestionType] in (' + @columns + ')
  )
as pt
'
execute sp_executeSQL @dynamicSQL

drop table #table

Open in new window


This one is lot easier to grasp, to me, at the least.
0
 
Larry Bristersr. DeveloperAuthor Commented:
Nitin... that does work...I have another variation of this I will post in another question
0
 
Mark WillsTopic AdvisorCommented:
Here you go
declare  @cols varchar(max)
declare @sql varchar(max)

select @cols = isnull(@cols+',','') + '['+questiontype+']' from #table group by QuestionType
set @sql = '
SELECT  * 
FROM 
(select individualid,originID,questionType,answertext from #table where OriginID = 101) src
PIVOT 
(max(answertext) for questiontype in ('+@cols+'))pvt'

exec(@sql)

Open in new window

Wrote an article about dynamic SQL for pivot :https://www.experts-exchange.com/articles/653/Dynamic-Pivot-Procedure-for-SQL-Server.html
0
 
Mark WillsTopic AdvisorCommented:
Ah well, too slow, too bad....  memo to self, dont make coffee when should be typing.
0
 
Larry Bristersr. DeveloperAuthor Commented:
:)  I have another questionposted
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.