Dynamic Query to Display data in different format using SQL

1

Hello All,

I need help writing SQL query to display data in different format Dynamically. Here is the current format of the data

WC Name Version Weekday Start Time End Time Break Capacity utilization
ABC 1 Mon 8:00:00 17:00:00 1:00:00 40
ABC 1 Tue 7:00:00 16:00:00 2:00:00 20
ABC 2 Mon 6:00:00 16:00:00 1:00:00 70
ABC 2 Tue 5:00:00 19:00:00 3:00:00 10
DEF 1 Mon 3:00:00 17:00:00 1:00:00 60
DEF 1 Tue 7:00:00 16:00:00 2:00:00 20
DEF 2 Mon 6:00:00 16:00:00 1:00:00 30
DEF 2 Tue 5:00:00 19:00:00 2:00:00 10

I need data to be displayed to below format.

WC Name Version Weekday Mon Tue
ABC 1 Start Time 08:00:00 7:00:00
ABC 1 End Time 17:00:00 16:00:00
ABC 1 Break 1:00:00 2:00:00
ABC 1 utilization 40 20
ABC 2 Start Time 6:00:00 5:00:00
ABC 2 End Time 16:00:00 19:00:00
ABC 2 Break 1:00:00 3:00:00
ABC 2 utilization 70 10
DEF 1 Start Time 3:00:00 7:00:00
DEF 1 End Time 17:00:00 16:00:00
DEF 1 Length of breaks 1:00:00 2:00:00
DEF 1 utilization 60 20
DEF 2 Start Time 6:00:00 5:00:00
DEF 2 End Time 16:00:00 19:00:00
DEF 2 Break 1:00:00 2:00:00
DEF 2 utilization 30 10

Can you please help? I have tried union and temp table logic and did not work. Thank you so much in advance.

2 
CREATE TABLE #t
(
	WCName varchar(20) NOT NULL
	,[Version] int NOT NULL
	,[Weekday] char(3) NOT NULL
	,StartTime time NOT NULL
	,EndTime time NOT NULL
	,[Break] time NOT NULL
	,Utilization int NOT NULL
	,PRIMARY KEY (WCName, [Version], Weekday)
);
INSERT INTO #t
VALUES ('ABC', 1, 'Mon', '8:00:00', '17:00:00', '1:00:00', 40)
	,('ABC', 1, 'Tue', '7:00:00', '16:00:00', '2:00:00', 20)
	,('ABC', 2, 'Mon', '6:00:00', '16:00:00', '1:00:00', 70)
	,('ABC', 2, 'Tue', '5:00:00', '19:00:00', '3:00:00', 10)
	,('DEF', 1, 'Mon', '3:00:00', '17:00:00', '1:00:00', 60)
	,('DEF', 1, 'Tue', '7:00:00', '16:00:00', '2:00:00', 20)
	,('DEF', 2, 'Mon', '6:00:00', '16:00:00', '1:00:00', 30)
	,('DEF', 2, 'Tue', '5:00:00', '19:00:00', '2:00:00', 10);

select * from #t;

SELECT T.WCName, T.[Version], M.Measure
	/* Could use sql_variant instead of varchar(8) */
	,MAX
	(
		CASE
			WHEN T.[Weekday] = 'Mon' AND M.MOrder = 1
			THEN CAST(T.StartTime AS varchar(8))
			WHEN T.[Weekday] = 'Mon' AND M.MOrder = 2
			THEN CAST(T.EndTime AS varchar(8))
			WHEN T.[Weekday] = 'Mon' AND M.MOrder = 3
			THEN CAST(T.[Break] AS varchar(8))
			WHEN T.[Weekday] = 'Mon' AND M.MOrder = 4
			THEN CAST(T.Utilization AS varchar(8))
		END
	) AS Mon
	,MAX
	(
		CASE
			WHEN T.[Weekday] = 'Tue' AND M.MOrder = 1
			THEN CAST(T.StartTime AS varchar(8))
			WHEN T.[Weekday] = 'Tue' AND M.MOrder = 2
			THEN CAST(T.EndTime AS varchar(8))
			WHEN T.[Weekday] = 'Tue' AND M.MOrder = 3
			THEN CAST(T.[Break] AS varchar(8))
			WHEN T.[Weekday] = 'Tue' AND M.MOrder = 4
			THEN CAST(T.Utilization AS varchar(8))
		END
	) AS Tue
FROM #t T
	CROSS JOIN
	(
		VALUES (1, 'StartTime')
			,(2, 'EndTime')
			,(3, 'Break')
			,(4, 'Utilization')
	) M (MOrder, Measure)
GROUP BY T.WCName, T.[Version], M.MOrder, M.Measure
ORDER BY T.WCName, T.[Version], M.MOrder;
3

Thank you for the code. Can you please let me know how to get values dynamically instead of inserting values manually?

4

I am not quite sure what you mean.

If you mean inserting data into #t, it is just test data so replace #t with you table name in the main query.

If you mean getting the column names for measures dynamically then they can be obtained by something like:

SELECT C.[name]
FROM sys.columns C
	JOIN sys.tables T
		ON C.[object_id] = T.[object_id]
WHERE SCHEMA_NAME(T.[schema_id]) = 'YourSchemaName'
	AND T.[name] = 'YourTableName'
	AND C.column_id > 3

Building the dynamic SQL is conceptually straightforward but can be fiddly and time consuming to get right; something I am not prepared to do for a forum post. I presume you are being paid to do this so spend the time or hire a consultant.

1 Like