May 23, 2015 at 2:26 pm
Hello, I'm very new at this and apologize if this is an easy question, I've spent the past couple hours searching and I just don't know how to articulate it properly on Google to get the appropriate answer. I appreciate your help. I am using Microsoft SQL Server 2012.
I have five tables FY1, FY2, FY3, FY4 and FY5.
Each table has three columns 1) CustomerNo 2) OrderAvg_FYn 3) OrderCount_FYn
CustomerNo is unique in each table.
I need to make one big table with the columns: CustomerNo, OrderAvg_FY1, OrderCount_FY1, OrderAvg_FY2, OrderCount_FY2, OrderAvg_FY3, OrderCount_FY3, OrderAvg_FY4, OrderCount_FY4, OrderAvg_FY5, OrderCount_FY5
With each unique CustomerNo in it's own row and all of it's corresponding data from each FY table. If a customer has an order in FY1 but not in FY2 then OrderCount_FY2 should be null.
So If FY1, FY2 and FY3 are this (comma delimited...just realized the forum deletes consecutive blank spaces):
Table FY1
CustomerNo, OrderAvg_FY1, OrderCount_FY1
0155, 300, 7
0133, 700, 6
0144, 200, 2
Table FY2
CustomerNo, OrderAvg_FY2, OrderCount_FY2
0155, 100, 2
0130, 500, 4
0144, 20, 1
0011, 25, 3
Table FY3
CustomerNo, OrderAvg_FY3, OrderCount_FY3
0155, 30, 8
0133, 70, 9
0144, 500, 1
0187, 40, 3
0589, 60, 2
I need my output to look like this:
CustomerNo, OrderAvg_FY1, OrderCount_FY1, OrderAvg_FY2, OrderCount_FY2, OrderAvg_FY3, OrderCount_FY3
0155, 300, 7, 100, 2, 30, 8
0133, 700, 6, NULL, NULL, 70, 9
0144, 200, 2, 20, 1, 500, 1
0130, 500, 4, NULL, NULL, NULL, NULL
0011, NULL, NULL, 25, 3, NULL, NULL
0187, NULL, NULL, NULL, NULL, 40, 3
0589, NULL, NULL, NULL, NULL, 60, 2
If I join them I get a table with five Customer No Columns. Each table has different customers and some of the same customers and I don't want to lose any of them or their information in the final output but I need all customers in one column in the output table.
I know there has to be a simple way to do this but I'm short on SQL vocabulary. Thank you for your help. ~Brenn
May 23, 2015 at 3:29 pm
This should give you the results you're looking for...
/* ==================================================
Test Data
================================================= */
IF OBJECT_ID('tempdb..#FY1') IS NOT NULL
DROP TABLE #FY1;
CREATE TABLE #FY1 (
CustomerNo CHAR(4),
OrderAvg INT,
OrderCount INT
);
INSERT #FY1 (CustomerNo,OrderAvg,OrderCount) VALUES
('0155', 300, 7),
('0133', 700, 6),
('0144', 200, 2);
IF OBJECT_ID('tempdb..#FY2') IS NOT NULL
DROP TABLE #FY2;
CREATE TABLE #FY2 (
CustomerNo CHAR(4),
OrderAvg INT,
OrderCount INT
);
INSERT #FY2 (CustomerNo,OrderAvg,OrderCount) VALUES
('0155', 100, 2),
('0130', 500, 4),
('0144', 20, 1 ),
('0011', 25, 3 );
IF OBJECT_ID('tempdb..#FY3') IS NOT NULL
DROP TABLE #FY3;
CREATE TABLE #FY3 (
CustomerNo CHAR(4),
OrderAvg INT,
OrderCount INT
);
INSERT #FY3 (CustomerNo,OrderAvg,OrderCount) VALUES
('0155', 30, 8 ),
('0133', 70, 9 ),
('0144', 500, 1),
('0187', 40, 3 ),
('0589', 60, 2 );
IF OBJECT_ID('tempdb..#FY4') IS NOT NULL
DROP TABLE #FY4;
CREATE TABLE #FY4 (
CustomerNo CHAR(4),
OrderAvg INT,
OrderCount INT
);
INSERT #FY4 (CustomerNo,OrderAvg,OrderCount) VALUES
('0155', 30, 8 ),
('0133', 70, 9 ),
('0144', 500, 1),
('0187', 40, 3 ),
('0589', 60, 2 );
IF OBJECT_ID('tempdb..#FY5') IS NOT NULL
DROP TABLE #FY5;
CREATE TABLE #FY5 (
CustomerNo CHAR(4),
OrderAvg INT,
OrderCount INT
);
INSERT #FY5 (CustomerNo,OrderAvg,OrderCount) VALUES
('0155', 30, 8 ),
('0133', 70, 9 ),
('0144', 500, 1),
('0187', 40, 3 ),
('0589', 60, 2 );
/* ==================================================
UNION ALL Solution
================================================= */
SELECT
f.CustomerNo,
SUM(CASE WHEN f.OrigTable = 1 THEN f.OrderAvg END) AS OrderAvg_FY1,
SUM(CASE WHEN f.OrigTable = 1 THEN f.OrderCount END) AS OrderCount_FY1,
SUM(CASE WHEN f.OrigTable = 2 THEN f.OrderAvg END) AS OrderAvg_FY2,
SUM(CASE WHEN f.OrigTable = 2 THEN f.OrderCount END) AS OrderCount_FY2,
SUM(CASE WHEN f.OrigTable = 3 THEN f.OrderAvg END) AS OrderAvg_FY3,
SUM(CASE WHEN f.OrigTable = 3 THEN f.OrderCount END) AS OrderCount_FY3,
SUM(CASE WHEN f.OrigTable = 4 THEN f.OrderAvg END) AS OrderAvg_FY4,
SUM(CASE WHEN f.OrigTable = 4 THEN f.OrderCount END) AS OrderCount_FY4,
SUM(CASE WHEN f.OrigTable = 5 THEN f.OrderAvg END) AS OrderAvg_FY5,
SUM(CASE WHEN f.OrigTable = 5 THEN f.OrderCount END) AS OrderCount_FY5
FROM (
SELECT f1.CustomerNo, f1.OrderAvg, f1.OrderCount, 1 AS OrigTable FROM #FY1 f1 UNION ALL
SELECT f2.CustomerNo, f2.OrderAvg, f2.OrderCount, 2 AS OrigTable FROM #FY2 f2 UNION ALL
SELECT f3.CustomerNo, f3.OrderAvg, f3.OrderCount, 3 AS OrigTable FROM #FY3 f3 UNION ALL
SELECT f4.CustomerNo, f4.OrderAvg, f4.OrderCount, 4 AS OrigTable FROM #FY4 f4 UNION ALL
SELECT f5.CustomerNo, f5.OrderAvg, f5.OrderCount, 5 AS OrigTable FROM #FY5 f5
) f
GROUP BY
f.CustomerNo
The output...
CustomerNo OrderAvg_FY1 OrderCount_FY1 OrderAvg_FY2 OrderCount_FY2 OrderAvg_FY3 OrderCount_FY3 OrderAvg_FY4 OrderCount_FY4 OrderAvg_FY5 OrderCount_FY5
---------- ------------ -------------- ------------ -------------- ------------ -------------- ------------ -------------- ------------ --------------
0011 NULL NULL 25 3 NULL NULL NULL NULL NULL NULL
0130 NULL NULL 500 4 NULL NULL NULL NULL NULL NULL
0133 700 6 NULL NULL 70 9 70 9 70 9
0144 200 2 20 1 500 1 500 1 500 1
0155 300 7 100 2 30 8 30 8 30 8
0187 NULL NULL NULL NULL 40 3 40 3 40 3
0589 NULL NULL NULL NULL 60 2 60 2 60 2
May 23, 2015 at 6:31 pm
You may have a really good reason for wanting to have a table with this structure, but, in my opinion, it is a very bad idea. You are de-normalizing the data and that will lead to very difficult queries on this table. And what happens when FY6, FY7 and FY8 need to be added? Maintenance becomes an issue.
I think you would be better off with a table that looks something like:
CREATE TABLE Combined
(
SysID int identity(1,1) not null, --not strictly needed, but helps to make the row unique (personal preference)
FY int not null,
CustomerNo int not null,
OrderAvg int,
OrderCount int
)
With this table, you can add as many, and I am guessing here, Fiscal Years as necessary. Then you can query on specific FY's. You don't have to worry about NULL either. If the FY does not exist for a CutomerNo, then there is nothing!
So your query to load this table would look something like this:
INSERT Combined (FY, CustomerNo, OrderAvg, OrderCount)
SELECT 1, CustomerNo, OrderAvg, OrderCount
FROM FY1
INSERT Combined (FY, CustomerNo, OrderAvg, OrderCount)
SELECT 2, CustomerNo, OrderAvg, OrderCount
FROM FY2
etc . . .
Then you can add any necessary indexes/keys.
If you then need to put the data into columnar form, de-normalize it, you can use PIVOT to achieve that. But other queries will be MUCH simpler.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 24, 2015 at 10:06 am
As Linksup said, what you want to do doesn't look like a good idea.
But if you really do want to do it, here is some straightforward code that does it:
-- first set up the original tables and test data
CREATE TABLE FY1 (
CustomerNo INT,
OrderAvg_FY1 INT,
OrderCount_FY1 INT
);
INSERT FY1 (CustomerNo,OrderAvg_FY1,OrderCount_FY1) VALUES
('0155', 300, 7),
('0133', 700, 6),
('0144', 200, 2);
CREATE TABLE FY2 (
CustomerNo INT,
OrderAvg_FY2 INT,
OrderCount_FY2 INT
);
INSERT FY2 (CustomerNo,OrderAvg_FY2,OrderCount_FY2) VALUES
('0155', 100, 2),
('0130', 500, 4),
('0144', 20, 1 ),
('0011', 25, 3 );
CREATE TABLE FY3 (
CustomerNo INT,
OrderAvg_FY3 INT,
OrderCount_FY3 INT
);
INSERT FY3 (CustomerNo,OrderAvg_FY3,OrderCount_FY3) VALUES
('0155', 30, 8 ),
('0133', 70, 9 ),
('0144', 500, 1),
('0187', 40, 3 ),
('0589', 60, 2 );
CREATE TABLE FY4 (
CustomerNo INT,
OrderAvg_FY4 INT,
OrderCount_FY4 INT
);
INSERT FY4 (CustomerNo,OrderAvg_FY4,OrderCount_FY4) VALUES
('0155', 30, 8 ),
('0133', 70, 9 ),
('0144', 500, 1),
('0187', 40, 3 ),
('0589', 60, 2 );
CREATE TABLE FY5 (
CustomerNo INT,
OrderAvg_FY5 INT,
OrderCount_FY5 INT
);
INSERT FY5 (CustomerNo,OrderAvg_FY5,OrderCount_FY5) VALUES
('0155', 30, 8 ),
('0133', 70, 9 ),
('0144', 500, 1),
('0187', 40, 3 ),
('0589', 60, 2 );
--Next create the table to hold the combined data
CREATE TABLE FYall(
CustomerNo INT,
OrderAvg_FY1 INT,
OrderCount_FY1 INT,
OrderAvg_FY2 INT,
OrderCount_FY2 INT,
OrderAvg_FY3 INT,
OrderCount_FY3 INT,
OrderAvg_FY4 INT,
OrderCount_FY4 INT,
OrderAvg_FY5 INT,
OrderCount_FY5 INT,
);
-- next combine the data from the original tables and store it in the new table
WITH customers (CustomerNo) as ( -- the complete set of customer numbers
select CustomerNo from FY1 union
select CustomerNo from FY2 union
select CustomerNo from FY3 union
select CustomerNo from FY4 union
select CustomerNo from FY5
)
insert FYall select customers.CustomerNo
, FY1.OrderAvg_FY1, FY1.OrderCount_FY1
, FY2.OrderAvg_FY2, FY2.OrderCount_FY2
, FY3.OrderAvg_FY3, FY3.OrderCount_FY3
, FY4.OrderAvg_FY4, FY4.OrderCount_FY4
, FY5.OrderAvg_FY5, FY5.OrderCount_FY5
from customers
left join FY1 on customers.CustomerNo = FY1.CustomerNo
left join FY2 on customers.CustomerNo = FY2.CustomerNo
left join FY3 on customers.CustomerNo = FY3.CustomerNo
left join FY4 on customers.CustomerNo = FY4.CustomerNo
left join FY5 on customers.CustomerNo = FY5.CustomerNo ;
-- finally look at the new table to see that the data in it is the right data
select * from FYall ;
Tom
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply