SQL Server 201 - Converting columns to rows

Transact-SQL
1

Hi, I have a query that pulls out the following columns of data.

image.png1394×171 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

2

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