Convert column value data to row value data in sql

Question

I have a query which generates the below result in sql server. And need to transpose the data to a single row from 4 rows.

When I tried with pivot function and case statements getting null values with 4 rows again. Kindly help me with the idea of getting it.

ID  No  Name Letter Answer CMT
123 103 A1    A      Low    Abc
123 103 A2    B      High   Abc
123 103 A3    A      Medium Cde
123 103 A4    C      Low    Hhj

Expected O/P :

ID No Name1 Letter1 Answe1 CMT1 Name2 Letter2 Ans2 Cmt2 Name3 Letter3 ans3 cmt3 name4 letter4 ans4 cmt4
123 103 A1 A Low Abc A2 B High Abc A3 A Medium Cde A4 C Low Hhj

Tried with case when Name=A1 then Name end as Name1, case when name=A1 then letter end as letter1, case when name =A1 then Answer end answer1 And used pivot function on name as well but again getting 4 rows with nulls except the match. Just typed code snip. — Dileep, May 19 '23 at 13:01
that's not an SQL statement, please use [edit], and share the complete SQL statement. — Luuk, May 19 '23 at 13:02
@lemon that one seems to be unrelated. (Linked question is not related to this question). This is not dynamic SQL really. — Cetin Basoz, May 19 '23 at 13:23
@lemon the linked question is answered by using the pivot operator. The problem posed in this question isn't solved by the pivot operator. — Paul Maxwell, May 20 '23 at 04:54

Paul Maxwell · Answer 1 · 2023-05-21T05:57:15.213

I wouldn't recommend using "pivot" for this need. Although this may at first look complex, it will be easier to achieve your output using "conditional aggregates" (case expressions inside an aggregate function):

SELECT
      ID
    , No
    , MAX(CASE WHEN Name = 'A1' THEN Name END) AS Name1
    , MAX(CASE WHEN Name = 'A1' THEN Letter END) AS Letter1
    , MAX(CASE WHEN Name = 'A1' THEN Answer END) AS Answer1
    , MAX(CASE WHEN Name = 'A1' THEN CMT END) AS CMT1
    , MAX(CASE WHEN Name = 'A2' THEN Name END) AS Name2
    , MAX(CASE WHEN Name = 'A2' THEN Letter END) AS Letter2
    , MAX(CASE WHEN Name = 'A2' THEN Answer END) AS Answer2
    , MAX(CASE WHEN Name = 'A2' THEN CMT END) AS CMT2
    , MAX(CASE WHEN Name = 'A3' THEN Name END) AS Name3
    , MAX(CASE WHEN Name = 'A3' THEN Letter END) AS Letter3
    , MAX(CASE WHEN Name = 'A3' THEN Answer END) AS Answer3
    , MAX(CASE WHEN Name = 'A3' THEN CMT END) AS CMT3
    , MAX(CASE WHEN Name = 'A4' THEN Name END) AS Name4
    , MAX(CASE WHEN Name = 'A4' THEN Letter END) AS Letter4
    , MAX(CASE WHEN Name = 'A4' THEN Answer END) AS Answer4
    , MAX(CASE WHEN Name = 'A4' THEN CMT END) AS CMT4
FROM mytable
GROUP BY
      ID
    , No

;

For dynamic sql:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)
;

SET @cols = STUFF((SELECT DISTINCT ',' +
                        'MAX(CASE WHEN Name = ''' + Name + ''' THEN Name END) AS [Name' + Name + '],' +
                        'MAX(CASE WHEN Name = ''' + Name + ''' THEN Letter END) AS [Letter' + Name + '],' +
                        'MAX(CASE WHEN Name = ''' + Name + ''' THEN Answer END) AS [Answer' + Name + '],' +
                        'MAX(CASE WHEN Name = ''' + Name + ''' THEN CMT END) AS [CMT' + Name +']'
                    FROM mytable
                    FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)'),1,1,'')


SET @query = 'SELECT ID, No,' + @cols +
             ' FROM mytable GROUP BY ID, No'

EXECUTE(@query);

ID	No	NameA1	LetterA1	AnswerA1	CMTA1	NameA2	LetterA2	AnswerA2	CMTA2	NameA3	LetterA3	AnswerA3	CMTA3	NameA4	LetterA4	AnswerA4	CMTA4
123	103	A1	A	Low	Abc	A2	B	High	Abc	A3	A	Medium	Cde	A4	C	Low	Hhj

fiddle

This works well. Missed to add aggregator for it. But if we want to build it dynamically with pivot how can I try to do it ? — Dileep, May 19 '23 at 13:08
It's the same process you need to build-up the sql. You should put requesst such as "I need dynamic sql" in the question. facts of this nature get lost in these tiny comments — Paul Maxwell, May 19 '23 at 13:12
@Dileep, can you explain what is dynamic in this case? I would assume you really don't have A1, A2, A3, A4 to define those columns, but other than that what is dynamic? — Cetin Basoz, May 19 '23 at 13:19
@CetinBasoz, in case statement we are using name = A1 . If in case, if we have more records, we need to keep on adding case statements. Instead of that if we have to use pivot function that will be great use. And many places we can’t use the dynamic sql which put above. Looking for a plain query option. — Dileep, May 20 '23 at 03:54
btw: "case" is an **expression** not a statement. An expression evaluates to a single value, that's what case does. — Paul Maxwell, May 20 '23 at 04:27

score -1 · Answer 2 · answered May 19 '23 at 13:15

WITH myData AS 
(
    SELECT ID,
           No,
           Name,
           Letter,
           Answer,
           CMT,
           ROW_NUMBER() OVER (PARTITION BY Id,No ORDER BY ID, No) rn
           FROM myTable
) 
SELECT ID,
       No,
       MAX(CASE WHEN rn = 1 THEN Name END) name1,
       MAX(CASE WHEN rn = 1 THEN Letter END) Letter1,
       MAX(CASE WHEN rn = 1 THEN Answer END) Answer1,
       MAX(CASE WHEN rn = 1 THEN CMT END) CMT1,
       MAX(CASE WHEN rn = 2 THEN Name END) name2,
       MAX(CASE WHEN rn = 2 THEN Letter END) Letter2,
       MAX(CASE WHEN rn = 2 THEN Answer END) Answer2,
       MAX(CASE WHEN rn = 2 THEN CMT END) CMT2,
       MAX(CASE WHEN rn = 3 THEN Name END) name3,
       MAX(CASE WHEN rn = 3 THEN Letter END) Letter3,
       MAX(CASE WHEN rn = 3 THEN Answer END) Answer3,
       MAX(CASE WHEN rn = 3 THEN CMT END) CMT3,
       MAX(CASE WHEN rn = 4 THEN Name END) name4,
       MAX(CASE WHEN rn = 4 THEN Letter END) Letter4,
       MAX(CASE WHEN rn = 4 THEN Answer END) Answer4,
       MAX(CASE WHEN rn = 4 THEN CMT END) CMT4
       FROM myData
       GROUP BY Id, No;

ID	No	name1	Letter1	Answer1	CMT1	name2	Letter2	Answer2	CMT2	name3	Letter3	Answer3	CMT3	name4	Letter4	Answer4	CMT4
123	103	A1	A	Low	Abc	A2	B	High	Abc	A3	A	Medium	Cde	A4	C	Low	Hhj

DBfiddle demo

Convert column value data to row value data in sql

2 Answers2

Linked