how to pivot

• Yihong

  SSChasing Mays

  Points: 624

  More actions

  May 31, 2009 at 8:36 am

  #204952

  I have a table like this:

  declare @T table

  (cid int,

  model char(4),

  shipdate datetime,

  quantity int,

  po_num char(4))

  insert into @T

  select 1, 'mod1', '5/24/06', 2, 'num1' union all

  select 2, 'mod1', '6/2/06' ,5, 'num2' union all

  select 1, 'mod2', '4/13/06', 12, 'num6' union all

  select 2, 'mod2', '7/2/06', 16, 'num4' union all

  select 3, 'mod2', '8/14/06', 11, 'num3' union all

  select 1, 'mod3', '1/10/07', 14, 'num9' union all

  select 2, 'mod3', '5/2/07' , 19, 'num8'

  select * from @T

  I need to pivot the cid = 2 as 2nd_shipment info, something like this:

  cidmodel1st_shipdate1st_quantity1st_po_num2nd_shipdate2nd_quantity2nd_po_num

  1mod12006-05-24 00:00:00.0002num12006-06-02 00:00:00.0005num2

  1mod22006-04-13 00:00:00.00012num62006-07-02 00:00:00.00016num4

  3mod22006-08-14 00:00:00.00011num3

  1mod32007-01-10 00:00:00.00014num92007-05-02 00:00:00.00019num8

  how can I pivot it? Thanks.

• Randolph West

  SSCommitted

  Points: 1588

  More actions

  June 1, 2009 at 2:20 am

  #1002596

  SQL BOL has a splendid article on how to use the PIVOT command, with examples. I'd recommend that as your first port of call.