CODEWITHSUNDEEP
sql-serverpivot
Esen
Esen

Reputation: 973

SQL Pivot query without aggregation or max

I am trying to get a pivot result with no aggregation, I tried max and it didn't help, may be 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 get this below result

enter image description here

I am trying to get this below result

enter image description here

It would be great if you could show me a path to achieve this.

Upvotes: 3

Views: 392

Answers (1)

Taryn
Taryn

Reputation: 247720

You'll still need to use an aggregate function with the PIVOT, but you need some sort of value to return multiple rows based on the combination of col1, col2, and col3. This is where you'd want to use a windowing function like 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;

See SQL Fiddle with Demo

The row_number() function creates a unique sequence that is partitioned by the col1, col2 and col3 values - I then ordered the results by your col5 and col4 values to create the sequence in a specific order. This new value is used when the pivot groups the data which results in multiple rows being returned instead of the single row.

Upvotes: 7

Related Questions