SQL Server Combine multiple rows in to one with the same ID [duplicate]

wfauudbj 于 2023-11-16 发布在其他

关注(0)|答案(1)|浏览(96)

This question already has answers here:

Efficiently convert rows to columns in sql server (5 answers)
Closed 16 days ago.

I currently have the table structure below where I have a custom ID called SubNo which ties all of the records from that insert together but ideally in an SQL query i need all of the values to output as one row for each SubNo

SubNo	Type	FID	FVal
2173	SOS	YourName	J Bloggs
2173	SOS	YourDate	2023-10-30
2173	SOS	YourTime	07:30
2174	SOS	YourName	N.E Body
2174	SOS	YourDate	2023-10-31
2174	SOS	YourTime	09:00

This is what I am trying to achieve is this output

SubNo	Type	YourName	YourDate	YourTime
2173	SOS	J Bloggs	2023-10-30	07:30
2174	SOS	N.E Body	2023-10-31	09:00

I currently run the SQL query

SELECT *
FROM [dbo].[Core] 
WHERE Type = 'SOS' 
  AND FID IN ('YourName','YourDate','YourTime')
ORDER BY SubNo DESC
    OFFSET 0 ROWS FETCH FIRST 2 ROWS ONLY

which returns the first table

I have tried to use JOIN and STRING_AGG without success.

sql-server

来源：https://stackoverflow.com/questions/77389707/combine-multiple-rows-in-to-one-with-the-same-id

1条答案

按热度按时间

q3qa4bjr1#

If you know the columns in advance you can use the conditional aggregation using MAX() :

select SubNo, Type, max(case when FID = 'YourName' then FVal end) as YourName,
                    max(case when FID = 'YourDate' then FVal end) as YourDate,
                    max(case when FID = 'YourTime' then FVal end) as YourTime
from mytable
where Type = 'SOS'
group by  SubNo, Type

Demo here

赞(0）回复(0）举报 2023-11-16

我来回答

SQL Server Combine multiple rows in to one with the same ID [duplicate]

1条答案

相关问题

热门标签

最新问答