CODEWITHSUNDEEP
sqloracle-databaseoracle11gcrosstab
user2951828
user2951828

Reputation: 69

Generating cross tab reports im oracle?

How to query for reports like cross tab ? For example my table looks like the following

+----+--+--+--+
|Name|v1|v2|v3|
+----+--+--+--+
|A   |12|10|5 |
+----+--+--+--+
|B   |10|5 |20|
+----+--+--+--+

Now my query should give me the following ?

+-----+--+--+--+
|Name |v1|v2|v3|
+-----+--+--+--+
|A    |12|10|5 |
+-----+--+--+--+
|B    |10|5 |20|
+-----+--+--+--+
|Total|22|15|25|
+-----+--+--+--+

Appreciate your help

Upvotes: 1

Views: 267

Answers (2)

Melon
Melon

Reputation: 883

Use UNION to join rows: (MYSQL CODE)

SELECT name,v1,v2,v3 FROM my_table 
UNION ALL 
SELECT "total",sum(v1) as v1 ,sum(v2) as v2 ,sum(v3) as v3 FROM my_table;

watch it working here: sqlfiddle

Upvotes: 1

Krishna
Krishna

Reputation: 438

Below is similar example to your query for mysql database: 

SELECT year, SUM(profit) FROM sales GROUP BY year WITH ROLLUP;

+----+-----------+
|year|SUM(profit)|
+----+-----------+
|2000|4525       |
+----+-----------+
|2001|3010       |
+----+-----------+
|NULL|7535       |
+----+-----------+

Upvotes: 1

Related Questions