Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to combine data from different tables using a query Expand / Collapse
Author
Message
Posted Tuesday, December 17, 2013 2:24 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 5:22 PM
Points: 58, Visits: 197
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 ?
Post #1523889
Posted Tuesday, December 17, 2013 2:26 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:36 AM
Points: 13,139, Visits: 11,979
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1523891
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse