SQL query to show columns based on column value repetition

Question

My table is :-

DECLARE @Tab TABLE(VId INT,QuestionNoId INT,AnswerType VARCHAR(20),Answer VARCHAR(100))
INSERT INTO @Tab(Vid,   QuestionNoId,   AnswerType, Answer)VALUES
(2370,  1,  'Text', '1'),
(2370,  1,  'Per',  '78'),
(2370,  1,  'Date', '04/05/2019'),
(2370,  2,  'Text', '1'),
(2370,  3,  'Text', '2'),
(2370,  4,  'YesNo','D'),
(2370,  5,  'Text', '68'),
(2370,  6,  'Text', '44'),
(2370,  6,  'Date', '05/05/2019'),
(2370,  7,  'YesNo','Y'),
(2370,  8,  'YesNo','N'),
(2370,  9,  'YesNo', 'Y')

select * from @Tab

I want to show resulted query based on my QuestionNoId, Number of columns should be increased based on QuestionNoId., If QuestionNoId is repeated 3 times then 3 columns should be created, if it is 1 then one only one.

Find my desired output below:-

DECLARE @Tab1 TABLE(VId INT,QuestionNoId INT,AnswerType VARCHAR(20),Answer VARCHAR(100),AnswerType1 VARCHAR(20),Answer1 VARCHAR(100),AnswerType2 VARCHAR(20),Answer2 VARCHAR(100))
INSERT INTO @Tab1(Vid,  QuestionNoId,   AnswerType, Answer, AnswerType1,    Answer1,    AnswerType2,    Answer2)VALUES
(2370,  1,  'Text', '1','Per',  '78','Date',    '04/05/2019'),
(2370,  2,  'Text', '1',NULL,NULL,NULL,NULL),
(2370,  3,  'Text', '2',NULL,NULL,NULL,NULL),
(2370,  4,  'YesNo','D',NULL,NULL,NULL,NULL),
(2370,  5,  'Text', '68',NULL,NULL,NULL,NULL),
(2370,  6,  'Text', '44','Date','05/05/2019',NULL,NULL),
(2370,  7,  'YesNo','Y',NULL,NULL,NULL,NULL),
(2370,  8,  'YesNo','N',NULL,NULL,NULL,NULL),
(2370,  9,  'YesNo','Y',NULL,NULL,NULL,NULL)

select * from @Tab1

Thanks

Answer 1

SQL Server PIVOT is your friend here Using PIVOT and UNPIVOT:

DECLARE @Tab TABLE(VId INT,QuestionNoId INT,AnswerType VARCHAR(20),Answer VARCHAR(100))
INSERT INTO @Tab(Vid,   QuestionNoId,   AnswerType, Answer)VALUES
(2370,  1,  'Text', '1'),
(2370,  1,  'Per',  '78'),
(2370,  1,  'Date', '04/05/2019'),
(2370,  2,  'Text', '1'),
(2370,  3,  'Text', '2'),
(2370,  4,  'YesNo','D'),
(2370,  5,  'Text', '68'),
(2370,  6,  'Text', '44'),
(2370,  6,  'Date', '05/05/2019'),
(2370,  7,  'YesNo','Y'),
(2370,  8,  'YesNo','N'),
(2370,  9,  'YesNo', 'Y')


;with AnswerType as (
    select Vid, QuestionNoId, max([1]) as AnswerType1, max([2]) as AnswerType2, max([3]) as AnswerType3, max([4]) as AnswerType4
    from (
        select *, ROW_NUMBER() OVER (PARTITION BY QuestionNoId ORDER BY QuestionNoId) AS qrc
        from @Tab
    ) as src1
    pivot (
        max(AnswerType)
        for qrc in ([1],[2],[3],[4] /*Max number of expected answers*/)
    ) as pvt1
    group by Vid, QuestionNoId
)
,Answer as (
    select Vid, QuestionNoId, max([1]) as Answer1, max([2]) as Answer2, max([3]) as Answer3, max([4]) as Answer4
    from (
        select *, ROW_NUMBER() OVER (PARTITION BY QuestionNoId ORDER BY QuestionNoId) AS qrc
        from @Tab
    ) as src1
    pivot (
        max(Answer)
        for qrc in ([1],[2],[3],[4] /*Max number of expected answers*/)
    ) as pvt1
    group by Vid, QuestionNoId
)
select t.VId, t.QuestionNoId, t.AnswerType1, a.Answer1, t.AnswerType2, a.Answer2, t.AnswerType3, a.Answer3, t.AnswerType4, a.Answer4
from AnswerType as t
inner join Answer as a
    on t.VId = a.VId
    and t.QuestionNoId = a.QuestionNoId