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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy