透视 SQL 表(行到列) [英] Pivot SQL table (Rows into Columns)
问题描述
我目前正在将数据返回到我的 SQL 存储过程中的一个表中.我正在尝试将行转换为列，将列转换为行，但我正在努力这样做，因为我遵循的许多教程已经将它们布置为不同的表格.
I am currently returning data into a table in my SQL stored procedure. I am trying to pivot the rows into columns, and the columns into rows, but I am struggling to do so as a lot of the tutorials I am following to do this have laid out they're tables differently.
这是我的存储过程底部的选择:
This is the select at the bottom of my stored procedure:
select
(CASE WHEN [Fitter] IS NULL THEN (Select Distinct substring([First Name],1,1)+' '+[Second Name] from Fitters where [Fitter Id]=FitterId) ELSE Fitter END) AS Fitter,
sum([Install Sell]) as [Install Sell],
sum([Install Cost]) as [Install Cost],
sum([Install Cost Amt]) as Gross,
(select cast(CAST((TaxStatus/100 * sum([Install Cost Amt])) as decimal(18,5)) as float)) as CIS,
(select cast(CAST((VatStatus/100 * sum([Install Cost Amt])) as decimal(18,5)) as float)) as VAT,
sum([Install Cost Amt]) - (select cast(CAST((TaxStatus/100 * sum([Install Cost Amt])) as decimal(18,5)) as float)) + (select cast(CAST((VatStatus/100 * sum([Install Cost Amt])) as decimal(18,5)) as float)) as FittersPay,
sum([Install Cost Amt]) + (select cast(CAST((VatStatus/100 * sum([Install Cost Amt])) as decimal(18,5)) as float)) as Datafile
from @TempTable
group by FitterId, Fitter, TaxStatus, VatStatus
这是它返回的数据:
我希望将列作为行进行透视，将行作为列进行透视...
I would like for the columns to pivot as rows and the rows to pivot as columns...
我已经在网上四处搜索并正在努力解决这个问题，我想知道我是否可以得到一些帮助.
I have searched around online and am struggling to figure this out, i was wondering if i could get some assistance with this please.
感谢任何帮助.
推荐答案
这是通过先取消旋转然后旋转来完成的.这是一个示例，您可以调整您的数据:
This is done by first unpivoting and then pivoting. Here is an example, that you can adjust yo your data:
DECLARE @t TABLE(col0 VARCHAR(20), col1 MONEY, col2 MONEY, col3 MONEY)
INSERT INTO @t VALUES
('aaaaaa', 1, 1.2, 0),
('bbbbbb', 2, 2.2, 0),
('cccccc', 3, 3.3, 100),
('dddddd', 4, 4.4, 0)
SELECT * FROM @t
SELECT * FROM @t
UNPIVOT (a FOR b IN([col1],[col2],[col3])) up
PIVOT (MAX(a) FOR col0 IN([aaaaaa],[bbbbbb],[cccccc],[dddddd])) p
输出 1:
col0 col1 col2 col3
aaaaaa 1.00 1.20 0.00
bbbbbb 2.00 2.20 0.00
cccccc 3.00 3.30 100.00
dddddd 4.00 4.40 0.00
输出 2:
b aaaaaa bbbbbb cccccc dddddd
col1 1.00 2.00 3.00 4.00
col2 1.20 2.20 3.30 4.40
col3 0.00 0.00 100.00 0.00
这取决于您的数据类型，但您可能需要动态进行.网站上有很多例子.只需搜索dynamic pivoting.
It depends on type of your data, but you could need to do it dynamically. There is plenty of examples on the site. Just search for dynamic pivoting.
像这样:
select * from (
select
(CASE WHEN [Fitter] IS NULL THEN (Select Distinct substring([First Name],1,1)+' '+[Second Name] from Fitters where [Fitter Id]=FitterId) ELSE Fitter END) AS Fitter,
sum([Install Sell]) as [Install Sell],
sum([Install Cost]) as [Install Cost],
sum([Install Cost Amt]) as Gross,
(select cast(CAST((TaxStatus/100 * sum([Install Cost Amt])) as decimal(18,5)) as float)) as CIS,
(select cast(CAST((VatStatus/100 * sum([Install Cost Amt])) as decimal(18,5)) as float)) as VAT,
sum([Install Cost Amt]) - (select cast(CAST((TaxStatus/100 * sum([Install Cost Amt])) as decimal(18,5)) as float)) + (select cast(CAST((VatStatus/100 * sum([Install Cost Amt])) as decimal(18,5)) as float)) as FittersPay,
sum([Install Cost Amt]) + (select cast(CAST((VatStatus/100 * sum([Install Cost Amt])) as decimal(18,5)) as float)) as Datafile
from @TempTable
group by FitterId, Fitter, TaxStatus, VatStatus) t
UNPIVOT (a FOR b IN([Install Sell],[Install Cost],[Gross]/*,...*/)) up
PIVOT (MAX(a) FOR Fitter IN([D Page],[J Hopley]/*,...*/)) p
这篇关于透视 SQL 表(行到列)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
