SQL Server SQL pivot one of the the columns [closed]

von4xj4u  于 2023-03-22  发布在  其他
关注(0)|答案(1)|浏览(149)

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
iyr7buue

iyr7buue1#

You can use CROSS APPLY (VALUES...) to "stack the values" using something like:

select B.Branchid , R.Rm, B.ReportDate 
from [Hierarchy].[dbo].[Branch_Coverage_BusBkg] B
cross apply (
    values (1, B.RM1), (2, B.RM2)
) R(Seq, Rm)
where BBO is not null
and ReportDate = (
    select max(ReportDate)
    from [Hierarchy].[dbo].[Branch_Coverage_BusBkg]
)

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:

select B.Branchid , R.Rm, B.ReportDate 
from [Hierarchy].[dbo].[Branch_Coverage_BusBkg] B
outer apply (
    select 1 AS Seq, B.RM1 AS RM where B.RM1 is not null
    union all
    select 2 AS Seq, B.RM2 AS RM where B.RM2 is not null
) R
where BBO is not null
and ReportDate = (
    select max(ReportDate)
    from [Hierarchy].[dbo].[Branch_Coverage_BusBkg]
)
order by B.Branchid, R.Seq

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.

-- Drops branch if both rooms are null.
select Branchid , Rm, ReportDate 
from Branch_Coverage_BusBkg B
unpivot (
    Rm FOR RmCol IN (B.Rm1, B.Rm2)
) UNPVT
where BBO is not null
and ReportDate = (
    select max(ReportDate)
    from Branch_Coverage_BusBkg
)
order by Branchid, RmCol

See this db<>fiddle for a working demo with some extra test cases.

相关问题