SQL Server 2016 枢轴 [英] SQL Server 2016 Pivot
问题描述
我有一个关于 sql (MS SQL 2016) 和 pivot 功能的问题.首先让我解释一下数据结构.
I have one question regarding sql (MS SQL 2016) and pivot functionality.
First let me explain about the data structure.
tbl_Preise 的示例.每个区域(Gebiet_von、Gebiet_bis)在接力(StaffelNr)中有多个价格(Preis).所有都连接到相同的货运 (Fracht_id).每种货物可以有不同数量的中继.所有这些中继在每个区域都重复，即在 1800 - 1899 区域有一个中继 1 的价格，但是中继 1 有另一个价格/code> 区域 1900 - 1999.
Examples of tbl_Preise. There are several prices (Preis) for each area (Gebiet_von, Gebiet_bis) in relays (StaffelNr). All connected to the same freight (Fracht_id). There can be a different number of relays for each freight. All of these relays repeat for each area, so i.e. there is one price for relay 1 in area 1800 - 1899, but there is another price for relay 1 for area 1900 - 1999.
这是tbl_Preise 表的样子:
autoID Fracht_id Gebiet_von Gebiet_bis Zielland_Nr StaffelNr Preis Mindestpreis Mautkosten
16933 4 1800 1899 4 1 22,6481 0,00 0,00
16934 4 1800 1899 4 2 37,0843 0,00 0,00
16935 4 1800 1899 4 3 54,9713 0,00 0,00
16936 4 1900 1999 4 1 23,4062 0,00 0,00
16937 4 1900 1999 4 2 84,4444 0,00 0,00
现在我有另一个表tbl_Fracht_Staffeln，其中保存了继电器的数量.
Now I have another table tbl_Fracht_Staffeln where the quantity of the relay is saved.
这张表看起来像:
id fracht_id staffelNr menge
18 4 1 50
19 4 2 100
20 4 3 150
21 4 4 200
现在我想合并这些数据，这些数据可能会因每个货物的中继数量不同而有所不同.我已通过此查询完成此操作:
Now I want to combine these data, which can vary through different number of relays to each freight. I have done this via this query:
DECLARE @cols AS NVARCHAR(MAX),@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(staffelNr)
from tbl_Preise (nolock)
where fracht_id = @freightId
group by staffelNr
order by StaffelNr
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = N'
SELECT
Bezeichnung,
fracht_id,
gebiet_von,
gebiet_bis,
' + @cols + N'
from
(
select
l.Bezeichnung as Bezeichnung,
Zielland_Nr,
tbl_Preise.fracht_id,
gebiet_von,
gebiet_bis,
preis,
tbl_Preise.staffelNr as staffelNr
from
tbl_Preise (nolock)
left join
[dbo].[vw_Laender] l on tbl_Preise.Zielland_Nr = l.[Nummer]
where
tbl_Preise.Fracht_id = ' + cast(@freightId as nvarchar(100)) + '
) x
pivot
(
max(preis)
for staffelNr in (' + @cols + N')
) p
order by
gebiet_von, gebiet_bis'
exec sp_executesql @query;
这个查询给了我这个结果:
This query gives me this result:
Bezeichnung fracht_id gebiet_von gebiet_bis 1 2 3 4 5 6
Germany 4 01800 01899 NULL NULL NULL NULL NULL NULL
Germany 4 06400 06499 NULL NULL NULL NULL NULL NULL
Germany 4 1800 1899 22,6481 37,0843 54,9713 64,4062 84,4444 94,6546
Germany 4 20500 20599 17,9088 27,3983 40,8845 46,7485 61,4905 67,835
Germany 4 21200 21299 17,9088 27,3983 40,8845 46,7485 61,4905 67,835
Germany 4 21500 21599 17,9088 27,3983 40,8845 46,7485 61,4905 67,835
不要完全看价格和区号.我在我的 tbl_Preise 示例中更改了一些内容，以使关系和意义更加清晰.到现在为止还挺好.但是现在，正如您所看到的，我的表中有 staffelNr (1,2,3,4,...) 作为标题.
Don't look exactly on the prices and the area codes. I've changed some in my example of tbl_Preise to make the relation and sense more clear.
So far so good. But now, as you can see, I have the staffelNr (1,2,3,4,...) as Header in my table.
我需要表 tbl_Fracht_Staffeln 的 menge 列代替.
I need there the column
mengeof tabletbl_Fracht_Staffelninstead.
我已经尝试了一些 joins 和其他东西，但都没有奏效，因为我发现无法连接 column names (1,2,3,4...) 到表 tbl_Fracht_Staffeln.有没有办法实现这一目标?非常感谢您的帮助！
I tried already some joins and other stuff, but all did not work, because I have found no way to connect the column names (1,2,3,4...) to the table tbl_Fracht_Staffeln. Is there any way to achieve this?
Thank you very much in advance for help!
推荐答案
要做到这一点，你需要重复列标题 2 次 -
To do this you need to play with column header 2 times -
DECLARE @cols AS NVARCHAR(MAX),@query AS NVARCHAR(MAX) , @freightId as int , @cols1 AS NVARCHAR(MAX)
select @freightId = 4
select @cols = STUFF((SELECT ',' + QUOTENAME(t1.staffelNr) + ' as ' + QUOTENAME(t2.menge )
from tbl_Preise t1 (nolock)
join tbl_Fracht_Staffeln t2(nolock)
on t1.fracht_id = t2.fracht_id and t1.staffelNr = t2.staffelNr
where t1.fracht_id = @freightId
group by t1.staffelNr , t2.menge
order by t1.StaffelNr
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
select @cols1 = STUFF((SELECT ',' + QUOTENAME(staffelNr)
from tbl_Preise (nolock)
where fracht_id = @freightId
group by staffelNr
order by StaffelNr
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = N'
SELECT
fracht_id,
gebiet_von,
gebiet_bis,
' + @cols + N'
from
(
select
Zielland_Nr,
tbl_Preise.fracht_id,
gebiet_von,
gebiet_bis,
preis,
tbl_Preise.staffelNr as staffelNr
from
tbl_Preise (nolock)
where
tbl_Preise.Fracht_id = ' + cast(@freightId as nvarchar(100)) + '
) x
pivot
(
max(preis)
for staffelNr in (' + @cols1 + N')
) p
order by
gebiet_von, gebiet_bis'
print @query
exec sp_executesql @query;
这篇关于SQL Server 2016 枢轴的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
