Pivot Multiple Fields With Dynamic Descriptions

Question

I'm trying to pivot the following and I don't know why I'm having such a hard time figuring it out.

Data Script

create table #data (ID varchar(50)
, nm varchar(50)
, val decimal(18,2)
)

insert into #data values (1,'Name1', 100.00),
(1,'Name2', 200.00),
(2,'Name3', 300.00),
(2,'Name4', 400.00),
(2,'Name5', 500.00),
(3,'Name6', 600.00),
(4,'Name7', 700.00),
(4,'Name8', 800.00),
(5,'Name9', 900.00)

Wanted Results As A Table in SQL Server

1   Name1   100 Name2   200     
2   Name3   300 Name4   400 Name5   500   
3   Name6   600             
4   Name7   700 Name8   800     
5   Name9   900

Update: The following provides results in two fields, but what I really want is for the Name and Values to all exist in separate columns, not in one,

SELECT  id,
(
    SELECT nm,val 
    FROM #data
    WHERE  id = d.id
    ORDER BY id FOR XML PATH('')
) 
FROM #data d
WHERE 
    id IS NOT NULL
GROUP BY id;

Possible duplicate of [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — clinomaniac, Mar 08 '18 at 21:40
Asked because in 2017, I believe there is a new method to do this. In 2008 you'll have to use something like what is mentioned in the post linked above. — clinomaniac, Mar 08 '18 at 21:44

score 0 · Answer 1 · answered Mar 08 '18 at 21:56

This is an example of "pivot" not aggregate string concatenation. One issue with SQL queries is that you need to specify the exact columns being returned. So, this cannot be dynamic with respect to the returning columns.

The following returns up to three values per nm:

select id,
       max(case when seqnum = 1 then nm end) as nm_1,
       max(case when seqnum = 1 then val end) as val_1,
       max(case when seqnum = 2 then nm end) as nm_2,
       max(case when seqnum = 2 then val end) as val_2,
       max(case when seqnum = 3 then nm end) as nm_3,
       max(case when seqnum = 3 then val end) as val_3
from (select d.*,
             row_number() over (partition by id order by (select null)) as seqnum
      from #data d
     ) d
group by id;

Note that you probably want the columns in insertion order. If so, you need to specify a column with the ordering. I would recommend defining the table as:

create table #data (
    dataId int identity(1, 1,) primary key,
    ID varchar(50),
    nm varchar(50),
    val decimal(18,2)
);

Pivot Multiple Fields With Dynamic Descriptions

1 Answers1