SQL Pivot query without aggregation or max
I am trying to get a pivot result without aggregation, I tried max and it didn't help, maybe I am doing something wrong.
When I run this below query
declare @t table
(
col1 int,
col2 varchar(100),
col3 varchar(100),
col4 varchar(100),
col5 int
)
insert into @t values(1,'test1','p1','v1',1)
insert into @t values(1,'test1','p2','v2',2)
insert into @t values(1, 'test1','p3','v3',3)
insert into @t values(1,'test1','p1','v11',1)
insert into @t values(1,'test1','p1','v12',1)
insert into @t values(1,'test1','p2','v21',2)
insert into @t values(1,'test1','p2','v22',2)
--select * from @t
select col1,
col2,
[p1],
[p2],
[p3]
from
(
select * from @t
) x
pivot
(
Max(col4 )
for col3 in ([p1],[p2],[p3])
) pvt
I am getting this below result
I am trying to get this result below
It would be great if you could show me the way to achieve this.
source to share
You still need to use an aggregate function with PIVOT, but you need some value to return multiple rows based on a combination of col1
, col2
and col3
. This is where you want to use the windows function, eg row_number()
.
If you use the following query, you should be able to get the result:
select col1, col2, p1, p2, p3
from
(
select col1, col2, col3, col4,
rn = row_number() over(partition by col1, col2, col3 order by col5, col4)
from @t
) d
pivot
(
max(col4)
for col3 in (p1, p2, p3)
) p;
The function row_number()
creates a unique sequence that is separated by the values col1
, col2
and col3
. Then I ordered the results according to your values col5
and col4
to create a sequence in a specific order. This new value is used when the pivot is grouping data that results in multiple rows being returned instead of a single row.
source to share