Dynamic SQL Columns Generation

Question

I have a table ProductTransDetailPassenger the structure is

PID  FirstName   Email    Phone   NoOfAdult    NoOfChild
1      ABC       [email protected]   111        2            0
1      XYZ       [email protected]   222        2            0
2      QWE       [email protected]   333        2            1
2      RTY       [email protected]   444        2            1
2      YUI       [email protected]   555        2            1

Based on the traveler Count(i.e No.OfAdult + No.OfChild) I need to display the result as

PID FirstName-1   Email-1  Phone-1    FirstName-2  Email-2  Phone-2   FirstName-3  Email-3  Phone-3
 1    ABC       [email protected]   111           XYZ       [email protected]   222             N/A       N/A        N/A
 2    QWE       [email protected]   333           RTY       [email protected]   444             YUI       [email protected]     555     

The table may change dynamically according to the max number of travelers. For an PID with less number of travelers than the Max, the remaining columns need to be displayed as N/A

Can u please help me out with this!!

I have tried this implementation using the PIVOT, but the result is not as expected

Thanks in Advance,

The SQL Snippet

 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)

Ref: Pivot Dynamic Columns, no Aggregation

Answer 1

The query below should get you the desired results:

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

Please don't do this. You are destroying the table as a relational table, it will be very hard to do SQL queries against the table and you will have many, many null values. You have to create another table with the id, email and phone.