September 4, 2024 at 12:19 am
Hi guys.
I need help with query results output.
I have the following table:
create table MyTable(HH int,F int,T int,Val int)
insert into MyTable (HH,F,T,Val)
values(2,6,7,11),
(2,6,10,1),
(3,7,6,12),
(3,6,7,17),
(4,6,7,15)
I need to show those F/T values on the same HH. Something like this
HH F T VAL F T VAL F T VAL
2 6 7 11 6 10 1
3 6 7 17 7 6 12 6 10 1
4 6 7 15
Thank You in advance
September 4, 2024 at 6:09 am
A CROSS TAB query will work here. You just need to know how many sets there are
WITH cteGroup AS (
SELECT *
, rn = ROW_NUMBER() OVER (PARTITION BY mt.HH ORDER BY mt.F, mt.T, mt.Val)
FROM MyTable AS mt
)
SELECT g.HH
/**********************************************/
, F = MAX(CASE WHEN g.rn = 1 THEN g.F END)
, T = MAX(CASE WHEN g.rn = 1 THEN g.T END)
, Val = MAX(CASE WHEN g.rn = 1 THEN g.Val END)
/**********************************************/
, F = MAX(CASE WHEN g.rn = 2 THEN g.F END)
, T = MAX(CASE WHEN g.rn = 2 THEN g.T END)
, Val = MAX(CASE WHEN g.rn = 2 THEN g.Val END)
/**********************************************/
--, F = MAX(CASE WHEN g.rn = 3 THEN g.F END)
--, T = MAX(CASE WHEN g.rn = 3 THEN g.T END)
--, Val = MAX(CASE WHEN g.rn = 3 THEN g.Val END)
FROM cteGroup AS g
GROUP BY g.HH
ORDER BY g.HH;
September 4, 2024 at 12:39 pm
DesNorton
Works for me.
Thank You so much!
Regards!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply