Pivot problems... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Pivot problems...

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-11-13 : 03:55:21

Hi all,

every day our financial controllers are creating a report with the sum of revenue per country for the previous day. This list of countries is usually pretty static but every now and then there are new countries popping up that hasn't generated revenue before. Is it possible to generate a pivot statement that has a dynamic list of countries?? The following is what I'm using today but as you can see UK is not appearing for the 12th even though it's data is in the table->

DECLARE @table table (
  CountryCode char(2),
  Revenue decimal(8, 2),
  RevenueDate datetime
)

INSERT INTO @table
SELECT 'NO', 51478, '2008-11-11' UNION ALL SELECT 'SE', 478, '2008-11-11' UNION ALL
SELECT 'DK', 2154, '2008-11-11' UNION ALL SELECT 'NO', 51478, '2008-11-12' UNION ALL
SELECT 'FI', 74123, '2008-11-12' UNION ALL SELECT 'UK', 124, '2008-11-12' UNION ALL
SELECT 'SE', 8741, '2008-11-12'

SELECT * FROM @table

SELECT 
  [SE] = [SE],
  [NO] = [NO],
  [FI] = [FI],
  [DK] = [DK]
FROM (
    SELECT 
      CountryCode,
      Revenue = SUM(Revenue)
    FROM @table
--    WHERE RevenueDate = '2008-11-11'
    WHERE RevenueDate = '2008-11-12'
    GROUP BY CountryCode
  ) AS a
PIVOT
  (
  SUM(Revenue)
  FOR CountryCode IN ([SE], [NO], [FI], [DK])
  ) AS b

How can I solve this without adding all country codes to my query...?

- Lumbago

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-13 : 04:03:48

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-11-13 : 04:06:11

Thanx madhi, I'll look into it right away :)

- Lumbago

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-11-13 : 07:15:08

Worked perfectly madhi, thank you. Too bad I didn't realize that it's impossibe to use in reporting services since the list of columns is dynamic and the report designer is unable to handle this

- Lumbago

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-13 : 07:40:58

quote:
Originally posted by Lumbago

Worked perfectly madhi, thank you. Too bad I didn't realize that it's impossibe to use in reporting services since the list of columns is dynamic and the report designer is unable to handle this

- Lumbago

Yes. It is not possible to design report where datasource dynamically changes. Doesnt SSRS support Cross-tab feature? If so use there

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-13 : 08:54:49

quote:
Originally posted by Lumbago

Worked perfectly madhi, thank you. Too bad I didn't realize that it's impossibe to use in reporting services since the list of columns is dynamic and the report designer is unable to handle this

- Lumbago

no need to pivot and bring in that case from db. just use a matrix container and use column group as the column based on which you want to pivot (CountryCode in above sample code) and apply whatever more grouping you want in row. just in case you dont want anymore grouping rowwise just use static group in row.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources