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

What are the possible values for `QuestionNoId` - from `1` to `3` or can be different? Thanks. — Zhorov, May 08 '19 at 08:33
You need [dynamic pivot query](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Michał Turczyn, May 08 '19 at 08:48
So what you are looking for is a dynamic query, but is there any particular order that the data has to be shown. is it ok to have date before text as opposed to your output for 2370 question 1 which has text before data — Ajan Balakumaran, May 08 '19 at 08:49

score 1 · Accepted Answer · answered May 08 '19 at 09:28

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

SQL query to show columns based on column value repetition

1 Answers1