CODEWITHSUNDEEP
sqlselectdb2
adam
adam

Reputation: 2970

SQL query which reformats results

Here is the example 'sales' table of data enter image description here

Here is the desired output Result Set enter image description here

Here is an example of the undesired Result Set that would be generated from this SQL statement.

SELECT Vendor, SUM(Markdown), SUM(Regular), SUM(Promotion), SUM(Returned)
FROM sales
GROUP BY Vendor, Date

enter image description here

Is there a way to get the desired result set through just SQL?

We are running a SQL DB2 database on an IBM iSeries.

I do realize this is a very odd way to try to do this... we are just trying to find a way to get the result set back as needed, without having to do any manual conversion of the results through code.

Upvotes: 2

Views: 132

Answers (1)

john
john

Reputation: 581

you need to use UNION statement

try,

SELECT Vendor, 'Markdown' as Type,  SUM(Markdown) as Amount 
FROM sales 
GROUP BY Vendor, Date

UNION

SELECT Vendor, 'Regular' as Type, SUM(Regular) as Amount  
FROM sales 
GROUP BY Vendor, Date

UNION

SELECT Vendor, 'Promotion' as Type, SUM(Promotion) as Amount 
FROM sales 
GROUP BY Vendor, Date

UNION

SELECT Vendor, 'Returned' as Type, SUM(Returned) as Amount 
FROM sales 
GROUP BY Vendor, Date

Upvotes: 6

Related Questions