SQL Server 2016 枢轴

我有一个问题sql（MS SQL 2016）和pivot功能。 首先让我解释一下数据结构。

示例tbl_Preise。有多种价格（Preis）对于每个区域（Gebiet_von, Gebiet_bis）在继电器（StaffelNr）。所有连接到相同的货运（Fracht_id）。每个货物可以有不同数量的继电器。所有这些中继在每个区域重复，因此中继有一个价格1在地区1800 - 1899，但是继电器还有另一个价格1对于面积1900 - 1999.

表格是这样的tbl_Preise looks:

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其中保存了继电器的数量。

该表看起来像：

id  fracht_id   staffelNr   menge
18  4           1           50
19  4           2           100
20  4           3           150
21  4           4           200

现在我想合并这些数据，这些数据可能会因每个货物的中继数量不同而有所不同。 我已经通过此查询完成了此操作：

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;

这个查询给了我这个结果：

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,...) 作为我表中的标题。

我需要那里的专栏menge表的tbl_Fracht_Staffeln反而。

我已经尝试过一些joins和其他东西，但一切都不起作用，因为我找不到连接的方法column names(1,2,3,4...) 到表中tbl_Fracht_Staffeln。有什么办法可以实现这一点吗？ 预先非常感谢您的帮助！

为此，您需要使用列标题 2 次 -

    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;