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 · 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