December 17, 2013 at 2:23 pm
I have a set of tables that look like what I have shown below.
Could you let me know how I can achive the desired output ?
CREATE TABLE #ABC([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductA INT);
CREATE TABLE #DEF([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductB INT);
CREATE TABLE #GHI([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductC INT);
INSERT #ABC VALUES (2013,1,'PPP',1);
INSERT #ABC VALUES (2013,1,'QQQ',2);
INSERT #ABC VALUES (2013,2,'PPP',3);
INSERT #DEF VALUES (2013,1,'QQQ',4);
INSERT #DEF VALUES (2013,1,'RRR',5);
INSERT #DEF VALUES (2013,2,'PPP',6);
INSERT #GHI VALUES (2013,1,'QQQ',7);
INSERT #GHI VALUES (2013,2,'RRR',8);
INSERT #GHI VALUES (2013,3,'PPP',9);
INSERT #GHI VALUES (2013,3,'QQQ',10);
I have a query currently that looks like this . @Month and @Year are supplied as parameters
SELECT
-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
(SELECT SUM(SalesofProductA) FROM #ABC WHERE [Year]=T.[Year] AND [Month]=T.[Month]) AS [Sum_SalesofProductA]
,(SELECT SUM(SalesofProductB) FROM #DEF WHERE [Year]=T.[Year] AND [Month]=T.[Month]) AS [Sum_SalesofProductB]
,(SELECT SUM(SalesofProductC) FROM #GHI WHERE [Year]=T.[Year] AND [Month]=T.[Month]) AS [Sum_SalesofProductC]
FROM (
-- this selects a list of all possible dates.
SELECT [Year],[Month] FROM #ABC
where Year = @Year and Month = @Month
UNION
SELECT [Year],[Month] FROM #DEF
where Year = @Year and Month = @Month
UNION
SELECT [Year],[Month] FROM #GHI
where Year = @Year and Month = @Month
) AS T;
Right now I see an output like this : for a particular value of @Month and @Year
SalesofProductA, SalesofProductB, SalesofProductC
What I would like to see is :
[Customer],SalesofProductA, SalesofProductB, SalesofProductC
Does anyone know how it can be done ?
December 17, 2013 at 2:42 pm
If you know how many products you can do something like this.
with MyData as
(
select SUM(SalesofProductA) as Sales, Customer, 'A' as ProductType
from #abc
where Year = @Year and Month = @Month
group by Customer
union all
select SUM(SalesofProductB), Customer, 'B'
from #def
where Year = @Year and Month = @Month
group by Customer
union all
select SUM(SalesofProductC), Customer, 'C'
from #ghi
where Year = @Year and Month = @Month
group by Customer
)
select Customer,
MAX(case when ProductType = 'A' then Sales end) as SalesofProductA,
MAX(case when ProductType = 'B' then Sales end) as SalesofProductB,
MAX(case when ProductType = 'C' then Sales end) as SalesofProductC
from MyData
group by Customer
If however, the number of columns is dynamic you will use a similar approach but it will use some dynamic sql. You can read more about the dynamic version of this by following the link in my signature about dynamic cross tabs.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 17, 2013 at 5:39 pm
Another option that is very similar to Seans
select customer, sum(PA) AS [Sum_SalesofProductA], sum(PB) AS [Sum_SalesofProductB], sum(PC) AS [Sum_SalesofProductC]
from (
select customer, SalesofProductA PA, null PB, null PC
FROM #ABC
where Year = @Year and Month = @Month
union all
select customer, null PA, SalesofProductB PB, null PC
FROM #DEF
where Year = @Year and Month = @Month
union all
select customer, null PA, null PB, SalesofProductC PC
FROM #GHI
where Year = @Year and Month = @Month
) a
group by customer;
Of course this also is dependent on knowing the number of products, but since you have represented them as being in separate tables, I guessing you do:-)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply