CODEWITHSUNDEEP
sqlpivot
Jt2ouan
Jt2ouan

Reputation: 1964

SQL Pivot Column that has multiple values for same column

Trying to pivot table results that may have multiple rows with the same value I have data that looks like this so far.

Nbr      Person     Test

33      Barry.      Prim
33      Brian       Sup
33      Burke RT    1st
33      Ray         Add
33      Jake        Add
33      Smith       Add

I'm trying to pivot it so that it looks like this:

Nbr   Prim    Sup     1st       Add   Add2       Add3

33   Barry   Brian   Burke      Ray   Jake       Smith

This is what I have so far with a normal pivot but it doesn't work to grab all the ones with the same value in the Test Column

CREATE TABLE #testTbl(nbr int,name varchar(20),test VARCHAR(10))
INSERT INTO #testTbl
SELECT '33','Barry','Prim'
UNION
SELECT '33','Brian','Sup'
UNION
SELECT '33','Burke','1st'
UNION
SELECT '33','Ray','Add'
UNION
SELECT '33','jake','Add'
UNION
SELECT '33','Smith','Add'


select * from (
Select * 
from #testTbl
) as x
 pivot(
 max(name) for test in ([prim],[sup],[1st],[add])
)
as pivot1

Any help is greatly appreciated. If its not possible to have the columns output as Add Add2 and Add3 thats fine. Whatever works.

Upvotes: 1

Views: 2836

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can do so by modifying the test value using window functions:

select *
from (Select tt.name,
             (test + (case when count(*) over (partition by test) = 1
                           then ''
                           else cast(row_number() over (partition by test order by (select null)) as varchar(255))
                      end)) as test
      from testTbl tt
     ) as x
pivot(
 max(name) for test in ([prim], [sup], [1st], [Add1], [Add2], [Add3])
) as pivot1

A SQL Fiddle is here.

Upvotes: 1

Related Questions