How to declare the columns dynamically in a Select query using PIVOT

Question

I am writing a query to get the address for PersonID. Following query is working for me but it only returns with two Addresses. I want to handle the 'n' number of address with a single query. Is there any way to do this?

Many thanks

SELECT 
  PersonID, PersonName
  [Address1], [Address2]
FROM
  (
  SELECT 
    P.PersonID, 
    P.PersonName, 
    (ROW_NUMBER() OVER(PARTITION BY P.PersonID ORDER BY A.AddressID)) RowID
    FROM tblPerson
    INNER JOIN tblAddress AS A ON A.PersonID = P.PersonID
  ) AS AddressTable
PIVOT
  (
    MAX(AddressID)
    FOR RowID IN ([Address1], [Address2])
  ) AS PivotTable;

Itzik Ben-Gan (SQL Server MVP) has canonical examples of dynamic pivoting. Try googling that and you'll find a lot of resources. Also, here: http://stackoverflow.com/q/2922797/112196. Regardless, you will need to construct and execute dynamic sql to achieve this. — Pero P., Feb 22 '12 at 17:29
Possible duplicate of http://stackoverflow.com/questions/2209700/how-to-use-pivot-in-sql-server-2005-stored-procedure-joining-two-views — Aaron Bertrand, Feb 22 '12 at 17:57
You have to move to dynamic SQL because every query, when compiled, has to return a result set with a specific shape - that is, a set of named columns, with specific types, that each row will conform to. — Damien_The_Unbeliever, Feb 22 '12 at 18:06
Is there any chance you could want the addresses as comma- or otherwise-separated strings instead of individual columns? I think that approach is far less complex. — Aaron Bertrand, Feb 22 '12 at 18:10

score 6 · Accepted Answer · answered Feb 22 '12 at 18:28

Assuming the following tables and sample data:

USE tempdb;
GO

CREATE TABLE dbo.tblPerson(PersonID INT, PersonName VARCHAR(255));

INSERT dbo.tblPerson SELECT 1, 'Bob'
           UNION ALL SELECT 2, 'Charlie'
           UNION ALL SELECT 3, 'Frank'
           UNION ALL SELECT 4, 'Amore';

CREATE TABLE dbo.tblAddress(AddressID INT, PersonID INT, [Address] VARCHAR(255));

INSERT dbo.tblAddress SELECT 1,1,'255 1st Street'
            UNION ALL SELECT 2,2,'99 Elm Street'
            UNION ALL SELECT 3,2,'67 Poplar Street'
            UNION ALL SELECT 4,2,'222 Oak Ave.'
            UNION ALL SELECT 5,1,'36 Main Street, Suite 22'
            UNION ALL SELECT 6,4,'77 Sicamore Ct.';

The following query gets the results you want, and shows how it handles 0, 1 or n addresses. In this case the highest number is 3 but you can play with more addresses if you like by adjusting the sample data slightly.

DECLARE @col   NVARCHAR(MAX) = N'',
        @sel   NVARCHAR(MAX) = N'',
        @from  NVARCHAR(MAX) = N'',
        @query NVARCHAR(MAX) = N'';

;WITH m(c) AS
(
    SELECT TOP 1 c = COUNT(*)
    FROM dbo.tblAddress
    GROUP BY PersonID
    ORDER BY c DESC
)
SELECT @col = @col + ',[Address' + RTRIM(n.n) + ']',
    @sel = @sel + ',' + CHAR(13) + CHAR(10) + '[Address' + RTRIM(n.n) + '] = x' 
        + RTRIM(n.n) + '.Address',
    @from = @from + CHAR(13) + CHAR(10) + ' LEFT OUTER JOIN xMaster AS x' 
        + RTRIM(n.n) + ' ON x' + RTRIM(n.n) + '.PersonID = p.PersonID AND x' 
        + RTRIM(n.n) + '.rn = ' + RTRIM(n.n)
FROM m CROSS JOIN (SELECT n = ROW_NUMBER() OVER (ORDER BY [object_id]) 
    FROM sys.all_columns) AS n WHERE n.n <= m.c;

SET @query = N';WITH xMaster AS 
(
  SELECT PersonID, Address, 
    rn = ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY Address) 
    FROM dbo.tblAddress
)
SELECT PersonID, PersonName' + @col
+ ' FROM
    (
        SELECT p.PersonID, p.PersonName, ' + STUFF(@sel, 1, 1, '')
 + CHAR(13) + CHAR(10) + ' FROM dbo.tblPerson AS p ' + @from + '
 ) AS Addresses;';

PRINT @query;
--EXEC sp_executesql @query;

If you print the SQL you will see this result:

;WITH xMaster AS 
(
  SELECT PersonID, Address, 
    rn = ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY Address) 
    FROM dbo.tblAddress
)
SELECT PersonID, PersonName,[Address1],[Address2],[Address3] FROM
    (
        SELECT p.PersonID, p.PersonName, 
 [Address1] = x1.Address,
 [Address2] = x2.Address,
 [Address3] = x3.Address 
FROM dbo.tblPerson AS p 
 LEFT OUTER JOIN xMaster AS x1 ON x1.PersonID = p.PersonID AND x1.rn = 1
 LEFT OUTER JOIN xMaster AS x2 ON x2.PersonID = p.PersonID AND x2.rn = 2
 LEFT OUTER JOIN xMaster AS x3 ON x3.PersonID = p.PersonID AND x3.rn = 3
 ) AS Addresses;

If you execute it, you will see this:

enter image description here

I know the query to get here is an ugly mess, but your requirement dictates it. It would be easier to return a comma-separated list as I suggested in my comment, or to have the presentation tier deal with the pivoting.

How to declare the columns dynamically in a Select query using PIVOT

1 Answers1