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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Nitin SontakkeDeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.