Dynamic SQL Columns Generation

Question

I have a table ProductTransDetailPassenger the structure is

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

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       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

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

http://stackoverflow.com/questions/17230388/dynamic-columns-generation-on-the-basis-of-rows-of-a-table-in-sql — Mukund Kn, Jun 18 '15 at 11:52

score 1 · Accepted Answer · answered Jun 18 '15 at 13:26

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

enter image description here

score -1 · Answer 2 · answered Jun 18 '15 at 13:09

-1

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.

answered Jun 18 '15 at 13:09

benjamin moskovits

5,261
1
12
22

This is a comment, NOT an answer. – Stephan Jun 18 '15 at 16:45
I dont want to engage in this further but suggesting that someone not do something, that they will probably regret is an answer. – benjamin moskovits Jun 18 '15 at 16:52
One, it is not answer because it doesn't answer the question. I do agree this would be bad if he was storing his data in this denormalized manner, but he's looking for a query so for all we know this could just be for a report, which is totally fine. – Stephan Jun 18 '15 at 17:11

Dynamic SQL Columns Generation

2 Answers2