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.