CODEWITHSUNDEEP
t-sql
PETZLr
PETZLr

Reputation: 3

In SQL Server, Is it possible to pivot rows without aggregation?

Here is an example: Assuming that each command has a limited number of parameters, I would like to represent the parameters and their values as named_indexed columns.

--drop table TCommand
--drop table TParam

Create Table TCommand (
CommandID INT,
CommandName NCHAR(20),
Description NVARCHAR(100)
);

Create Table TParam (
CommandID INT,
ParamName NCHAR(20),
ParamValue NCHAR(50)
);

insert into TCommand Values(1, 'C1', 'Desc for command C1')
insert into TCommand Values(2, 'C2', 'Desc for command C2')
insert into TCommand Values(3, 'C3', 'Desc for command C3')

insert into TParam Values (1, 'Pa', 'xa1')
insert into TParam Values (1, 'Pb', 'yb1')
insert into TParam Values (1, 'Pc', 'zc1')

insert into TParam Values (2, 'Px', 'xa2')
insert into TParam Values (2, 'Py', 'yb2')

insert into TParam Values (3, 'Pt', 'xa3')
insert into TParam Values (3, 'Pu', 'yb3')

select tc.*, tp.ParamName, tp.ParamValue 
from TCommand tc
join TParam tp on tp.CommandID=tc.CommandID
order by tc.CommandName, tp.ParamName

Results:

CommandID   CommandName Description          ParamName ParamValue
----------- ----------- -------------------- --------- ----------
1           C1          Desc for command C1  Pa        xa1       
1           C1          Desc for command C1  Pb        yb1       
1           C1          Desc for command C1  Pc        zc1       
2           C2          Desc for command C2  Px        xa2       
2           C2          Desc for command C2  Py        yb2       
3           C3          Desc for command C3  Pt        xa3       
3           C3          Desc for command C3  Pu        yb3

Here is the format I would like to obtain.

CommandID   CommandName Description          ParamName_1 ParamValue_1 ParamName_2 ParamValue_2 ParamName_3 ParamValue_3
----------- ----------- -------------------- ----------- ------------ ----------- ------------ ----------- ------------
1           C1          Desc for command C1  Pa          xa1          Pb          yb1          Pc          zc1   
2           C2          Desc for command C2  Px          xa2          Py          yb2          NULL        NULL      
3           C3          Desc for command C3  Pt          xa3          Pu          yb3          NULL        NULL

What query should I write? Earlier attempts failed using PIVOT because of missing Aggregation function (which I thought I do not need).

Thanks in advance.

Upvotes: 0

Views: 38

Answers (1)

Razvan Socol
Razvan Socol

Reputation: 5694

You need to create two pivot queries here: one for ParamName and one for ParamValue. In the q1 and q2 derived tables I have selected only the needed columns for each pivot (if I would have used just q in both queries, I would get extra rows with NULLs in the relevant columns).

To be able to join the two queries, you need a column representing the position of the parameter (named RowNum in the query below).

If there is a single value for each value of the pivot column, you can use an aggregator function such as MIN or MAX (which ignores the NULL values and keeps the single input value).

Therefore, you can use the following query:

;WITH q AS (
    SELECT tp.CommandID,
        ROW_NUMBER() OVER (PARTITION BY tp.CommandID ORDER BY tp.ParamName) AS RowNum,
        tp.ParamName, tp.ParamValue
    FROM dbo.TParam tp
)
SELECT tc.CommandID, tc.CommandName, tc.Description, 
    x1.ParamName_1, x2.ParamValue_2, x1.ParamName_2, x2.ParamValue_2, x1.ParamName_3, x2.ParamValue_3
FROM dbo.TCommand tc
LEFT JOIN (
    SELECT p.CommandID, p.[1] AS ParamName_1, p.[2] AS ParamName_2, p.[3] AS ParamName_3
    FROM (SELECT q.CommandID, q.RowNum, q.ParamName FROM q) q1
    PIVOT (MAX(ParamName) FOR RowNum IN ([1],[2],[3])) p
) x1 ON x1.CommandID = tc.CommandID
LEFT JOIN (
    SELECT p.CommandID, p.[1] AS ParamValue_1, p.[2] AS ParamValue_2, p.[3] AS ParamValue_3
    FROM (SELECT q.CommandID, q.RowNum, q.ParamValue FROM q) q2
    PIVOT (MAX(ParamValue) FOR RowNum IN ([1],[2],[3])) p
) x2 ON x2.CommandID = tc.CommandID

Upvotes: 1

Related Questions