SQL Server pivot table to show as a row (combining two queries)

Question

I am using SQL Server. I created a query:

SELECT
    p.[accountNumber],  
    pf.fundid
FROM 
    [dbo].[Property] p
LEFT JOIN
    dbo.propertyfundassessment pf ON p.id = pf.propertyid
LEFT JOIN
    dbo.fund f ON pf.fundid = f.id
WHERE
    p.accountnumber = '238492348' AND p.taxyear = 2018

This shows the data as:

    accountNumber  fundid
--------------------------
1   238492348       1004
2   238492348       1005
3   238492348       1006
4   238492348       1007
5   238492348       1008
6   238492348       1009
7   238492348       1022
8   238492348       1339

I am trying to some how pivot the table in order to make the table look like this instead:

   accountNumber  adv1  adv2  adv3  adv4  adv5  adv6  adv7  adv8
-----------------------------------------------------------------
1   238492348     1004  1005  1006  1007  1008  1009  1022  1339

Can someone assist me in how I can do this with SQL Server?

I have found this:

SELECT *
FROM
   (SELECT ColNbr = ROW_NUMBER() OVER(PARTITION BY accountNUmber ORDER BY fundid,accountNumber)
        ,accountNumber
        ,fundid
    FROM
      #tmpS a
   ) src PIVOT(MAX(src.fundid) FOR src.ColNbr IN(       [1]
                                           ,[2]
                                           ,[3]
                                           ,[4]
                                           ,[5]
                                           ,[6]
                                           ,[7]
                                           ,[8]
                                           ,[9]
                                           ,[10]
                                           ,[11]
                                           ,[12]
                                           ,[13]
                                           ,[14]
                                           ,[15]
                                           ,[16]
                                           ,[17]
                                           ,[18]
                                           ,[19]
                                           ,[20]
                                           ,[21]
                                           ,[22]
                                           ,[23]
                                           ,[24]
                                           ,[25]
                                           )) pvt

And I am trying to combine the two queries to have it do it on the fly. Instead of trying to create a #tmpS table.

Any help would be greatly appreciated!

Answer 1

You can combine both queries like the following:

;WITH StartingData AS
(
    SELECT
        [accountNumber] = p.[accountNumber],
        fundid = pf.fundid,
        FundRanking = ROW_NUMBER() OVER (PARTITION BY p.[accountNumber] ORDER BY pf.fundid ASC) -- The order by is crucial for the pivot ordering later
    FROM 
        [dbo].[Property] p
        left join dbo.propertyfundassessment pf on p.id = pf.propertyid
        left join dbo.fund f on pf.fundid = f.id
    where 
        p.taxyear = 2018
)
SELECT
    P.accountNumber,
    P.[1],
    P.[2],
    P.[3],
    P.[4],
    P.[5],
    P.[6],
    P.[7],
    P.[8],
    P.[9],
    P.[10],
    P.[11],
    P.[12],
    P.[13],
    P.[14],
    P.[15],
    P.[16],
    P.[17],
    P.[18],
    P.[19],
    P.[20],
    P.[21],
    P.[22],
    P.[23],
    P.[24],
    P.[25]
FROM
    StartingData AS S
    PIVOT (
        MAX(S.fundid) FOR S.FundRanking IN (
            [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], 
            [11], [12], [13], [14], [15], [16], [17], [18], 
            [19], [20], [21], [22], [23], [24], [25])
    ) AS P

Keep in mind what Tim said, either you hard-code the number of fundid you will be pivoting (this example hard-codes from 1 to 25), or you will have to use dynamic SQL to generate a pivot statement that dynamically pivots up to the max amount of fundid you might have to a particular accountNumber. This will generate a column for each inicial record, by accountNumber.

---

To make it dynamic use the following:

IF OBJECT_ID('tempdb..#AccountFunds') IS NOT NULL
    DROP TABLE #AccountFunds

SELECT
    [accountNumber] = p.[accountNumber],
    fundid = pf.fundid,
    FundRanking = ROW_NUMBER() OVER (PARTITION BY p.[accountNumber] ORDER BY pf.fundid ASC) -- The order by is crucial for the pivot ordering later
INTO
    #AccountFunds
FROM 
    [dbo].[Property] p
    left join dbo.propertyfundassessment pf on p.id = pf.propertyid
    left join dbo.fund f on pf.fundid = f.id
where 
    p.taxyear = 2018
    AND p.[accountNumber] = '238492348'


DECLARE @PivotValues VARCHAR(MAX) = STUFF (
    (
        SELECT DISTINCT
            ',' + QUOTENAME(CONVERT(VARCHAR(10), A.FundRanking))
        FROM
            #AccountFunds AS A
        ORDER BY
            ',' + QUOTENAME(CONVERT(VARCHAR(10), A.FundRanking)) ASC
        FOR XML
            PATH ('')
    ),
    1, 1, '')

DECLARE @SelectColumnAlias VARCHAR(MAX) = STUFF (
    (
        SELECT
            ',P.' + QUOTENAME(CONVERT(VARCHAR(10), A.FundRanking)) + ' AS adv'  + CONVERT(VARCHAR(10), A.FundRanking)
        FROM
            #AccountFunds AS A
        GROUP BY
            A.FundRanking
        ORDER BY
            A.FundRanking ASC
        FOR XML
            PATH ('')
    ),
    1, 1, '')

DECLARE @DynamicSQL VARCHAR(MAX) = '
    SELECT
        P.AccountNumber,
        ' + @SelectColumnAlias + '
    FROM
        #AccountFunds AS A
        PIVOT (
            MAX(A.fundid) FOR A.FundRanking IN (
            ' + @PivotValues + ')
        ) AS P '

--PRINT (@DynamicSQL) -- Use Print to check the query

EXEC (@DynamicSQL)

If you check the value of @PivotValues it's something like the following:

[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25]

The value of @SelectColumnAlias is:

P.[1] AS adv1,P.[2] AS adv2,P.[3] AS adv3,P.[4] AS adv4,P.[5] AS adv5,P.[6] AS adv6,P.[7] AS adv7,P.[8] AS adv8,P.[9] AS adv9,P.[10] AS adv10,P.[11] AS adv11,P.[12] AS adv12,P.[13] AS adv13,P.[14] AS adv14,P.[15] AS adv15,P.[16] AS adv16,P.[17] AS adv17,P.[18] AS adv18,P.[19] AS adv19,P.[20] AS adv20,P.[21] AS adv21,P.[22] AS adv22,P.[23] AS adv23,P.[24] AS adv24,P.[25] AS adv25

And finally the full expression:

SELECT
        P.AccountNumber,
        P.[1] AS adv1,P.[2] AS adv2,P.[3] AS adv3,P.[4] AS adv4,P.[5] AS adv5,P.[6] AS adv6,P.[7] AS adv7,P.[8] AS adv8,P.[9] AS adv9,P.[10] AS adv10,P.[11] AS adv11,P.[12] AS adv12,P.[13] AS adv13,P.[14] AS adv14,P.[15] AS adv15,P.[16] AS adv16,P.[17] AS adv17,P.[18] AS adv18,P.[19] AS adv19,P.[20] AS adv20,P.[21] AS adv21,P.[22] AS adv22,P.[23] AS adv23,P.[24] AS adv24,P.[25] AS adv25
    FROM
        #AccountFunds AS A
        PIVOT (
            MAX(A.fundid) FOR A.FundRanking IN (
            [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25])
        ) AS P 

Answer 2

Try this dynamic sql

IF OBJECT_ID('Tempdb..#temp')IS NOT NULL
DROP TABLE #temp

;WITH CTE(Id,accountNumber,fundid)
AS
(
SELECT 1,238492348,1004 UNION ALL
SELECT 2,238492348,1005 UNION ALL
SELECT 3,238492348,1006 UNION ALL
SELECT 4,238492348,1007 UNION ALL
SELECT 5,238492348,1008 UNION ALL
SELECT 6,238492348,1009 UNION ALL
SELECT 7,238492348,1022 UNION ALL
SELECT 8,238492348,1339
)
SELECT * ,'adv'+CAST(ROW_NUMBER()OVER(ORDER BY  (SELECT 1)) AS VARCHAR(10)) AS tcol  INTO  #temp FROM CTE


DECLARE @Columns nvarchar(max),@IsnullColumns nvarchar(max)
        ,@Sql  nvarchar(max)


SELECT @Columns= STUFF((SELECT ', '+tcol   FROM #temp FOR XML PATH ('')),1,1,'')
SELECT @IsnullColumns=STUFF((SELECT ', '+'MAX('+QUOTENAME(tcol)+') AS ' +QUOTENAME(tcol)  FROM #temp FOR XML PATH ('')),1,1,'')
SET @Sql='
SELECT accountNumber  ,'+@IsnullColumns+'
FROM
(SELECT * FROM #temp
    ) AS SRC
    PIVOT 
    (MAX(fundid) FOR tcol IN ('+@Columns+')
    ) AS PVT
    GROUP BY accountNumber'
    PRINT @Sql
    EXEC (@Sql)

Result

 accountNumber  adv1  adv2  adv3  adv4  adv5  adv6  adv7  adv8
-----------------------------------------------------------------
  238492348     1004  1005  1006  1007  1008  1009  1022  1339