Pivot query

• EamonSQL

  SSCrazy

  Points: 2307

  More actions

  July 30, 2018 at 5:38 am

  #373925

  Hi,

  I'm having trouble with the pivot statement below and would like to line up each [Comment] value for each [pid] value under a series of columns genereted by [comment_id].
  I know I'm close but I can't see what I'm doing wrong.
  Any help very welcome.
  Thanks,
  Eamon

  declare @table table(pid int,Comment varchar(20),comment_id int)
insert into @table values(1,'AAAA',1)
insert into @table values(1,'BBBB',2)
insert into @table values(2,'CCCC',1)
insert into @table values(2,'DDDD',2)
insert into @table values(2,'EEEE',3)
insert into @table values(2,'FFFF',4)

  select *
from @table
pivot
(
min(comment_id)
FOR Comment IN ([1],[2],[3],[4],[5])
) AS PivotTable;

  -->>>>Desired Result
  pid    1            2             3           4           5    
  1       AAAA    BBBB      NULL    NULL    NULL
  2      CCCC    DDDD     EEEE   FFFF     NULL
  

• Jonathan AC Roberts

  SSCoach

  Points: 18356

  More actions

  July 30, 2018 at 6:29 am

  #1999445

  select *
from @table
pivot
(
min(comment)
FOR comment_id IN ([1],[2],[3],[4],[5])
) AS PivotTable;

• EamonSQL

  SSCrazy

  Points: 2307

  More actions

  July 30, 2018 at 6:47 am

  #1999450

  so close yet so far away!
  You're a star Jonathan AC Roberts!
  Thanks,
  Eamon