我有三张桌子。
PeriodTable(始终为四年)
**ID Year**
1 2016
2 2017
3 2018
4 2019
StatusTable(始终为四个状态)
**ID Status**
1 Completed
2 Open
3 New
4 Declained
交易表
**Year AccountName TotalAmount Status**
2019 King 1000000.00 Declained
2019 BlueBird 3800000.00 Completed
2018 Heartly Inc 250000.00 Open
2016 ABB Data 1500000.00 Completed
2016 Delta 500000.00 Declained
预期产出:总数。状态来自TransactionTable * 4年。在这种情况下为20行。
**Year AccountName TotalAmount Status**
2019 King 1000000.00 Declained
2019 BlueBird 3800000.00 Completed
2019 NULL NULL Open
2019 NULL NULL Completed
2019 NULL NULL Declained
2018 NULL NULL Declained
2018 NULL NULL Completed
2018 Heartly Inc 250000.00 Open
2018 NULL NULL Completed
2018 NULL NULL Declained
2017 NULL NULL Declained
2017 NULL NULL Completed
2017 NULL NULL Open
2017 NULL NULL Completed
2017 NULL NULL Declained
2016 NULL NULL Declained
2016 NULL NULL Completed
2016 NULL NULL Open
2016 ABB Data 1500000.00 Completed
2016 Delta 500000.00 Declained
我尝试了许多来自Google的解决方案,但没有任何解决方案。是否可以通过透视功能实现,因为我在这里不需要聚合选项。
尝试这样的事情:
select
p.Year,
case when p.Year <> t.Year then null else t.AccountName end as AccountName,
case when p.Year <> t.Year then null else t.TotalAmount end as TotalAmount,
t.Status
from #period as p
cross join #trans as t
order by p.Year desc
您可以在SQL Fiddle上对其进行测试
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句