Row TO Columns based on Formid (ID)

Question

i have extracted data from console application by reading the 2 to 3k XMls and stored in database in  following form (sample Data).where  ihave change the rows to columns based of formid .
if particular form id do not have  question _(dash ) should be displayed.
Question ID
Form Id
Question
Answer
1
1
Do you Smoke
yes
2
2
Do you have asthama
yes
3
1
Do have Bo above 330
No
4
3
Do have Bo above 330
yes
5
1
Do you have asthama
No
6
2
Do you Smoke
No
7
3
Do you have asthama
yes
8
2
Do have Bo above 330
No
9
3
Do you Smoke
yes
10
1
do you have vitamins alergy
yes
11
3
Allery with antiblotics
yes
i want following output
formid
Do you Smoke
Do you have asthama
Do have Bo above 330
do you have vitamins alergy
1
yes
No
No
yes
_
2
No
yes
No
_
_
3
yes
yes
yes
_
yes

Sanwar Ranwa · Accepted Answer

Hello Niki    Check this link you can use Pivot to perform thisb kind of operations in SQL    https://www.c-sharpcorner.com/UploadFile/f0b2ed/pivot-and-unpovit-in-sql-server/

Nikki Nagarkar · Answer

I have got the answer using Pivot in sql DECLARE  @COLS  AS  NVARCHAR( MAX ), @QUERY  AS  NVARCHAR( MAX )   SELECT  @COLS = STUFF(( SELECT   ','  + QUOTENAME(Question)   FROM  PromQuestionAnswerData   GROUP   BY  Question   FOR  XML PATH( '' ), TYPE   ).value( '.' ,  'NVARCHAR(MAX)' )   ,1,1, '' )   SET  @QUERY =  'SELECT FormdID,'  + @COLS + '   FROM    (   SELECT  Answer Answer,FormdID,Question   FROM  PromQuestionAnswerData  GROUP   BY  FormdID,Answer,Question   ) X   PIVOT   (   MIN (Answer)   FOR  Question  IN  ( ' + @COLS + ' )   ) P '   PRINT(@QUERY)   EXECUTE  SP_EXECUTESQL @QUERY;

Question ID	Form Id	Question	Answer
1	1	Do you Smoke	yes
2	2	Do you have asthama	yes
3	1	Do have Bo above 330	No
4	3	Do have Bo above 330	yes
5	1	Do you have asthama	No
6	2	Do you Smoke	No
7	3	Do you have asthama	yes
8	2	Do have Bo above 330	No
9	3	Do you Smoke	yes
10	1	do you have vitamins alergy	yes
11	3	Allery with antiblotics	yes

formid	Do you Smoke	Do you have asthama	Do have Bo above 330	do you have vitamins alergy
1	yes	No	No	yes	_
2	No	yes	No	_	_
3	yes	yes	yes	_	yes