CODEWITHSUNDEEP
mysqlsqlresultset
R J.
R J.

Reputation: 1542

how to reuse the resultset in mysql query

I have result set like this after executing "select * from temp"

id | Month | value1 | value2 |
------------------------------
1  | Apr   | 100    | 150
2  | May   | 50     | 75
3  | Jan   | 50     | 75
5  | Feb   | 50     | 75
6  | mar   | 50     | 75

I want to change it to column to rows like pivot table.

i used case when statement to change it.

select
    'value1' as Field,

    SUM(CASE 
        WHEN dc.month = 'January' THEN dc.value1 
        ELSE  ''
    END)    AS January,

    SUM(CASE 
        WHEN dc.month = 'February' THEN dc.value1 
        ELSE  ''
    END)    AS February,

    SUM(CASE 
        WHEN dc.month = 'March' THEN dc.value1 
        ELSE  ''
    END)    AS March
      SUM(CASE 
        WHEN dc.month = 'April' THEN dc.value1 
        ELSE  ''
    END)    AS April,

    SUM(CASE 
        WHEN dc.month = 'May' THEN dc.value1 
        ELSE  ''
    END)    AS May
      from
      (select 
         * from 
      temp)dc

Field   | Jan | Feb | Mar | Apr | May
-------------------------------------
Value1  | 50  | 50  | 50  | 100  | 50

I want to get same result set for value2 in same query without using UNION. Because temp table has lot of data's.

Field   | Jan | Feb | Mar | Apr | May
-------------------------------------
Value1  | 50  | 50  | 50  | 100  | 50
Value1  | 75  | 75  | 75  | 150  | 75

Upvotes: 2

Views: 311

Answers (2)

Jonathan Amend
Jonathan Amend

Reputation: 12815

This solution uses a union, but only to create "fake" rows to represent each value* column, and then joins and groups by those rows:

SELECT
    `field`,

    SUM(CASE 
        WHEN dc.month = 'Jan' THEN
            CASE `field`
            WHEN 'value1' THEN dc.value1
            WHEN 'value2' THEN dc.value2
        END
        ELSE  ''
    END)    AS January,

    SUM(CASE 
        WHEN dc.month = 'Feb' THEN
            CASE `field`
            WHEN 'value1' THEN dc.value1
            WHEN 'value2' THEN dc.value2
        END
        ELSE  ''
    END)    AS February,

    SUM(CASE 
        WHEN dc.month = 'Mar' THEN
            CASE `field`
            WHEN 'value1' THEN dc.value1
            WHEN 'value2' THEN dc.value2
        END
        ELSE  ''
    END)    AS March,
      SUM(CASE 
        WHEN dc.month = 'Apr' THEN
            CASE `field`
            WHEN 'value1' THEN dc.value1
            WHEN 'value2' THEN dc.value2
        END
        ELSE  ''
    END)    AS April,

    SUM(CASE 
        WHEN dc.month = 'May' THEN
            CASE `field`
            WHEN 'value1' THEN dc.value1
            WHEN 'value2' THEN dc.value2
        END
        ELSE  ''
    END)    AS May
      FROM
      (SELECT 
         * FROM temp
      )dc
      INNER JOIN (
       SELECT 'value1' AS `field`
            UNION SELECT 'value2'
      ) AS value_columns
    GROUP BY `field`

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

If its just 2 colums i.e. value1 and value2 then you can use UNION as DEMO

select
    'value1' as Field,
    SUM(
        CASE 
        WHEN dc.month = 'Jan' THEN dc.value1 
        ELSE  ''
    END)    AS January,

    SUM(
        CASE 
        WHEN dc.month = 'Feb' THEN dc.value1 
        ELSE  ''
    END)    AS February,

    SUM(
        CASE 
        WHEN dc.month = 'Mar' THEN dc.value1 
        ELSE  ''
    END)    AS March,
      SUM(
        CASE 
        WHEN dc.month = 'Apr' THEN dc.value1 
        ELSE  ''
    END)    AS April,

    SUM(
        CASE 
        WHEN dc.month = 'May' THEN dc.value1 
        ELSE  ''
    END)AS May
from
temp dc

UNION

select
    'value2' as Field,
    SUM(
        CASE 
        WHEN dc.month = 'Jan' THEN dc.value2 
        ELSE  ''
    END)    AS January,

    SUM(
        CASE 
        WHEN dc.month = 'Feb' THEN dc.value2 
        ELSE  ''
    END)    AS February,

    SUM(
        CASE 
        WHEN dc.month = 'Mar' THEN dc.value2 
        ELSE  ''
    END)    AS March,
      SUM(
        CASE 
        WHEN dc.month = 'Apr' THEN dc.value2 
        ELSE  ''
    END)    AS April,

    SUM(
        CASE 
        WHEN dc.month = 'May' THEN dc.value2 
        ELSE  ''
    END)AS May
from temp dc

Upvotes: 0

Related Questions