动态SQL列生成

Question

我有一张桌子ProductTransDetailPassenger结构是

PID  FirstName   Email    Phone   NoOfAdult    NoOfChild
1      ABC       x@.com   111        2            0
1      XYZ       y@.com   222        2            0
2      QWE       z@.com   333        2            1
2      RTY       c@.com   444        2            1
2      YUI       v@.com   555        2            1

根据旅行者计数(即No.OfAdult + No.OfChild)，我需要将结果显示为

PID FirstName-1   Email-1  Phone-1    FirstName-2  Email-2  Phone-2   FirstName-3  Email-3  Phone-3
 1    ABC       x@.com   111           XYZ       y@.com   222             N/A       N/A        N/A
 2    QWE       z@.com   333           RTY       c@.com   444             YUI       v@.com     555     

该表可根据旅客的最大数量动态变化。对于旅行次数少于最大值的PID，其余的列需要显示为N/A。

你能帮帮我吗！！

我已经尝试过使用支点来实现这个实现，但是结果并不像预期的那样。

提前谢谢，

SQL片段

 DECLARE @cols AS NVARCHAR(MAX),
 @query  AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct + char(10) + QUOTENAME(PTDP.FirstName) --+ QUOTENAME(AP.Type) + QUOTENAME(PTDP.EmailAddress) + QUOTENAME(PTDP.PhoneNumber) + QUOTENAME(DAY(GETDATE() - PTDP.Birthdate))
            FROM ProductTransactionDetailPassenger PTDP
            INNER JOIN AppParameters AP ON AP.EnumValue = PTDP.GenderTypeValue AND Ap.Context = 'Gender'
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')



SET @query = N'SELECT ' + @cols + ' from 
            (
                select 
                    PTDP.ProductTransactionDetailID  PID,
                    PTDE.ProductTransactionDetailID  ID 
                from ProductTransactionDetailPassenger PTDP
                inner join ProductTransactionDetail PTD
                    on PTD.ProductTransactionDetailID=PTDP.ProductTransactionDetailID 
                    and PTD.ParentProductTransactionDetailID=00000000-0000-0000-0000-000000000000
                INNER JOIN dbo.ProductTransactionDetailExtended PTDE ON PTDE.ProductTransactionDetailID = PTD.ProductTransactionDetailID
           ) x
            pivot 
            (
                max(x.PID) //this must be traveler count
                for ' + @cols + ' in (' + @cols + ')
            ) p '

print @query
execute(@query)

参考文献：Pivot Dynamic Columns, no Aggregation

Answer 1

下面的查询应该会得到所需的结果：

DECLARE @Temp TABLE
(
PID int,
PIDROW int,
FirstName varchar(25),
Email varchar(25),
Phone varchar(25)
)

INSERT INTO @Temp
SELECT 
PID,
DENSE_RANK() OVER (PARTITION BY PID ORDER BY PID,FirstName) PIDROW,
FirstName,
Email,
Phone
from [dbo].[ProductTransDetailPassenger];


WITH FirstName AS
(
SELECT PID,
[1], [2], [3], [4]
FROM
(SELECT PIDROW, FirstName, PID 
    FROM @Temp) AS SourceTable
PIVOT
(
MAX(FirstName)
FOR PIDROW IN ([1], [2], [3], [4])
) AS PivotTable
),
Email As
(
SELECT PID,
[1], [2], [3] , [4]
FROM
(SELECT PIDROW, Email, PID 
    FROM @Temp) AS SourceTable
PIVOT
(
MAX(Email)
FOR PIDROW IN ([1], [2], [3], [4])
) AS PivotTable
),
Phone As
(
SELECT PID,
[1], [2], [3] , [4]
FROM
(SELECT PIDROW, Phone, PID 
    FROM @Temp) AS SourceTable
PIVOT
(
MAX(Phone)
FOR PIDROW IN ([1], [2], [3], [4])
) AS PivotTable
)

SELECT 
f.[1] AS 'FirstName-1',
e.[1] AS 'Email-1', 
p.[1] AS 'Phone-1',
f.[2] AS 'FirstName-2',
e.[2] AS 'Email-2',
p.[2] AS 'Phone-2',
f.[3] AS 'FirstName-3',
e.[3] AS 'Email-3',
p.[3] AS 'Phone-3'

FROM FirstName f
 JOIN Email e on f.PID = e.PID
 JOIN Phone p on f.PID = p.PID

​

​

Answer 2

请不要这样做。您正在将该表作为关系表销毁，对该表执行SQL查询将非常困难，而且您将有许多很多空值。您必须使用id、电子邮件和电话创建另一个表。