sql - SQL rotate results from wide to vertical
问题描述
I would love some help with the best way to capture some column data and rotate it so I can store the column name and numeric value in a temp table.
The results are a single row showing a value for the columns listed here:
AccountingCode ActiveCostAllocationCode1Segment1 ActiveCostAllocationCode1Segment1Description
-------------- --------------------------------- --------------------------------------------
0 71 264
I would like to take the above query and rotate the output to look more vertical.
ColName Value
--------------------------------------------- ---------
AccountingCode 0
ActiveCostAllocationCode1Segment1 71
ActiveCostAllocationCode1Segment1Description 264
I was trying to use PIVOT / UNPIVOT but could not figure how to make it work for this case.
Any ideas?
解决方案
If you are working with SQL Sever
then you can use APPLY
:
SELECT tt.ColName, tt.val
FROM table t CROSS APPLY
( VALUES ('AccountingCode', AccountingCode),
('ActiveCostAllocationCode1Segment1', ActiveCostAllocationCode1Segment1),
('ActiveCostAllocationCode1Segment1Description', ActiveCostAllocationCode1Segment1Description)
) tt(ColName, Val);
In standard you can use UNION ALL
to UNPIVOT
the data.
推荐阅读
- angular - Ionic5 angular9 vendor-es2015.js ERROR [object Object]
- django - Inherit from abstract class Django
- android - 改造界面如何添加cookie来存储数据
- architecture - Right architecture for Authentication and Authorization with IdentityServer4
- android - Xamarin forms Android SurfaceView - still video image left behind
- vba - Powerpoint Macros: Column(unknown member) error at Columns.Width Formatting code
- angular - 只调用一次切换地图后点击
- python - 如何重新启动我的 Python Arcade 游戏以使其在游戏结束后正常运行?
- php - Prestashop 1.7.6 覆盖模块 src 文件夹中的类的正确方法
- javascript - Firebase Messaging Web getToken 在正常工作 1-2 天后返回过期令牌(无法删除)