Rows to columns, using varied number of rows

Question

I need to turn this:

Acct    Email                    SomeCategory
0111    abc@abc.com              Commercial
0222    abc@abc.com              Commercial
0333    abc@abc.com              Commercial
0111    joe@joeblow.com          Commercial
0121    joe@joeblow.com          Residential

Into this:

Acct    Email                    SomeCategory  Acct1    Acct2   Acct3
0111    abc@abc.com              Commercial    0111     0222    0333
0111    joe@joeblow.com          Commercial    0111
0121    joe@joeblow.com          Residential   0121

There will be up to 500 fields (!). In other words, each customer record (email address + Category) may have from 1 to 500 Acct numbers.

I can't see how using a Pivot to dynamically create the Column Names from the contents of the Acct column would work because there are thousands of values in the Acct column, which SQL won't allow anyway, and I only need 500 max.

It's going to wind up in an Excel sheet.

I know it looks crazy...this wasn't my idea, a client is demanding it...

Any ideas?

Possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — xQbert, Nov 21 '16 at 18:52

score 0 · Answer 1 · answered Nov 21 '16 at 19:45

IF OBJECT_ID('tempdb..#Tbl') IS NOT NULL
    BEGIN
        DROP TABLE #Tbl
    END

CREATE TABLE #Tbl (
    Acct CHAR(4)
    ,Email VARCHAR(100)
    ,SomeCategory VARCHAR(25)
)

INSERT INTO #Tbl VALUES
('0111','abc@abc.com','Commercial')
,('0222','abc@abc.com','Commercial')
,('0333','abc@abc.com','Commercial')
,('0111','joe@joeblow.com','Commercial')
,('0121','joe@joeblow.com','Residential')

DECLARE @Cols as NVARCHAR(MAX)
DECLARE @query as NVARCHAR(MAX)

SET @Cols = STUFF(
    (
       SELECT ',' + QUOTENAME('Acct' + CAST(RowNumber AS NVARCHAR(10)))
       FROM
          (
             SELECT DISTINCT ROW_NUMBER() OVER (PARTITION BY Email, SomeCategory ORDER BY Acct) as RowNumber
             FROM
                #Tbl
          ) t
       WHERE
          t.RowNumber <= 500
       FOR XML PATH(''), TYPE
    ).value('.','NVARCHAR(MAX)')
    ,1,1, '')

SET @query = '
SELECT *
FROM
    (
       SELECT
          Acct
          ,Email
          ,SomeCategory
          ,''Acct'' + CAST((ROW_NUMBER() OVER (PARTITION BY Email, SomeCategory ORDER BY Acct)) AS NVARCHAR(10)) as Col
       FROM
          #Tbl
    ) t
    PIVOT
    (
       MAX(Acct)
       FOR col IN (' + @Cols + ')
    ) p'

EXECUTE (@query)

Rows to columns, using varied number of rows

1 Answers1