Closed. This question needs details or clarity . It is not currently accepting answers.
Want to improve this question? Add details and clarify the problem by editing this post .
Closed 2 days ago.
Improve this question
I have the below query and the results. I need to be able to “stack” RM1 and RM2 if branchid is the same. Meaning instead of having four columns to have three. So if branchid is same for rm1 and rm2 then duplicate those branchids. Your help is much appreciated!
select * from
[Hierarchy].[dbo].[Branch_Coverage_BusBkg]
where BBO is not null and ReportDate = (select max(ReportDate) from [Hierarchy].[dbo].[Branch_Coverage_BusBkg])
BranchID
RM1
RM2
ReportDate
Have
Branchid. RM1. RM2. Reportdate
29273. J15. J22. 12/31/2022
Need
Branchid. Rm. reportdate
28273. J15. 12/31/2022
28273. J22. 12/31/2022
1条答案
按热度按时间iyr7buue1#
You can use
CROSS APPLY (VALUES...)
to "stack the values" using something like:The Seq value is not needed unless you care about the result for order.
If you wish to exclude one or both room values if null, you can use the following
OUTER APPLY (SELECT ... UNION ALL SELECT ...)
variation:UNPIVOT
can also be used. Sometimes the syntax can be a bit tricky, but for this simple case, it is pretty straight forward. The downside is that it will drop the entire source record if all of the unpivotted (room number) columns are null.See this db<>fiddle for a working demo with some extra test cases.