March 16, 2012 at 9:31 am
I have a table containing some customers names and the amounts they have spent per month.
I would like to aggregate amounts in every month into 1 column called total amount and then add a field which groups the data by month. In a way almost transposing the data.
I have provided some data to illustrate.
CREATE TABLE #Test(
CustName varchar(10) NULL
,Region Varchar(5) NULL
, JanAmount numeric(19,2)NULL
, FebAmount numeric(19,2)NULL
, MarAmount numeric(19,2)NULL
, AprAmount numeric(19,2)NULL
, MayAmount numeric(19,2)NULL
, JunAmount numeric(19,2)NULL
, JulAmount numeric(19,2)NULL
, AugAmount numeric(19,2)NULL
, SepAmount numeric(19,2)NULL
, OctAmount numeric(19,2)NULL
, NovAmount numeric(19,2)NULL
, DecAmount numeric(19,2)NULL
)
INSERT INTO #Test
SELECT 'JOHN', 'NLD',100000.00,100000.00,100000.00,100000.00,200000.00,200000.00,200000.00,200000.00,200000.00,0,0,0 UNION ALL
SELECT 'MICHAEL', 'NLD',100000.00,100000.00,100000.00,200000.00,200000.00,200000.00,200000.00,0,0,0,0,0 UNION ALL
SELECT 'JAMES', 'NLD',100000.00,100000.00,100000.00,100000.00,200000.00,200000.00,200000.00,200000.00,0,0,0,0
With the data above, my output should be:
CustName, Region, Month, Amount
JOHN, NLD, JAN, 100000
JOHN, NLD, FEB, 100000
JOHN, NLD, MAR, 100000
JOHN, NLD, APR, 100000
JOHN, NLD, MAY, 200000
JOHN, NLD, JUN, 200000
JOHN, NLD, JUL, 200000
JOHN, NLD, AUG, 200000
JOHN, NLD, SEP, 200000
JOHN, NLD, OCT,0
JOHN, NLD, NOV,0
JOHN, NLD, DEC,0
MICHAEL, NLD, JAN, 100000
MICHAEL, NLD, FEB, 100000
MICHAEL, NLD, MAR, 100000
MICHAEL, NLD, APR, 200000
MICHAEL, NLD, MAY, 200000
MICHAEL, NLD, JUN, 200000
MICHAEL, NLD, JUL, 200000
MICHAEL, NLD, AUG, 0
MICHAEL, NLD, SEP, 0
MICHAEL, NLD, OCT, 0
MICHAEL, NLD, NOV, 0
MICHAEL, NLD, DEC, 0
JAMES, NLD, JAN, 100000
My issue is that I do not know how create the month field as this will only be determined from the column names.
any ideas?
March 16, 2012 at 9:36 am
this will do the trick
select custname, region, upper(left(amount,3)), amount1
from
(
select
custname, region, janamount, febamount, maramount, apramount, mayamount, junamount, julamount, augamount, sepamount, octamount, novamount, decamount
from #test) t
UNPIVOT
(amount1 for amount in (janamount, febamount, maramount, apramount, mayamount, junamount, julamount, augamount, sepamount, octamount, novamount, decamount)
) As unpvt
March 16, 2012 at 9:48 am
March 16, 2012 at 10:01 am
worked like a charm!! much appreciated.
March 16, 2012 at 11:32 pm
CELKO (3/16/2012)
>> I have a table containing some customers names and the amounts they have spent per month. <<You have no idea what first normal form means and that a table has to have a key. All NULLs means there is no way to ever have a key! In fact, you mixed columns and fields in your specs; RDBMS only has columns.
A month-within-year is a temporal value; you made it into an attribute and destroyed any data integrity.
CREATE TABLE Monthly_Customer_Summary
(customer_name VARCHAR(10) NOT NULL,
region_name VARCHAR(5) NOT NULL
CHECK(region_name IN (..)),
summary_month CHAR(10) NOT NULL
CHECK (summary_month LIKE '[21][0-9][0-9][0-9]-[01][0-9]-00'),
PRIMARY KEY (customer_name, region_name, summary_month)
month_amt DECIMAL (19,5) NOT NULL);
I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL.
Your problem is now trivial, which is usually the case with a normalized table
SELECT summary_month, SUM (month_amt) AS month_amt_tot
FROM Monthly_Customer_Summary;
You assume too much. Maybe he does actually understand what 1NF is and is just trying to fix the denormalized table he's been given. Lighten up Joe. Heh... and if you like "MySQL" so much, please go to a "MySQL" forum and beat on people there for a while.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 17, 2012 at 10:45 am
Joe I didn't realise my post would cause such controversy.
The denormalised table that i created was based on a csv source file we get from one of our vendors. It is uploaded into the loading area of our database with very little modifications in the ETL i.e converting nulls to zero, trimming text etc. We have very little control over the data we are given from the vendor
By the time the data gets into the final destination, the table where it resides is normalised with a primary key and other necessary constraints to enforce referential integrity.
I was more concerned with the T-SQL needed to manipulate the data which comes precisely in the format that was presented in the example. Hence my posting in the T-SQL part of the forum.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply