SQL help pivot vertical to horizontal

• jarkkonen

  SSC Journeyman

  Points: 92

  More actions

  December 6, 2016 at 1:18 am

  #315579

  Hi

  I have SQL database where I have Boxes and 6 or 4 bottle serials inside boxes. I to have these like

  BoxID1, ItemCode, Batch, Netweight, Grossweight,Bottle1,Bottle2,Bottle3

  BoxID2, ItemCode, Batch, Netweight, Grossweight,Bottle1,Bottle2,Bottle3

  BoxID3, ItemCode, Batch, Netweight, Grossweight,Bottle1,Bottle2,Bottle3..... to get it exported to text file (one per boxId)

  so basicly I need to have sql pivot vertical to horizontal or something like it but cannot

  

  Can anyone advice? Thanks!

  br Jake

• Eirikur Eiriksson

  SSC Guru

  Points: 182948

  More actions

  December 6, 2016 at 1:31 am

  #1916153

  Start by having a look at these two articles

  

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]

  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]

• jarkkonen

  SSC Journeyman

  Points: 92

  More actions

  December 6, 2016 at 1:55 am

  #1916156

  Thanks, but I'm on real hurry...Otherwise I would let some SQL expert to to this.

• J Livingston SQL

  SSC Guru

  Points: 51550

  More actions

  December 6, 2016 at 3:33 am

  #1916172

  as an example....maybe something like this ?

  CREATE TABLE somedata(

  BoxID VARCHAR(2)

  ,BottleID VARCHAR(4)

  ,ItemCode VARCHAR(4)

  ,Batch INT

  );

  INSERT INTO somedata(BoxID,BottleID,ItemCode,Batch) VALUES ('A1','1000','ABC1',900);

  INSERT INTO somedata(BoxID,BottleID,ItemCode,Batch) VALUES ('A1','1001','ABC1',900);

  INSERT INTO somedata(BoxID,BottleID,ItemCode,Batch) VALUES ('A1','1002','ABC1',900);

  INSERT INTO somedata(BoxID,BottleID,ItemCode,Batch) VALUES ('A1','1003','ABC1',900);

  INSERT INTO somedata(BoxID,BottleID,ItemCode,Batch) VALUES ('A1','1004','ABC1',900);

  INSERT INTO somedata(BoxID,BottleID,ItemCode,Batch) VALUES ('A2','2003','ABC2',999);

  INSERT INTO somedata(BoxID,BottleID,ItemCode,Batch) VALUES ('A2','2002','ABC2',999);

  INSERT INTO somedata(BoxID,BottleID,ItemCode,Batch) VALUES ('A2','2001','ABC2',999);

  INSERT INTO somedata(BoxID,BottleID,ItemCode,Batch) VALUES ('A2','2000','ABC2',999);

  WITH cte AS (

  SELECT BoxID,

  BottleID,

  ItemCode,

  Batch,

  ROW_NUMBER() OVER(PARTITION BY BoxID ORDER BY BottleId) rn

  FROM somedata

  )

  SELECT BoxID,

  ItemCode,

  Batch,

  MAX(CASE WHEN rn = 1 THEN BottleId ELSE '' END) B1,

  MAX(CASE WHEN rn = 2 THEN BottleId ELSE '' END) B2,

  MAX(CASE WHEN rn = 3 THEN BottleId ELSE '' END) B3,

  MAX(CASE WHEN rn = 4 THEN BottleId ELSE '' END) B4,

  MAX(CASE WHEN rn = 5 THEN BottleId ELSE '' END) B5,

  MAX(CASE WHEN rn = 6 THEN BottleId ELSE '' END) B6

  FROM cte

  GROUP BY BoxID, ItemCode, Batch

  ________________________________________________________________
  you can lead a user to data....but you cannot make them think
  and remember....every day is a school day