December 17, 2013 at 2:24 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:26 pm
Duplicate post. Original thread here. http://www.sqlservercentral.com/Forums/Topic1523885-3077-1.aspx
_______________________________________________________________
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/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply