Agreegation in CrossTab

Hi Experts,

  Here am using the Web intelligence. I have cross tab  which is displaying State,Product line and Revenue. where as I have multiple product lines which are displaying. out of which I would like to group few objects and sum it .

 For example: I have Accessories,City Skirts,City Trousers,leather,overcoats in this I would like to display the City Skirts+City Trousers together in one column.

Thank you so much for your time.

Regards,
MR
Maruthi Rao YarapathineniAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Maruthi Rao YarapathineniAuthor Commented:
0
Maruthi Rao YarapathineniAuthor Commented:
find the attached report file.
0
Hamed NasrRetired IT ProfessionalCommented:
Here is an idea, expressed in Access SQL.
Assume table: a (adesc, type, value)
  Sample data:
adesc	type	value
California	Accessories	15
California	City Skirts	15
California	City Trousers	20
DC	Accessories	15
DC	City Skirts	10
DC	City Trousers	10
Florida	Accessories	20

Open in new window


Create 4 queries:
aggregate_2_columns_1 - from table a excluding the two unrequired values
SELECT a.adesc, a.type, a.value
FROM a
WHERE (((a.type) Not In ("City Skirts","City Trousers")));

adesc	type 	value
Florida	Accessories	20
California	Accessories	15
DC	Accessories	15

Open in new window

aggregate_2_columns_2 - from table a containing the sum of aggregating the 2 columns
SELECT a.adesc, "City Skirts + City Trousers" AS type, Sum(a.value) AS SumOfvalue
FROM a
WHERE (((a.type)="City Skirts" Or (a.type)="City Trousers"))
GROUP BY a.adesc, "City Skirts + City Trousers";

adesc	type	   SumOfvalue
California	City Skirts + City Trousers	35
DC	City Skirts + City Trousers	20

Open in new window

aggregate_2_columns_3 - UNION both queries aggregate_2_columns_1  and
aggregate_2_columns_2
SELECT aggregate_2_columns_1.adesc, aggregate_2_columns_1.type, aggregate_2_columns_1.value
FROM aggregate_2_columns_1
UNION ALL SELECT  aggregate_2_columns_2.adesc, aggregate_2_columns_2.type, aggregate_2_columns_2.SumOfvalue
FROM aggregate_2_columns_2;

adesc	type 	value
Florida	Accessories	20
California	Accessories	15
DC	Accessories	15
California	City Skirts + City Trousers	35
DC	City Skirts + City Trousers	20

Open in new window

aggregate_2_columns_crosstab - from aggregate_2_columns_3
TRANSFORM Sum(aggregate_2_columns_3.value) AS SumOfvalue
SELECT aggregate_2_columns_3.adesc
FROM aggregate_2_columns_3
GROUP BY aggregate_2_columns_3.adesc
PIVOT aggregate_2_columns_3.type;

adesc	Accessories	City Skirts + City Trousers
California	15	35
DC	15	20
Florida	20	

Open in new window

0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

Vitor MontalvãoMSSQL Senior EngineerCommented:
It would help if you tell us with database are you using (MSSQL, MySQL, Oracle, DB2, Sybase, Access, Excel files, ...?) and also which application is at the frontend.
0
Maruthi Rao YarapathineniAuthor Commented:
Sure. Back end is the MSSQL and Front end is SAP Business Objects Web intelligence.

Actually I would like to see


 A B C D are the columns, I would like to see the display as A,B+C,C,B+D . More details are attached as a file with figures.
Requirement.xlsx
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry for taking so long to answer.
I don't have your original query but based on the Excel you just need to do something like:
SELECT Accessories,	
(CitySkirts, CitySkirts + CityTrousers) AS CitySkirtsTrousers, 
Leather, 
(CitySkirts + OverCoats) AS CitySkirtsOverCoats
FROM TableName

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SAP

From novice to tech pro — start learning today.