CODEWITHSUNDEEP
mysqldynamicpivot
Adithya Kumar
Adithya Kumar

Reputation: 159

MySQL rows into columns

For one of my projects I am facing an unique problem

Current Table Output : 

CaseKey CustomField Fruits  Electronics Sports

1       10320       Apples  null        null
1       10864       null    Laptops     null
1       10471       null    null        cricket
2       10320       Oranges null        null
2       10864       null    TV          null
2       10471       null    null        baseball

My desired table structure is to group by CaseKey

CaseKey Fruits  Electronics Sports
1       Apples  Laptops     Cricket
2       Oranges TV          BaseBall

Current Query : 

select j.pkey as CaseKey,cfv.customfield,
(CASE   
            WHEN cfv.customfield=10320
            THEN cfv.customvalue
        END)   Fruits,
        (CASE
            WHEN cfv.customfield=10864
            THEN cfv.customvalue
        END) AS Electronics,
        (
        CASE
            WHEN cfv.customfield=10310
            THEN cfv.customvalue
        END)Sports,


from 

basetable j

left join  customfieldvalue cfv on j.id=cfv.issue


       and cfv.customfield in (10320,10864,10471)

-- group by j.id

Table Structure : basetable

ID     CaseKey
 4000  1
 4001  2

customfieldvalue

ID ISSUE CUSTOMFIELD CUSTOMVALUE

1  4000  10320       Apples
2  4000  10864       Laptops
3  4000  10471       Cricket
4  4001  10320       Oranges
5  4001  10864       TV
6  4001  10471       BaseBall

Can you please let me know how to achieve this result ? I want to run this query in Tableau as CustomSQL, so am not sure if advanced SQL like dynamic SQL, Stored Procedures would work there. Looking for a traditional SQL which would achieve this result.

Upvotes: 2

Views: 60

Answers (2)

Matt
Matt

Reputation: 1407

You could use subqueries, like so:

select
    CaseKey,
    (select customvalue from customfieldvalue where issue = b.id and customfield = 10320) as Fruits,
    (select customvalue from customfieldvalue where issue = b.id and customfield = 10864) as Electronics,
    (select customvalue from customfieldvalue where issue = b.id and customfield = 10471) as Sports
from
    basetable as b

Which outputs your desired format of:

+---------+---------+-------------+----------+
| CaseKey | Fruits  | Electronics | Sports   |
+---------+---------+-------------+----------+
|       1 | Apples  | Laptops     | Cricket  |
|       2 | Oranges | TV          | Baseball |
+---------+---------+-------------+----------+

Also in available in Fiddle flavor.

Upvotes: 1

Suresh Kamrushi
Suresh Kamrushi

Reputation: 16076

Can you try this query: (it works for me, not able to crate fiddle..)

    SELECT CaseKey, CustomField, 
     IF(  `Fruits` IS NOT NULL ,  `Fruits` ,  (IF (`Electronics` IS NOT NULL,`Electronics`,`Sports` )) ) AS T
    FROM TableName

Upvotes: 1

Related Questions