SQL Server: Transpose rows to columns without PIVOT

In this section we will see how to transpose rows to columns without PIVOT concept. Rather than explanation I will show with an example.
Execute below queries to create a sample table
CREATE TABLE package
(
itemnumber INT,
qty INT,
uom VARCHAR(10)
)

INSERT INTO package (itemnumber,qty,uom) VALUES (1111,1,'EA')
INSERT INTO package (itemnumber,qty,uom) VALUES (1111,10,'BX')
INSERT INTO package (itemnumber,qty,uom) VALUES (1111,100,'CA')
INSERT INTO package (itemnumber,qty,uom) VALUES (2222,1,'EA')
INSERT INTO package (itemnumber,qty,uom) VALUES (2222,15,'BX')
INSERT INTO package (itemnumber,qty,uom) VALUES (2222,30,'CA')
INSERT INTO package (itemnumber,qty,uom) VALUES (2222,60,'CARTON')
INSERT INTO package (itemnumber,qty,uom) VALUES (3333,1,'EA')
INSERT INTO package (itemnumber,qty,uom) VALUES (3333,12,'DZ')

SELECT * FROM package
itemnumber qty uom
1111 1 EA
1111 10 BX
1111 100 CA
2222 1 EA
2222 15 BX
2222 30 CA
2222 60 CARTON
3333 1 EA
3333 12 DZ

The below query will transpose rows to columns.
SELECT itemnumber,
MAX(CASE WHEN serial = 1 THEN qty END) qty1,
MAX(CASE WHEN serial = 1 THEN uom END) uom1,
MAX(CASE WHEN serial = 2 THEN qty END) qty2,
MAX(CASE WHEN serial = 2 THEN uom END) uom2,
MAX(CASE WHEN serial = 3 THEN qty END) qty3,
MAX(CASE WHEN serial = 3 THEN uom END) uom3,
MAX(CASE WHEN serial = 4 THEN qty END) qty4,
MAX(CASE WHEN serial = 4 THEN uom END) uom4,
MAX(CASE WHEN serial = 5 THEN qty END) qty5,
MAX(CASE WHEN serial = 5 THEN uom END) uom5
FROM
(
SELECT itemnumber,qty,uom,
ROW_NUMBER() OVER(PARTITION BY itemnumber
ORDER BY itemnumber,qty) serial
FROM package
) d
GROUP BY itemnumber
itemnumber qty1 uom1 qty2 uom2 qty3 uom3 qty4 uom4 qty5 uom5
1111 1 EA 10 BX 100 CA NULL NULL NULL NULL
2222 1 EA 15 BX 30 CA 60 CARTON NULL NULL
3333 1 EA 12 DZ NULL NULL NULL NULL NULL NULL

No comments:

Post a Comment