This request might seem incredibly convoluted and longwinded, so apologies in advance.
I have several tables: ITEM, FINAL_SORT and "another" table (in this case I will use the CARTON table, but there are other tables which require the same function).
We have items where each individual SKU are in the format of [range-style-style].[colour].[size] in a single string/”Item” column (e.g. AAA-TSHIRT-AA1.RED.S). In virtually the tables, the SKUs will be in this format. A “range” (i.e. the very first part of the code, AAA) could have multiple product types with different sizes (e.g. tshirts XS-XL, pants 28”-38” and so on)
If I were to do SELECT * FROM CARTON for carton 123, it would return:
| ITEM | QTY| PARENT_CONTAINER |
|------------------------|----|------------------|
| A1A-TSHIRTS-AA1.RED.S | 2 | 123 |
| A1A-TSHIRTS-AA1.RED.M | 2 | 123 |
| A1A-TSHIRTS-AA1.RED.L | 2 | 123 |
| B2B-PANTS-CC3.BLUE.32 | 3 | 123 |
| B2B-PANTS-CC3.BLUE.34 | 3 | 123 |
| B2B-PANTS-CC3.BLUE.36 | 3 | 123 |
The ITEM table is the master item list, so it contains ALL SKU colour/size combinations (e.g. that product above could have XS and XL available, it's just not packed in the above carton). The FINAL_SORT table has a "SIZE_FINAL_SORT" column which is used to order by the sizes logical size (XS, S, M, L, XL etc.), otherwise it would do it in alphabetical order.
Here is where things get far beyond my skill level. I need to pivot the CARTON output in a way that instead of one row per SKU, there is one single row per style/colour combination, and pivoted columns should be as generic "SIZE1, SIZE2, SIZE3...SIZE10" columns which represent the possible sizes for that product style, which is sorted using the FINAL_SORT table, for an output like below:
| ITEM | SIZE1 | SIZE2 | SIZE3 | SIZE4 | SIZE5 | SIZE6 | PARENT_CONTAINER |
((size represents) | XS/28 | S/30 | M/32 | L/34 | XL/36 | -/38 | )
|------------------------|--------|--------|--------|--------|--------|--------|------------------|
| A1A-TSHIRTS-AA1.RED |(ISNULL)| 2 | 2 | 2 |(ISNULL)|(ISNULL)| 123 |
| B2B-PANTS-CC3.BLUE |(ISNULL)|(ISNULL)| 3 | 3 |3 |(ISNULL)| 123 |
Is this at all achievable with the current tables? I might be able to create new tables to help with this, but I can't add columns to the above existing tables.
CREATE TABLE testITEM
(
[ITEM] NVARCHAR(30) NULL,
[ITEM_SIZE] NVARCHAR(10) NULL,
[RANGE] NVARCHAR(3) NULL
)
INSERT INTO testITEM
values
('A1A-TSHIRTS-AA1.RED.XS','XS','A1A'),
('A1A-TSHIRTS-AA1.RED.S','S','A1A'),
('A1A-TSHIRTS-AA1.RED.M','M','A1A'),
('A1A-TSHIRTS-AA1.RED.L','L','A1A'),
('A1A-TSHIRTS-AA1.RED.XL','XL','A1A'),
('A1A-TSHIRTS-AA1.BLUE.XS','XS','A1A'),
('A1A-TSHIRTS-AA1.BLUE.S','S','A1A'),
('A1A-TSHIRTS-AA1.BLUE.M','M','A1A'),
('A1A-TSHIRTS-AA1.BLUE.L','L','A1A'),
('A1A-TSHIRTS-AA1.BLUE.XL','XL','A1A'),
('A1A-TSHIRTS-BB2.WHITE.S:M','S:M','A1A'),
('A1A-TSHIRTS-BB2.WHITE.L:XL','L:XL','A1A'),
('A1A-TSHIRTS-BB2.BLACK.S:M','S:M','A1A'),
('A1A-TSHIRTS-BB2.BLACK.L:XL','L:XL','A1A'),
('B2B-PANTS-CC3.BLUE.28','28','B2B'),
('B2B-PANTS-CC3.BLUE.30','30','B2B'),
('B2B-PANTS-CC3.BLUE.32','32','B2B'),
('B2B-PANTS-CC3.BLUE.34','34','B2B'),
('B2B-PANTS-CC3.BLUE.36','36','B2B'),
('B2B-PANTS-CC3.BLUE.38','38','B2B'),
('B2B-TSHIRTS-EE5.GREEN.XS','XS','B2B'),
('B2B-TSHIRTS-EE5.GREEN.S','S','B2B'),
('B2B-TSHIRTS-EE5.GREEN.M','M','B2B'),
('B2B-TSHIRTS-EE5.GREEN.L','L','B2B'),
('C3C-BLOUSE-DD4.RED.28','28','C3C'),
('C3C-BLOUSE-DD4.RED.30','30','C3C'),
('C3C-BLOUSE-DD4.RED.32','32','C3C'),
('C3C-BLOUSE-FF5.PINK.XS','XS','C3C'),
('C3C-BLOUSE-FF5.PINK.S','S','C3C'),
('C3C-BLOUSE-FF5.PINK.M','M','C3C'),
('C3C-BLOUSE-FF5.PINK.L','L','C3C')
--**
CREATE TABLE testFINAL_SORT
(
[RANGE] NVARCHAR(3) NULL,
[ITEM_SIZE] NVARCHAR(10) NULL,
[SIZE_FINAL_SORT] INT NULL
)
INSERT INTO testFINAL_SORT
values
('A1A','XS','10000'),
('A1A','S','10001'),
('A1A','M','10002'),
('A1A','L','10003'),
('A1A','XL','10004'),
('A1A','S:M','10005'),
('A1A','L:XL','10006'),
('A1A','28','10007'),
('A1A','30','10008'),
('A1A','32','10009'),
('A1A','34','10010'),
('A1A','38','10011'),
('A1A','40','10012'),
('B2B','28','10013'),
('B2B','30','10014'),
('B2B','32','10015'),
('B2B','34','10016'),
('B2B','36','10017'),
('B2B','38','10018'),
('B2B','XS','10019'),
('B2B','S','10020'),
('B2B','M','10021'),
('B2B','L','10022'),
('B2B','XL','10023'),
('C3C','28','10024'),
('C3C','30','10025'),
('C3C','32','10026'),
('C3C','34','10027'),
('C3C','36','10028'),
('C3C','XS','10029'),
('C3C','S','10030'),
('C3C','M','10031'),
('C3C','L','10032')
--**
CREATE TABLE testCARTON
(
[ITEM] NVARCHAR(30) NULL,
[QTY] INT NULL,
[PARENT_CONTAINER] NVARCHAR(30) NULL
)
INSERT INTO testCARTON
values
('A1A-TSHIRTS-AA1.RED.S','2','123'),
('A1A-TSHIRTS-AA1.RED.M','2','123'),
('A1A-TSHIRTS-AA1.RED.L','1','123'),
('B2B-PANTS-CC3.BLUE.32','3','123'),
('B2B-PANTS-CC3.BLUE.34','3','123'),
('B2B-PANTS-CC3.BLUE.36','3','123')
Try This Pivot script it will gives your expected Result
SELECT
SUBSTRING(Item,0,CHARINDEX('.',Item)) AS Item,
MAX([SIZE1 XS/28]) AS [SIZE1 XS/28]
,MAX([SIZE2 S/30] ) AS [SIZE2 S/30]
,MAX([SIZE3 M/32] ) AS [SIZE3 M/32]
,MAX([SIZE4 L/34] ) AS [SIZE4 L/34]
,MAX([SIZE5 XL/36]) AS [SIZE5 XL/36]
,MAX([SIZE6 -/38] ) AS [SIZE6 -/38]
,PARENT_CONTAINER
FROM
(
SELECT * , CASE WHEN JoinCol IN('XS','28') THEN 'SIZE1 XS/28'
WHEN JoinCol IN('S' ,'30') THEN 'SIZE2 S/30'
WHEN JoinCol IN('M' ,'32') THEN 'SIZE3 M/32'
WHEN JoinCol IN('L' ,'34') THEN 'SIZE4 L/34'
WHEN JoinCol IN('XL','36') THEN 'SIZE5 XL/36'
WHEN JoinCol IN('-','38') THEN 'SIZE6 -/38' END AS Sizecol
FROM testFINAL_SORT s
LEFT JOIN
(
SELECT *,
REVERSE(SUBSTRING(REVERSE(Item),0,CHARINDEX('.',REVERSE(Item)))) AS JoinCol
FROM testCARTON
)dt
ON dt.JoinCol = s.ITEM_SIZE
) AS SRC
PIVOT
(
SUM(QTY) FOR Sizecol IN ([SIZE1 XS/28],[SIZE2 S/30] ,[SIZE3 M/32] ,[SIZE4 L/34] ,[SIZE5 XL/36],[SIZE6 -/38])
)AS Pvt
WHERE Item IS NOT NULL
GROUP BY SUBSTRING(Item,0,CHARINDEX('.',Item)),PARENT_CONTAINER
Result
| ITEM | SIZE1 | SIZE2 | SIZE3 | SIZE4 | SIZE5 | SIZE6 | PARENT_CONTAINER |
( | XS/28 | S/30 | M/32 | L/34 | XL/36 | -/38 | )
|------------------------|--------|--------|--------|--------|--------|--------|------------------|
| A1A-TSHIRTS-AA1.RED |(ISNULL)| 2 | 2 | 1 |(ISNULL)|(ISNULL)| 123 |
| B2B-PANTS-CC3.BLUE |(ISNULL)|(ISNULL)| 3 | 3 |3 |(ISNULL)| 123 |