How to achieve Matrix Pivot for below layout

Question

I am trying to write a Pivot query to achieve below. Tried multiple ways but no luck. Below is a mock data. Can someone suggest best way to achieve this in T-SQL

Current Data:

Expected Output:

Answer 1

I guess you were meddling with the PIVOT operator. Stay away from it. It is difficult to understand (at least for me), and it is also limited in what you can do. Here is a general pattern for a pivot query, which easily can be extended:

SELECT Location, Date, 
       IIF(right(Interval, 2) = '00', '1', '0') + right(Interval, 3),
       MIN (CASE Hours WHEN 'H00' THEN Value END) AS H00,
       MIN (CASE Hours WHEN 'H01' THEN Value END) AS H01,
       MIN (CASE Hours WHEN 'H02' THEN Value END) AS H02,
       MIN (CASE Hours WHEN 'H03' THEN Value END) AS H03,
       MIN (CASE Hours WHEN 'H04' THEN Value END) AS H04,
       MIN (CASE Hours WHEN 'H05' THEN Value END) AS H05,
       MIN (CASE Hours WHEN 'H06' THEN Value END) AS H06
FROM  tbl
GROUP  BY Location, Date, IIF(right(Interval, 2) = '00', '1', '0') + right(Interval, 3)

Note: if you had posted CREATE TABLE for your table, and your sample data as INSERT statements rather than an image, I would have tested my solution. But since I can't copy and paste from an image, this is untested.

Answer 2

Hi @Manasa M

Depending on your needs, I wrote two pieces of code in case when and pivot, you can give them a try.

create table source(Location char(6),Date datetime,Hours varchar(20),
Interval datetime,Value varchar(20));
insert into source values
('UK','2022/1/11','H00','0:15','1.2'),
('UK','2022/1/11','H00','0:30','2.2'),
('UK','2022/1/11','H00','0:45','3.3'),
('UK','2022/1/11','H01','1:00','4.4'),
('UK','2022/1/11','H01','1:15','1.2'),
('UK','2022/1/11','H01','1:30','2.2'),
('UK','2022/1/11','H01','1:45','3.3'),
('UK','2022/1/11','H02','2:00','4.4'),
('UK','2022/1/11','H02','2:15','1.2'),
('UK','2022/1/11','H02','2:30','2.2'),
('UK','2022/1/11','H02','2:45','3.3'),
('UK','2022/1/11','H03','3:00','4.4'),
('UK','2022/1/11','H03','3:15','1.2'),
('UK','2022/1/11','H03','3:30','2.2'),
('UK','2022/1/11','H03','3:45','3.3'),
('UK','2022/1/11','H04','4:00','4.4'),
('UK','2022/1/11','H04','4:15','1.2'),
('UK','2022/1/11','H04','4:30','2.2'),
('UK','2022/1/11','H04','4:45','3.3'),
('UK','2022/1/11','H05','5:00','4.4'),
('UK','2022/1/11','H05','5:15','1.2'),
('UK','2022/1/11','H05','5:30','2.2'),
('UK','2022/1/11','H05','5:45','3.3'),
('UK','2022/1/11','H06','6:00','4.4'),
('UK','2022/1/11','H06','6:15','1.2'),
('UK','2022/1/11','H06','6:30','2.2'),
('UK','2022/1/11','H06','6:45','3.3'),
('UK','2022/1/11','H07','7:00','4.4'),
('UK','2022/1/11','H07','7:15','1.2'),
('UK','2022/1/11','H07','7:30','2.2'),
('UK','2022/1/11','H07','7:45','3.3'),
('UK','2022/1/11','H08','8:00','4.4'),
('UK','2022/1/11','H08','8:15','1.3742');

Case when:

;with CTE as(
  select *,case when datepart(mi,Interval) = 0 then '1:00'
                when datepart(mi,Interval) = 15 then '0:15'
                when datepart(mi,Interval) = 30 then '0:30'
				when datepart(mi,Interval) = 45 then '0:45' end as q,
         case when datepart(mi,Interval) = 0 
         then left(Hours,2) + convert(varchar(10),(convert (int,right(Hours,1)) - 1))
         else Hours end as p from source)
select Location,Date,q as Interval,
       max(case when p = 'H00' then Value end) as H00,
	   max(case when p = 'H01' then Value end) as H01,
	   max(case when p = 'H02' then Value end) as H02,
	   max(case when p = 'H03' then Value end) as H03,
	   max(case when p = 'H04' then Value end) as H04,
	   max(case when p = 'H05' then Value end) as H05,
	   max(case when p = 'H06' then Value end) as H06
from CTE group by Location,Date,q order by q;

Pivot:

;with T1 as(
  select *,case when datepart(mi,Interval) = 0 then '1:00'
                when datepart(mi,Interval) = 15 then '0:15'
                when datepart(mi,Interval) = 30 then '0:30'
				when datepart(mi,Interval) = 45 then '0:45' end as q,
         case when datepart(mi,Interval) = 0 
         then left(Hours,2) + convert(varchar(10),(convert (int,right(Hours,1)) - 1))
         else Hours end as p from source
),T2 as(
  select Location,Date,q as Interval,p,Value from T1)
select * from T2 pivot(max(Value) for p in (H00,H01,H02,H03,H04,H05,H06)) as t;

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.