Pivot Report - Dynamic Columns

Question

Post reply

Pivot Report - Dynamic Columns

Roy Rogers

SSCertifiable

Points: 7712

August 12, 2005 at 8:58 am

#165675

SET NOCOUNT ON

/* My Table */

DECLARE @MyHistory TABLE

(

DataDate DATETIME,

AreaCode VARCHAR(10),

SoldMcs INT,

InstalledMcs INT,

RunningMcs INT

)

/* My Data */

INSERT @MyHistory

SELECT '08/08/2005', '6200', 344, 11, 0 UNION

SELECT '08/08/2005', '6400', 260, 13, 5 UNION

SELECT '08/08/2005', '6600', 134, 0, 0 UNION

SELECT '08/08/2005', '6800', 249, 25, 0 UNION

SELECT '08/08/2005', '7000', 120, 1, 1 UNION

SELECT '08/08/2005', '7400', 32, 0, 0 UNION

SELECT '08/08/2005', '7600', 5, 0, 0 UNION

SELECT '08/08/2005', '7800', 37, 1, 0 UNION

SELECT '08/08/2005', '8200', 92, 13, 0 UNION

SELECT '08/08/2005', '8800', 84, 4, 1 UNION

SELECT '08/08/2005', '9000', 136, 4, 0 UNION

SELECT '08/08/2005', '10200', 98, 2, 0 UNION

SELECT '08/08/2005', '10600', 200, 1, 0 UNION

SELECT '08/08/2005', '10800', 283, 18, 0 UNION

SELECT '08/08/2005', '11000', 310, 3, 1 UNION

SELECT '08/08/2005', '11200', 47, 0, 1 UNION

SELECT '08/08/2005', '11400', 387, 2, 0 UNION

SELECT '08/08/2005', '11800', 197, 3, 1 UNION

SELECT '08/08/2005', '12200', 137, 2, 2 UNION

SELECT '08/08/2005', '12400', 242, 0, 2 UNION

SELECT '08/08/2005', '12600', 277, 6, 1 UNION

SELECT '08/08/2005', '12800', 295, 2, 0 UNION

SELECT '08/08/2005', '13000', 149, 2, 1 UNION

SELECT '08/08/2005', '13200', 126, 3, 3 UNION

SELECT '08/08/2005', '13400', 60, 0, 0 UNION

SELECT '08/08/2005', '16000', 2, 0, 0 UNION

SELECT '08/10/2005', '6200', 347, 11, 0 UNION

SELECT '08/10/2005', '6400', 263, 13, 5 UNION

SELECT '08/10/2005', '6600', 134, 0, 0 UNION

SELECT '08/10/2005', '6800', 249, 25, 0 UNION

SELECT '08/10/2005', '7000', 120, 1, 1 UNION

SELECT '08/10/2005', '7400', 32, 0, 0 UNION

SELECT '08/10/2005', '7600', 5, 0, 0 UNION

SELECT '08/10/2005', '7800', 37, 1, 0 UNION

SELECT '08/10/2005', '8200', 92, 13, 0 UNION

SELECT '08/10/2005', '8400', 164, 10, 2 UNION

SELECT '08/10/2005', '8600', 96, 8, 1 UNION

SELECT '08/10/2005', '8800', 84, 4, 1 UNION

SELECT '08/10/2005', '9000', 138, 4, 0 UNION

SELECT '08/10/2005', '10200', 99, 2, 0 UNION

SELECT '08/10/2005', '10600', 204, 1, 0 UNION

SELECT '08/10/2005', '10800', 287, 18, 0 UNION

SELECT '08/10/2005', '11000', 312, 3, 1 UNION

SELECT '08/10/2005', '11200', 49, 0, 1 UNION

SELECT '08/10/2005', '11400', 389, 2, 0 UNION

SELECT '08/10/2005', '11800', 199, 3, 1 UNION

SELECT '08/10/2005', '12200', 141, 2, 2 UNION

SELECT '08/10/2005', '12400', 243, 0, 2 UNION

SELECT '08/10/2005', '12600', 281, 6, 1 UNION

SELECT '08/10/2005', '12800', 298, 2, 0 UNION

SELECT '08/10/2005', '13000', 149, 2, 1 UNION

SELECT '08/10/2005', '13200', 126, 3, 3 UNION

SELECT '08/10/2005', '13400', 60, 0, 0 UNION

SELECT '08/10/2005', '16000', 2, 0, 0 UNION

SELECT '08/10/2005', '1740', 1, 0, 0 UNION

SELECT '06/19/2005', '8600', 82, 7, 0 UNION

SELECT '06/19/2005', '8800', 68, 3, 1 UNION

SELECT '06/19/2005', '9000', 121, 1, 0 UNION

SELECT '06/19/2005', '10200', 76, 1, 0 UNION

SELECT '06/19/2005', '10600', 170, 1, 0 UNION

SELECT '06/19/2005', '10800', 238, 18, 0 UNION

SELECT '06/19/2005', '11000', 284, 3, 0 UNION

SELECT '06/19/2005', '11200', 43, 0, 0 UNION

SELECT '06/19/2005', '11400', 340, 2, 0 UNION

SELECT '06/19/2005', '11800', 179, 2, 1 UNION

SELECT '06/19/2005', '12200', 120, 2, 1 UNION

SELECT '06/19/2005', '12400', 220, 0, 2 UNION

SELECT '06/19/2005', '12600', 245, 3, 1 UNION

SELECT '06/19/2005', '12800', 263, 2, 0 UNION

SELECT '06/19/2005', '13000', 125, 2, 1 UNION

SELECT '06/19/2005', '13200', 110, 3, 1 UNION

SELECT '06/19/2005', '13400', 47, 0, 0 UNION

SELECT '06/19/2005', '16000', 2, 0, 0 UNION

SELECT '06/20/2005', '7600', 3, 0, 0 UNION

SELECT '06/20/2005', '7800', 33, 0, 0 UNION

SELECT '06/20/2005', '8200', 80, 10, 0 UNION

SELECT '06/20/2005', '8400', 142, 8, 2 UNION

SELECT '06/20/2005', '8600', 82, 7, 0 UNION

SELECT '06/20/2005', '8800', 68, 3, 1 UNION

SELECT '06/20/2005', '9000', 121, 1, 0 UNION

SELECT '06/20/2005', '10200', 76, 1, 0 UNION

SELECT '06/20/2005', '10600', 170, 1, 0 UNION

SELECT '06/20/2005', '10800', 238, 18, 0 UNION

SELECT '06/20/2005', '11000', 284, 3, 0 UNION

SELECT '06/20/2005', '11200', 43, 0, 0 UNION

SELECT '06/20/2005', '11400', 340, 2, 0 UNION

SELECT '08/08/2005', '8400', 162, 10, 2 UNION

SELECT '08/08/2005', '8600', 95, 8, 1 UNION

SELECT '06/21/2005', '16000', 2, 0, 0 UNION

SELECT '06/17/2005', '6200', 294, 11, 0 UNION

SELECT '06/17/2005', '6400', 227, 10, 5 UNION

SELECT '06/17/2005', '6600', 126, 0, 0 UNION

SELECT '06/17/2005', '6800', 215, 10, 0 UNION

SELECT '06/17/2005', '7000', 102, 1, 0 UNION

SELECT '06/17/2005', '7400', 29, 0, 0 UNION

SELECT '06/17/2005', '7600', 3, 0, 0 UNION

SELECT '06/17/2005', '7800', 33, 0, 0 UNION

SELECT '06/17/2005', '8200', 80, 10, 0 UNION

SELECT '06/17/2005', '8400', 142, 8, 2 UNION

SELECT '06/17/2005', '8600', 80, 6, 0 UNION

SELECT '06/17/2005', '8800', 68, 3, 1 UNION

SELECT '06/17/2005', '9000', 121, 1, 0 UNION

SELECT '06/17/2005', '10200', 76, 1, 0 UNION

SELECT '06/17/2005', '10600', 170, 1, 0 UNION

SELECT '06/17/2005', '10800', 238, 18, 0 UNION

SELECT '06/17/2005', '11000', 283, 3, 0 UNION

SELECT '06/17/2005', '11200', 42, 0, 0 UNION

SELECT '06/17/2005', '11400', 340, 2, 0 UNION

SELECT '06/17/2005', '11800', 179, 2, 1 UNION

SELECT '06/17/2005', '12200', 120, 2, 1 UNION

SELECT '06/17/2005', '12400', 217, 0, 2 UNION

SELECT '06/17/2005', '12600', 244, 3, 1 UNION

SELECT '06/17/2005', '12800', 263, 2, 0 UNION

SELECT '06/17/2005', '13000', 124, 2, 1 UNION

SELECT '06/17/2005', '13200', 110, 3, 1 UNION

SELECT '06/17/2005', '13400', 47, 0, 0 UNION

SELECT '06/17/2005', '16000', 2, 0, 0 UNION

SELECT '06/18/2005', '7400', 29, 0, 0 UNION

SELECT '06/18/2005', '7600', 3, 0, 0 UNION

SELECT '06/18/2005', '7800', 33, 0, 0 UNION

SELECT '06/18/2005', '8200', 80, 10, 0 UNION

SELECT '06/18/2005', '8400', 142, 8, 2 UNION

SELECT '06/18/2005', '8600', 80, 7, 0 UNION

SELECT '06/18/2005', '8800', 68, 3, 1 UNION

SELECT '06/18/2005', '9000', 121, 1, 0 UNION

SELECT '06/18/2005', '10200', 76, 1, 0 UNION

SELECT '06/18/2005', '10600', 170, 1, 0 UNION

SELECT '06/18/2005', '10800', 238, 18, 0 UNION

SELECT '06/18/2005', '11000', 284, 3, 0 UNION

SELECT '06/18/2005', '11200', 43, 0, 0 UNION

SELECT '06/18/2005', '11400', 340, 2, 0 UNION

SELECT '06/18/2005', '11800', 179, 2, 1 UNION

SELECT '06/18/2005', '12200', 120, 2, 1 UNION

SELECT '06/18/2005', '12400', 220, 0, 2 UNION

SELECT '06/18/2005', '12600', 245, 3, 1 UNION

SELECT '06/18/2005', '12800', 263, 2, 0 UNION

SELECT '06/18/2005', '13000', 125, 2, 1 UNION

SELECT '06/18/2005', '13200', 110, 3, 1 UNION

SELECT '06/18/2005', '13400', 47, 0, 0 UNION

SELECT '06/18/2005', '16000', 2, 0, 0 UNION

SELECT '06/20/2005', '6200', 296, 11, 0 UNION

SELECT '06/20/2005', '6400', 230, 10, 5 UNION

SELECT '06/20/2005', '6600', 126, 0, 0 UNION

SELECT '06/20/2005', '6800', 215, 10, 0 UNION

SELECT '06/20/2005', '7000', 102, 1, 0 UNION

SELECT '06/20/2005', '7400', 29, 0, 0 UNION

SELECT '06/19/2005', '6200', 296, 11, 0 UNION

SELECT '06/19/2005', '6400', 230, 10, 5 UNION

SELECT '06/19/2005', '6600', 126, 0, 0 UNION

SELECT '06/19/2005', '6800', 215, 10, 0 UNION

SELECT '06/19/2005', '7000', 102, 1, 0 UNION

SELECT '06/19/2005', '7400', 29, 0, 0 UNION

SELECT '06/19/2005', '7600', 3, 0, 0 UNION

SELECT '06/19/2005', '7800', 33, 0, 0 UNION

SELECT '06/19/2005', '8200', 80, 10, 0 UNION

SELECT '06/19/2005', '8400', 142, 8, 2 UNION

SELECT '06/16/2005', '6200', 291, 11, 0 UNION

SELECT '06/16/2005', '6400', 225, 10, 5 UNION

SELECT '06/16/2005', '6600', 126, 0, 0 UNION

SELECT '06/16/2005', '6800', 215, 10, 0 UNION

SELECT '06/16/2005', '7000', 102, 1, 0 UNION

SELECT '06/16/2005', '7400', 29, 0, 0 UNION

SELECT '06/16/2005', '7600', 3, 0, 0 UNION

SELECT '06/16/2005', '7800', 33, 0, 0 UNION

SELECT '06/16/2005', '8200', 80, 10, 0 UNION

SELECT '06/16/2005', '8400', 142, 8, 2 UNION

SELECT '06/16/2005', '8600', 80, 6, 0 UNION

SELECT '06/16/2005', '8800', 68, 3, 1 UNION

SELECT '06/16/2005', '9000', 121, 1, 0 UNION

SELECT '06/16/2005', '10200', 75, 1, 0 UNION

SELECT '06/16/2005', '10600', 170, 1, 0 UNION

SELECT '06/16/2005', '10800', 237, 18, 0 UNION

SELECT '06/16/2005', '11000', 282, 3, 0 UNION

SELECT '06/16/2005', '13200', 110, 3, 1 UNION

SELECT '06/16/2005', '13400', 47, 0, 0 UNION

SELECT '06/16/2005', '16000', 2, 0, 0 UNION

SELECT '06/18/2005', '6200', 296, 11, 0 UNION

SELECT '06/18/2005', '6400', 230, 10, 5 UNION

SELECT '06/18/2005', '6600', 126, 0, 0 UNION

SELECT '06/18/2005', '6800', 215, 10, 0 UNION

SELECT '06/18/2005', '7000', 102, 1, 0 UNION

SELECT '06/16/2005', '11200', 42, 0, 0 UNION

SELECT '06/16/2005', '11400', 340, 2, 0 UNION

SELECT '06/16/2005', '11800', 174, 2, 1 UNION

SELECT '06/16/2005', '12200', 120, 2, 1 UNION

SELECT '06/16/2005', '12400', 217, 0, 2 UNION

SELECT '06/16/2005', '12600', 244, 3, 1 UNION

SELECT '06/16/2005', '12800', 263, 2, 0 UNION

SELECT '06/16/2005', '13000', 124, 2, 1 UNION

SELECT '06/20/2005', '11800', 179, 2, 1 UNION

SELECT '06/20/2005', '12200', 120, 2, 1 UNION

SELECT '06/20/2005', '12400', 220, 0, 2 UNION

SELECT '06/20/2005', '12600', 245, 3, 1 UNION

SELECT '06/20/2005', '12800', 263, 2, 0 UNION

SELECT '06/20/2005', '13000', 125, 2, 1 UNION

SELECT '06/20/2005', '13200', 110, 3, 1 UNION

SELECT '06/20/2005', '13400', 47, 0, 0 UNION

SELECT '06/20/2005', '16000', 2, 0, 0 UNION

SELECT '06/21/2005', '6200', 296, 11, 0 UNION

SELECT '06/21/2005', '6400', 230, 10, 5 UNION

SELECT '06/21/2005', '6600', 126, 0, 0 UNION

SELECT '06/21/2005', '6800', 215, 10, 0 UNION

SELECT '06/21/2005', '7000', 102, 1, 0 UNION

SELECT '06/21/2005', '7400', 29, 0, 0 UNION

SELECT '06/21/2005', '7600', 3, 0, 0 UNION

SELECT '06/21/2005', '7800', 33, 0, 0 UNION

SELECT '06/21/2005', '8200', 80, 10, 0 UNION

SELECT '06/21/2005', '8400', 142, 8, 2 UNION

SELECT '06/21/2005', '8600', 82, 7, 1 UNION

SELECT '06/21/2005', '8800', 68, 3, 1 UNION

SELECT '06/21/2005', '9000', 121, 1, 0 UNION

SELECT '06/21/2005', '10200', 76, 1, 0 UNION

SELECT '06/21/2005', '10600', 170, 1, 0 UNION

SELECT '06/21/2005', '10800', 238, 18, 0 UNION

SELECT '06/21/2005', '11000', 284, 3, 0 UNION

SELECT '06/21/2005', '11200', 43, 0, 0 UNION

SELECT '06/21/2005', '11400', 340, 2, 0 UNION

SELECT '06/21/2005', '11800', 179, 2, 1 UNION

SELECT '06/21/2005', '12200', 120, 2, 1 UNION

SELECT '06/21/2005', '12400', 220, 0, 2 UNION

SELECT '06/21/2005', '12600', 245, 3, 1 UNION

SELECT '06/21/2005', '12800', 263, 2, 0 UNION

SELECT '06/21/2005', '13000', 125, 2, 1 UNION

SELECT '06/21/2005', '13200', 110, 3, 1 UNION

SELECT '06/21/2005', '13400', 47, 0, 0

/* My Need */

/*

A result set

Data Date

Area 10200

Sold

Area 10200

Installed

Area 10200

Running

Area 10600

Sold

Area 10600

Installed

Area 10600

Running

…

Area 9000

Sold

Area 9000

Installed

Area 9000

Running

06/16/2005

75

1

0

237

18

0

…

121

1

0

10/08/2005

99

2

0

204

1

0

…

138

4

0

I can hear you asking why can't you do it in the front end. Answer is some one invented a Excel export function for that only an T-SQL statement can be passed. If anyone can help with set based approch that woud be great

*/

Regards,
gova

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 1

Hello,

I have included two possible ways of building the query you are requesting. There is a minor issue regarding the number of AreaCodes and therefore the length of the dynamic query. In order to build the full query you will need to use multiple varchar variables to hold the full text.

one o the key elements to use this approach is to create two dynamic tables within the query. One table is used to drive the dynamic set of area codes. The other table is used to turn columns into rows, thus making the selection and grouping a bit easier.

Hope this helps

Wayne

-- Here is how to build a static query

SELECT DISTINCT V.DataDate,

SUM(CASE WHEN V.AreaCode = '10200' AND V.CATEGORY = 'SOLD' THEN V.UNITS END) AS 'Area 10200 Sold',

SUM(CASE WHEN V.AreaCode = '10200' AND V.CATEGORY = 'INSTALLED' THEN V.UNITS END) AS 'Area 10200 Installed',

SUM(CASE WHEN V.AreaCode = '10200' AND V.CATEGORY = 'RUNNING' THEN V.UNITS END) AS 'Area 10200 Running',

SUM(CASE WHEN V.AreaCode = '10600' AND V.CATEGORY = 'SOLD' THEN V.UNITS END) AS 'Area 10600 Sold',

SUM(CASE WHEN V.AreaCode = '10600' AND V.CATEGORY = 'INSTALLED' THEN V.UNITS END) AS 'Area 10600 Installed',

SUM(CASE WHEN V.AreaCode = '10600' AND V.CATEGORY = 'RUNNING' THEN V.UNITS END) AS 'Area 10600 Running'

FROM (SELECT DataDate, AreaCode, SoldMcs AS UNITS, 'SOLD' AS CATEGORY FROM #MyHistory UNION

SELECT DataDate, AreaCode, InstalledMcs AS UNITS, 'INSTALLED' AS CATEGORY FROM #MyHistory UNION

SELECT DataDate, AreaCode, RunningMcs AS UNITS, 'RUNNING' AS CATEGORY FROM #MyHistory) V

GROUP BY DataDate

-- Here is how to build a dynamic query

DECLARE @sql VARCHAR(8000)

SET @sql = 'SELECT DISTINCT V.DataDate ' + CHAR(13)

SELECT @sql = COALESCE(@Sql + ',', '')

+ 'SUM(CASE WHEN V.AreaCode='''

+ CAST(AreaCode AS VARCHAR)

+ ''' AND V.CATEGORY=''SOLD'' THEN V.UNITS END) AS ''Area '

+ CAST(AreaCode AS VARCHAR)

+ ' SOLD'','

+ char(13)

+ 'SUM(CASE WHEN V.AreaCode='''

+ CAST(AreaCode AS VARCHAR)

+ ''' AND V.CATEGORY=''INSTALLED'' THEN V.UNITS END) AS ''Area '

+ CAST(AreaCode AS VARCHAR)

+ ' INSTALLED'','

+ char(13)

+ 'SUM(CASE WHEN V.AreaCode='''

+ CAST(AreaCode AS VARCHAR)

+ ''' AND V.CATEGORY=''RUNNING'' THEN V.UNITS END) AS ''Area '

+ CAST(AreaCode AS VARCHAR)

+ ' RUNNING'''

+ char(13)

FROM (SELECT DISTINCT AreaCode FROM #MyHistory) AC

WHERE AC.AreaCode > 10000

ORDER BY AC.AreaCode

SET @sql = @sql + '

FROM (SELECT DataDate, AreaCode, SoldMcs AS UNITS, ''SOLD'' AS CATEGORY FROM #MyHistory UNION

SELECT DataDate, AreaCode, InstalledMcs AS UNITS, ''INSTALLED'' AS CATEGORY FROM #MyHistory UNION

SELECT DataDate, AreaCode, RunningMcs AS UNITS, ''RUNNING'' AS CATEGORY FROM #MyHistory) V

GROUP BY DataDate'

PRINT @sql

-- I think the output from the print statement is what you are looking for

noeld SSC Guru Points: 96590 More actions · Answer 2

Another warning here: you can't have more than 256 columns in an XL worksheet

* Noel

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 3

Thank You Wayne Lawton I did the solution similar to that. I thought there might be a way other than dynamic query. I guess no, since We have to spefiy the area in the case.

About 256 columns noe the data gives about 100 columns. Client is well aware of that. When the columns reach beyond the invented method to Export To Excel should be re-Written to make it across sheets.

Regards,
gova