SQL help pivot vertical to horizontal

Question

Post reply

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182881 More actions · Answer 1

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 · Answer 2

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 · Answer 3

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