Need help with SQL pivot query

Other SQL Server Topics
1

Hello,

I need help coming up with the query to pivot [Table1] into [Table2]

[Table1]

LotNumber Code Qty
5295289 KX-1.7 1
5294679 AX-1.6 1
5294699 AX-1.1 1
5294679 KX-1.1 1
5294701 AX-1.3 1
5294694 AX-1.3 1
5294693 KX-1.5 1
5294696 AX-1.1 1
5294696 AX-1.1 1
5294697 AX-6.2 1
5294697 AX-1.4 1
5295290 KX-1.2 1
[Table2]
Lot Number AX-1.1 AX-1.3 AX-1.4 AX-1.6 AX-6.2 KX-1.1 KX-1.2 KX-1.5 KX-1.7
5295289 1 1
5294679 1
5294699 1
5294701 2
5294694 1 1
5294693 1
5294696 1
5294697 1
5295290 1
2

Can the list of Codes change or is it always a specific list?

Btw, please post data as below in the future because that gives everyone usable data for SQL.


CREATE TABLE #Table1 ( LotNumber int NOT NULL, Code varchar(20) NOT NULL, Qty int NULL )
INSERT INTO #Table1 VALUES
    (5295289,	'KX-1.7',	1),
    (5294679,	'AX-1.6',	1),
    (5294699,	'AX-1.1',	1),
    (5294679,	'KX-1.1',	1),
    (5294701,	'AX-1.3',	1),
    (5294694,	'AX-1.3',	1),
    (5294693,	'KX-1.5',	1),
    (5294696,	'AX-1.1',	1),
    (5294696,	'AX-1.1',	1),
    (5294697,	'AX-6.2',	1),
    (5294697,	'AX-1.4',	1),
    (5295290,	'KX-1.2',	1)
3

Will use the standard format for future reference.

The codes are a specific list that gets added to over time.

4

I greatly prefer CROSS TAB to pivot.


CREATE TABLE #Table1 ( LotNumber int NOT NULL, Code varchar(20) NOT NULL, Qty int NULL )
INSERT INTO #Table1 VALUES
    (5295289,	'KX-1.7',	1),
    (5294679,	'AX-1.6',	1),
    (5294699,	'AX-1.1',	1),
    (5294679,	'KX-1.1',	1),
    (5294701,	'AX-1.3',	1),
    (5294694,	'AX-1.3',	1),
    (5294693,	'KX-1.5',	1),
    (5294696,	'AX-1.1',	1),
    (5294696,	'AX-1.1',	1),
    (5294697,	'AX-6.2',	1),
    (5294697,	'AX-1.4',	1),
    (5295290,	'KX-1.2',	1)

SELECT
    LotNumber,
    MAX(CASE WHEN Code = 'AX-1.1' THEN Qty END) AS [AX-1.1],
    MAX(CASE WHEN Code = 'AX-1.3' THEN Qty END) AS [AX-1.3],
    MAX(CASE WHEN Code = 'AX-1.4' THEN Qty END) AS [AX-1.4],
    MAX(CASE WHEN Code = 'AX-1.6' THEN Qty END) AS [AX-1.6],
    MAX(CASE WHEN Code = 'AX-6.2' THEN Qty END) AS [AX-6.2],
    MAX(CASE WHEN Code = 'KX-1.1' THEN Qty END) AS [KX-1.1],
    MAX(CASE WHEN Code = 'KX-1.2' THEN Qty END) AS [KX-1.2],
    MAX(CASE WHEN Code = 'KX-1.5' THEN Qty END) AS [KX-1.5],
    MAX(CASE WHEN Code = 'KX-1.7' THEN Qty END) AS [KX-1.7]
FROM #Table1 t
GROUP BY LotNumber
ORDER BY LotNumber
5

this worked out perfectly, thank you!