SQL Pivot Table Query

Question

Date TYPE TIME 01-01-2020 A 01:20 01-01-2020 A 12:30 01-01-2020 B 18:30 02-01-2020 A 10:01 02-01-2020 B 10:20 03-01-2020 A 11:01 03-01-2020 B 11:10 04-01-2020 A 11:21 04-01-2020 B 12:22 04-01-2020 B 18:21 04-01-2020 A 09:00 till month end more TYPE (AB)... more times....(different times) How can i present table in GET only TYPE value A is MIN value and TYPE B is Max value with this formate TYPE 01-01-2020 02-01-2020 03-01-2020 04-01-2020 05-01-2020 06-01-2020 07-01-2020 A 12:30 10:01 11:21 B 18:30 10:20 18:21

Amit Mohanty · Answer

First create a view which will show min and max time of dates and then use pivort on the view. CREATE VIEW view_ViewName AS SELECT [DATE] [TYPE] [TIME] = CASE WHEN [TYPE] = 'A' THEN MIN([TIME]) ELSE MAX([TIME]) END FROM [sampleTable] GROUP BY [DATE] [TYPE] SELECT * FROM ( SELECT [TYPE] [DATE] [TIME] FROM [view_ViewName] ) Results PIVOT ( MAX([TIME]) FOR [DATE] IN ([2023-03-01][2023-03-02][2023-03-03]) ) AS PivotTable

Raj Bhatt · Answer

Hi Please try Below code  SELECT TYPE MAX(CASE WHEN DATE = '2020-01-01' THEN TIME ELSE NULL END) AS '01-01-2020' MAX(CASE WHEN DATE = '2020-01-02' THEN TIME ELSE NULL END) AS '02-01-2020' MAX(CASE WHEN DATE = '2020-01-03' THEN TIME ELSE NULL END) AS '03-01-2020' MAX(CASE WHEN DATE = '2020-01-04' THEN TIME ELSE NULL END) AS '04-01-2020' MAX(CASE WHEN DATE = '2020-01-05' THEN TIME ELSE NULL END) AS '05-01-2020' MAX(CASE WHEN DATE = '2020-01-06' THEN TIME ELSE NULL END) AS '06-01-2020' MAX(CASE WHEN DATE = '2020-01-07' THEN TIME ELSE NULL END) AS '07-01-2020' FROM MY_TABLE GROUP BY TYPE;

Date	TYPE	TIME
01-01-2020	A	01:20
01-01-2020	A	12:30
01-01-2020	B	18:30
02-01-2020	A	10:01
02-01-2020	B	10:20
03-01-2020	A	11:01
03-01-2020	B	11:10
04-01-2020	A	11:21
04-01-2020	B	12:22
04-01-2020	B	18:21
04-01-2020	A	09:00
till month end	more TYPE (A,B)...	more times....(different times)

TYPE	01-01-2020	02-01-2020	03-01-2020	04-01-2020	05-01-2020	06-01-2020	07-01-2020
A	12:30	10:01		11:21
B	18:30	10:20		18:21