Hi, I have a query that pulls out the following columns of data.
Reference StockCode Quantity SubAssembly Level 1 Code SubAssembly Level 1 Quantity SubAssembly Level 2 Code SubAssembly Level 2 Quantity SubAssembly Level 3 Code SubAssembly Level 3 Quantity
WX1-3040-1050 WX1-ASSY 99 99991 99
WX1-3040-1050 WX1-ASSY 99 99999 99
WX1-3040-1050 WX1-HARDWARE-10AMP 99 43109 99
WX1-3040-1050 WX1-HARDWARE-10AMP 99 WX1-HARDWARE 99 12056 99
WX1-3040-1050 WX1-HARDWARE-10AMP 99 WX1-HARDWARE 99 221N-WRAS 99
WX1-3040-1050 WX1-HARDWARE-10AMP 99 WX1-HARDWARE 99 UCMF12 99
WX1-3040-1050 WX1-HARDWARE-10AMP 99 WX1-HARDWARE 99 HP04 99
WX1-3040-1050 WX1-HARDWARE-10AMP 99 WX1-HARDWARE 99 PS5SQ 99
WX1-3040-1050 WX1-HARDWARE-10AMP 99 WX1-HARDWARE 99 100572 99
How would I convert the data to the following format:
Reference StockCode Quantity
WX1-3040-1050 12056 99
WX1-3040-1050 221N-WRAS 99
WX1-3040-1050 UCMF12 99
WX1-3040-1050 HP04 99
WX1-3040-1050 PS5SQ 99
WX1-3040-1050 100572 99
WX1-3040-1050 99991 99
WX1-3040-1050 99999 99
WX1-3040-1050 43109 99
Jannette:
Hi, I have a query that pulls out the following columns of data.
image.png1394x171 5.36 KB
Reference StockCode Quantity SubAssembly Level 1 Code SubAssembly Level 1 Quantity SubAssembly Level 2 Code SubAssembly Level 2 Quantity SubAssembly Level 3 Code SubAssembly Level 3 Quantity
WX1-3040-1050 WX1-ASSY 99 99991 99
WX1-3040-1050 WX1-ASSY 99 99999 99
WX1-3040-1050 WX1-HARDWARE-10AMP 99 43109 99
WX1-3040-1050 WX1-HARDWARE-10AMP 99 WX1-HARDWARE 99 12056 99
WX1-3040-1050 WX1-HARDWARE-10AMP 99 WX1-HARDWARE 99 221N-WRAS 99
WX1-3040-1050 WX1-HARDWARE-10AMP 99 WX1-HARDWARE 99 UCMF12 99
WX1-3040-1050 WX1-HARDWARE-10AMP 99 WX1-HARDWARE 99 HP04 99
WX1-3040-1050 WX1-HARDWARE-10AMP 99 WX1-HARDWARE 99 PS5SQ 99
WX1-3040-1050 WX1-HARDWARE-10AMP 99 WX1-HARDWARE 99 100572 99
How would I convert the data to the following format:
Reference StockCode Quantity
WX1-3040-1050 12056 99
WX1-3040-1050 221N-WRAS 99
WX1-3040-1050 UCMF12 99
WX1-3040-1050 HP04 99
WX1-3040-1050 PS5SQ 99
WX1-3040-1050 100572 99
WX1-3040-1050 99991 99
WX1-3040-1050 99999 99
WX1-3040-1050 43109 99
Making the terrible assumption that any that has a "Code Subassembly" will ALWAYS also have the related "Quantity", the following could work.
SELECT [Reference]
,StockCode = COALESCE([SubAssembly Level 3 Code],[SubAssembly Level 2 Code],[SubAssembly Level 1 Code],StockCode)
,Quantity = COALESCE([SubAssembly Level 3 Quantity],[SubAssembly Level 2 Quantity],[SubAssembly Level 1 Quantity],Quantity)
FROM dbo.YourTable
;
1 Like