SQL Server 2016枢纽

Question

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

的例子tbl_Preise。有几种价格（Preis）为每个区域（Gebiet_von，Gebiet_bis）在继电器（StaffelNr）。全部连接到同一货运（Fracht_id）。每种货物可以有不同数量的继电器。所有这些继电器重复每个区域，因此即有一个价格为继电器1的面积1800 - 1899，但还有另一种价格为继电器1的区域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，其中保存了继电器的数量。

该表如下所示：

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。有什么办法可以做到这一点？预先非常感谢您的帮助！

Answer 1

为此，您需要使用列标题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;