Transpose Query Help

Transact-SQL
1

Hello All
I need some help writing a query to transpose data into a format that i'm struggling to get it to work.

Grid 1
Actual Data format

Company worklocation Category Setup Processing
1920 ABCD 001 ZCOSET ZMYBTH
1920 ABCD 002 ZCOSUT ZCORUN
1920 ABCD 003 ZCOSUL ZFXLAB
1500 GFGG 001 ZCOSPL ZCOOPL
1500 GFGG 002 ZCOSAM ZMYBMG
1500 GFGG 003 ZCOSAM ZXRPY

Required Data Format

Company worklocation Category 1 Setup 1 Processing 1 Category 2 Setup 2 Processing 2 Category 3 Setup 3 Processing 3
1920 ABCD 001 ZCOSET ZMYBTH 002 ZCOSUL ZFXLAB 003 ZCOSAM ZMYBMG
1500 GFGG 001 ZCOSUT ZCORUN 002 ZCOSPL ZCOOPL 003 ZCOSAM ZXRPY

Can you please help.

Thanks

2

You can use subqueries or use a CTE (WITH common_table_expression (Transact-SQL) - SQL Server | Microsoft Learn) like this:

/* Sample data */

DROP TABLE IF EXISTS #TableData;

SELECT 1920 AS Company,	'ABCD' AS worklocation, 	'001' AS Category, 	'ZCOSET' AS Setup, 	'ZMYBTH' AS Processing
INTO #TableData
UNION
SELECT 1920, 	'ABCD',	'002',	'ZCOSUT',	'ZCORUN'
UNION
SELECT 1920, 	'ABCD',	'003',	'ZCOSUL',	'ZFXLAB'
UNION
SELECT 1500, 	'GFGG',	'001',	'ZCOSPL',	'ZCOOPL'
UNION
SELECT 1500, 	'GFGG',	'002',	'ZCOSAM',	'ZMYBMG'
UNION
SELECT 1500, 	'GFGG',	'003',	'ZCOSAM',	'ZXRPY';


SELECT * FROM #TableData;

WITH Category001 AS
(
SELECT Company, Worklocation, Category, Setup, Processing
FROM #TableData
WHERE Category='001'
), Category002 AS
(
SELECT Company, Worklocation, Category, Setup, Processing
FROM #TableData
WHERE Category='002'
)
SELECT
	C001.Company, 
	C001.Worklocation, 
	C001.Category AS Category001, 
	C001.Setup AS Setup001, 
	C001.Processing AS Processing001,
	C002.Category AS Category002, 
	C002.Setup AS Setup002, 
	C002.Processing AS Processing002
FROM Category001 C001
	INNER JOIN Category002 C002
		ON C001.Company=C002.Company AND C001.worklocation=C002.worklocation;
3

You can also use a cross-tab approach:


;WITH cte_data AS (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Company, worklocation ORDER BY Category) AS row_num 
	FROM #TableData
)
SELECT Company, worklocation,
    MAX(CASE WHEN row_num = 1 THEN Category ELSE '' END) AS [Category 1],
    MAX(CASE WHEN row_num = 1 THEN Setup ELSE '' END) AS [Setup 1],
    MAX(CASE WHEN row_num = 1 THEN Processing ELSE '' END) AS [Processing 1],
    MAX(CASE WHEN row_num = 2 THEN Category ELSE '' END) AS [Category 2],
    MAX(CASE WHEN row_num = 2 THEN Setup ELSE '' END) AS [Setup 2],
    MAX(CASE WHEN row_num = 2 THEN Processing ELSE '' END) AS [Processing 2],
    MAX(CASE WHEN row_num = 3 THEN Category ELSE '' END) AS [Category 3],
    MAX(CASE WHEN row_num = 3 THEN Setup ELSE '' END) AS [Setup 3],
    MAX(CASE WHEN row_num = 3 THEN Processing ELSE '' END) AS [Processing 3]
    /*, ...*/
FROM cte_data
GROUP BY Company, worklocation
2 Likes
4

Thank you so much. This has helped me build the query.

5

You're very welcome. Thanks for the feedback.