SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 split one result into 2 rows		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

HenryFulmer
Yak Posting Veteran

USA
98 Posts
Posted - 08/03/2012 :  17:50:31  Show Profile  Reply with Quote
I have a SalesHistory table for which I've written a query to summarize sales by time period

SELECT  s.Country, SUM(s.SalesAmount) as MonthlySales,d.YearMonth
FROM SalesHistory s
INNER JOIN Dates d ON s.SalesDate = d.DateID
GROUP BY s.Country, d.YearMonth
ORDER BY


What I'd like to get as a result is something like :

            04-2012     03-2012     02-2012     01-2012     12-2011
            04-2011     03-2011     02-2011     01-2011     12-2010
COUNTRY
Mexico      120000      150000      170000      104000       80000
            113500      110000      130000      125000       70000

Canada      230000      320000      270000      164000       93000
            143500      160000      145000      144000       60000


Is something like this possible using PIVOT function or any other available feature?
Thanks for your help.

visakh16
Very Important crosS Applying yaK Herder

India
47188 Posts
Posted - 08/03/2012 :  17:57:38  Show Profile  Reply with Quote
in sql you can get result like this

Mexico      120000      150000      170000      104000       80000
Mexico      113500 
...


and you can get your required output formatting by using reporting tool like reporting services and coosing suppress duplicates property for textbox showing country info

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

HenryFulmer
Yak Posting Veteran

USA
98 Posts
Posted - 08/03/2012 :  18:03:10  Show Profile  Reply with Quote
OK - thanks. So how would I get a result set that split rows by after a 12 month period and than start a new row for the same country?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47188 Posts
Posted - 08/03/2012 :  22:13:39  Show Profile  Reply with Quote 


...

group by country,datename(mm,datefield) + ' ' + datename(yy,datefield)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47188 Posts
Posted - 08/03/2012 :  22:24:07  Show Profile  Reply with Quote
and if you need all 12 months for all countries regardless of whether data is available or not, you need to have calendar table like below

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html


and use like

SELECT m.country,m.Period,
COALESCE(n.MonthlySales,0) AS MonthlySales
FROM
(
SELECT country,[Period]
FROM (SELECT DISTINCT country FROM table)t
CROSS JOIN (SELECT DATENAME(mm,m.[Date]) + ' ' + DATENAME(yyyy,m.[Date]) AS [Period] FROM dbo.CalendarTable (@yourstartdate,@yourenddate,0,1)f)u 
)m
LEFT JOIN (SELECT  s.Country, SUM(s.SalesAmount) as MonthlySales,d.YearMonth
FROM SalesHistory s
INNER JOIN Dates d ON s.SalesDate = d.DateID
GROUP BY s.Country, d.YearMonth)n
ON n.country = m.country
AND n.[YearMonth] = m.[period]


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000