split one result into 2 rows - 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 2008 Forums

Transact-SQL (2008)

split one result into 2 rows

Author

Topic

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2012-08-03 : 17:50:31

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

52326 Posts

Posted - 2012-08-03 : 17:57:38

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/

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2012-08-03 : 18:03:10

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-03 : 22:13:39

[code]

...

group by country,datename(mm,datefield) + ' ' + datename(yy,datefield)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-03 : 22:24:07

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources