MySQL rows into columns

Question

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.

can you share the structure of your table. or create a sql fiddle for it. — Suresh Kamrushi
– Suresh Kamrushi, Commented Jul 7, 2015 at 9:02
Hi @SureshKamrushi : I have added structure. Could you please help — Adithya Kumar
– Adithya Kumar, Commented Jul 7, 2015 at 9:09

Suresh Kamrushi · Accepted Answer · 2015-07-07 09:32:47Z

1

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

answered Jul 7, 2015 at 9:32

Suresh Kamrushi

16.2k13 gold badges79 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Adithya Kumar Over a year ago

This would create a single column. But I want Fruits, Electronics and Sports to be three separate columns in my final output. Could you please provide a suggestion for that

Adithya Kumar Over a year ago

Also the query should be group by CaseKey

Matt · Accepted Answer · 2015-07-07 10:35:08Z

1

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.

edited Jul 7, 2015 at 10:35

answered Jul 7, 2015 at 10:09

Matt

1,4072 gold badges14 silver badges26 bronze badges

Collectives™ on Stack Overflow

MySQL rows into columns

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related