Aggregating multiple columns into 1

Question

Post reply

Aggregating multiple columns into 1

eseosaoregie

SSCertifiable

Points: 6889
More actions
March 16, 2012 at 9:31 am

#253297

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?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 112965 More actions · Answer 1

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

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 2

ColdCoffee

SSC-Dedicated

Points: 39971

March 16, 2012 at 9:48 am

#1460597

I sense dynamic Cross-Tabs[/url] here!

eseosaoregie SSCertifiable Points: 6889 More actions · Answer 3

eseosaoregie

SSCertifiable

Points: 6889

March 16, 2012 at 10:01 am

#1460606

worked like a charm!! much appreciated.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

eseosaoregie SSCertifiable Points: 6889 More actions · Answer 5

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.