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.
Author |
Topic |
tota00
Starting Member
10 Posts |
Posted - 2013-01-31 : 07:47:30
|
Hi!I have a table shown below (just a part of it) and need to aggregate the data as the example under the table.I can solve it if i just want the total but needs the sql-statement to return the sum for every item and country (zero if it doesn´t exists for that country and so on)TABLE and data in it--------------------Total_Active Country ReportYear ReportMonth MarketingName Manufacturer30 Finland 2012 November MTCBA-G-UF4 Multi-Tech Systems, Inc3 Finland 2012 November Treo 680 Palm1 Finland 2012 November Treo 750na Palm19 Finland 2012 November iPhone Apple1 Finland 2012 November ThinkPad T61 14 Lenovo1 Finland 2012 November CU515 LG3 Finland 2012 November W580 Sony Ericsson105 Finland 2012 November iPhone Apple165 Finland 2012 November iPhone Apple36 Finland 2012 November GM400 Ubinetics2 Finland 2012 November N/A N/A30 Sweden 2012 November MTCBA-G-UF4 Multi-Tech Systems, Inc103 Sweden 2012 November Treo 680 Palm7 Sweden 2012 November Treo 750na Palm23 Sweden 2012 November iPhone Apple165 Norway 2012 November iPhone Apple30 Norway 2012 November MTCBA-G-UF4 Multi-Tech Systems, Inc103 Norway 2012 November Treo 680 Palm7 Norway 2012 November Treo 750na Palm23 Norway 2012 November iPhone 5 AppleThe Output I need from the sql-statement is something like this and of course the month has to be november as well... and as You see some items exists more than one time like "apple IPhone" in Finland and some exits only in one country etc..(You get the idéa)-----------------MarketingName Manufacturer Finland Sweden Norway TotalMTCBA-G-UF4 Multi-Tech Systems, Inc 30 30 30 90Treo 680 Palm 3 103 103 209Treo 750na Palm 1 7 7 15iPhone Apple 289 23 165 477ThinkPad T61 14 Lenovo 1 0 0 0CU515 LG 1 0 0 1W580 Sony Ericsson 105 0 0 105iPhone 5 Apple 0 0 23 23GM400 Ubinetics 36 0 0 23 Thanks in advance // TT |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-31 : 08:31:14
|
You can use the PIVOT operator like shown below. If you have an unknown number of countries, or if the country names are not known in advance, you would have to use a dynamic query to construct the pivot. SELECT *, ISNULL(Finland,0) + ISNULL(SWeden,0) + ISNULL(Norway,0) AS TotalFROM ( SELECT Total_Active,MarketingName, Manufacturer, Country FROM TheTable ) s PIVOT (SUM(Total_active) FOR Country IN ([Finland],[Sweden],[Norway]))P |
|
|
tota00
Starting Member
10 Posts |
Posted - 2013-01-31 : 08:38:01
|
That worked like a charm :-)Thank´s a LOT James!quote: Originally posted by James K You can use the PIVOT operator like shown below. If you have an unknown number of countries, or if the country names are not known in advance, you would have to use a dynamic query to construct the pivot. SELECT *, ISNULL(Finland,0) + ISNULL(SWeden,0) + ISNULL(Norway,0) AS TotalFROM ( SELECT Total_Active,MarketingName, Manufacturer, Country FROM TheTable ) s PIVOT (SUM(Total_active) FOR Country IN ([Finland],[Sweden],[Norway]))P
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-31 : 09:42:34
|
You are very welcome tota00.Thanks for that link Visakh - very useful! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-31 : 09:46:26
|
you're wc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|