September 3, 2024 at 8:39 pm
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 9:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
September 13, 2024 at 1:25 pm
You would probably start with something like this example of a dynamic pivot:
https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/
Your MyTable data doesn't match your expected output, but I think the idea that each HH has every triple (F, T, Val) on the same line, so you would need to add something (like a triple number) to the output column names.
HH F1 T1 VAL1 F 2 T2 VAL2 F3 T3 VAL3
2 6 7 11 6 10 1
3 6 7 17 7 6 12 6 10 1
A search for "dynamic pivot" here on sqlservercentral returns many good examples and some previously solved questions that are similar.
September 13, 2024 at 2:04 pm
;WITH cte_1 AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY HH ORDER BY HH) AS row_num
FROM MyTable
)
SELECT
HH,
MAX(CASE WHEN row_num = 1 THEN F END) AS F_1,
MAX(CASE WHEN row_num = 1 THEN T END) AS T_1,
MAX(CASE WHEN row_num = 1 THEN Val END) AS Val_1,
MAX(CASE WHEN row_num = 2 THEN F END) AS F_2,
MAX(CASE WHEN row_num = 2 THEN T END) AS T_2,
MAX(CASE WHEN row_num = 2 THEN Val END) AS Val_2,
MAX(CASE WHEN row_num = 3 THEN F END) AS F_3,
MAX(CASE WHEN row_num = 3 THEN T END) AS T_3,
MAX(CASE WHEN row_num = 3 THEN Val END) AS Val_3 /*, ...*/
FROM cte_1
GROUP BY HH
ORDER BY HH
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 13, 2024 at 2:47 pm
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply