Is it possible for SQL to make a SELECT from a "list"? Example inside

Question

My tables as follows

name
| id | name |
|  1 | jon  |
|  2 | mary |

skill
| id | skill | level |
|  1 | C++   | 3     |
|  1 | Java  | 2     |
|  1 | HTML  | 5     |
|  1 | CSS   | 4     |
|  1 | JS    | 5     |
|  2 | PHP   | 4     |
|  2 | Ruby  | 3     |
|  2 | Perl  | 1     |

So I want the output to be like this:

| name | skill_1 | lv_1 | skill_2 | lv_2 | skill_3 | lv_3 | skill_4 | lv_4 | skill_5 | lv_5 |
| jon  | C++     | 3    | Java    | 2    | HTML    | 5    | CSS     | 4    | JS      | 5    |
| mary | PHP     | 4    | Ruby    | 3    | Perl    | 1    |         |      |         |      |

What type of join or union statements would I be using? Each person has only 5 skills max.

So how would the SQL for this look like? Is it even possible?

I'm completely lost and have no idea where to start.

You are right it doesn't. But the technique itself is called pivot and there are ways to do it in MySQL as well. SO even has a tag for that. Start here: http://stackoverflow.com/search?q=mysql+pivot — , Jun 02 '13 at 07:42

John Woo · Accepted Answer · 2013-06-02T08:06:34.793

Since you have mentioned that a Name can have a maximum of 5 Skills, this problem can be done using static query.

-- <<== PART 2
SELECT  Name,
        MAX(CASE WHEN RowNumber = 1 THEN Skill END) Skill_1,
        MAX(CASE WHEN RowNumber = 2 THEN Skill END) Skill_2,
        MAX(CASE WHEN RowNumber = 3 THEN Skill END) Skill_3,
        MAX(CASE WHEN RowNumber = 4 THEN Skill END) Skill_4,
        MAX(CASE WHEN RowNumber = 5 THEN Skill END) Skill_5
FROM
        ( -- <<== PART 1
            SELECT  a.Name, 
                    b.Skill,
                    (
                        SELECT  COUNT(*)
                        FROM    Skill c
                        WHERE   c.id = b.id AND
                                c.Skill <= b.Skill) AS RowNumber
            FROM    Name a
                    INNER JOIN Skill b
                        ON a.id = b.id
        ) x
GROUP   BY Name

SQLFiddle Demo

OUTPUT

╔══════╦═════════╦═════════╦═════════╦═════════╦═════════╗
║ NAME ║ SKILL_1 ║ SKILL_2 ║ SKILL_3 ║ SKILL_4 ║ SKILL_5 ║
╠══════╬═════════╬═════════╬═════════╬═════════╬═════════╣
║ jon  ║ C++     ║ CSS     ║ HTML    ║ Java    ║ JS      ║
║ mary ║ Perl    ║ PHP     ║ Ruby    ║ (null)  ║ (null)  ║
╚══════╩═════════╩═════════╩═════════╩═════════╩═════════╝

BRIEF EXPLANATION

Let's breakdown it down. There are two parts in the query.

The first part, which is PART 1, of the query generates the sequence of number on Skill for every Name. It just uses correlated subquery to mimic a window function ROW_NUMBER which MySQL does not support.

Generating Sequential Number on Skill (SQLFiddle Demo)

The second part, PART 2, transpose the rows into columns based on the sequential number generated on PART 1. It uses CASE to test the value of the number and returns the Skill associated on the number. If the number does not match it returns a NULL value. Next, it aggregates the column for every group of Name using MAX() so SKILL will be returned instead of NULL if there is any.

UPDATE 1

SELECT  Name,
        MAX(CASE WHEN RowNumber = 1 THEN Skill END) Skill_1,
        MAX(CASE WHEN RowNumber = 1 THEN Level END) Level_1,
        MAX(CASE WHEN RowNumber = 2 THEN Skill END) Skill_2,
        MAX(CASE WHEN RowNumber = 2 THEN Level END) Level_2,
        MAX(CASE WHEN RowNumber = 3 THEN Skill END) Skill_3,
        MAX(CASE WHEN RowNumber = 3 THEN Level END) Level_3,
        MAX(CASE WHEN RowNumber = 4 THEN Skill END) Skill_4,
        MAX(CASE WHEN RowNumber = 4 THEN Level END) Level_4,
        MAX(CASE WHEN RowNumber = 5 THEN Skill END) Skill_5,
        MAX(CASE WHEN RowNumber = 5 THEN Level END) Level_5
FROM
        (
            SELECT  a.Name, 
                    b.Skill,
                    (
                        SELECT  COUNT(*)
                        FROM    Skill c
                        WHERE   c.id = b.id AND
                                c.skill <= b.skill) AS RowNumber,
                    b.Level
            FROM    Name a
                    INNER JOIN Skill b
                        ON a.id = b.id
        ) x
GROUP   BY Name

SQLFiddle Demo

OUTPUT

╔══════╦═════════╦═════════╦═════════╦═════════╦═════════╦═════════╦═════════╦═════════╦═════════╦═════════╗
║ NAME ║ SKILL_1 ║ LEVEL_1 ║ SKILL_2 ║ LEVEL_2 ║ SKILL_3 ║ LEVEL_3 ║ SKILL_4 ║ LEVEL_4 ║ SKILL_5 ║ LEVEL_5 ║
╠══════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╬═════════╣
║ jon  ║ C++     ║       3 ║ CSS     ║       4 ║ HTML    ║       5 ║ Java    ║ 2       ║ JS      ║ 5       ║
║ mary ║ Perl    ║       1 ║ PHP     ║       4 ║ Ruby    ║       3 ║ (null)  ║ (null)  ║ (null)  ║ (null)  ║
╚══════╩═════════╩═════════╩═════════╩═════════╩═════════╩═════════╩═════════╩═════════╩═════════╩═════════╝

Thanks. I have 5 minutes to accept the answer so I want to make sure I understand: say my `skill` table had another column `level`, how would I modify so my output is now `| name | skill_1 | lv_1 | skill_2 | lv_2 | skill_3 | lv_3 | skill_4 | lv_4 | skill_5 | lv_5 |`? — meiryo, Jun 02 '13 at 07:51
@meiryo it's hard to understand the output in the comment, can you post it along with your question? — John Woo, Jun 02 '13 at 07:55
ok sure, done. It's basically adding a new column to the selection. — meiryo, Jun 02 '13 at 08:00

webGautam · Answer 2 · 2013-06-02T10:18:28.813

This could also be the possible solution!

Query

select a.name, 
(select skill  from skill where skill.id=a.id limit 0,1  ) as skill_1,
(select skill  from skill where skill.id=a.id limit 1,1) skill_2,
(select skill from skill where skill.id=a.id limit 2,1 ) skill_3,
(select skill from skill where skill.id=a.id limit 3,1) skill_4,
(select skill from skill  where skill.id=a.id limit 4,1) skill_5
 from name a

Output

enter image description here

classicjonesynz · Answer 3 · 2013-06-04T21:49:29.787

1

I understand your after what is called a pivot table, there is a nice tutorial here. In order to achive what you are after, you would need to change the schema of the skill table slightly to...

| id | skill_id | skill |
|  1 | 1        | C++   |
|  1 | 2        | Java  |
|  1 | 3        | HTML  |
|  1 | 4        | CSS   |
|  1 | 5        | PHP   |
|  2 | 1        | RUBY  |
|  2 | 2        | PERL  |
|  2 | 3        | JS    |

try this:

SELECT n.name,
, if(s.skill_id=1, s.skill, '') as 'skill_1'
, if(s.skill_id=2, s.skill, '') as 'skill_2'
, if(s.skill_id=3, s.skill, '') as 'skill_3'
, if(s.skill_id=4, s.skill, '') as 'skill_4'
, if(s.skill_id=5, s.skill, '') as 'skill_5'
FROM name n
INNER JOIN skill s ON n.id = s.id
group by s.skill
LIMIT 0, 10

edited Jun 04 '13 at 21:49

answered Jun 02 '13 at 07:41

classicjonesynz

4,012
5
38
78

That is not what the OP is looking for – Jun 02 '13 at 07:43
I'm quite sure this won't get the end resut the OP wants, but its a step in the right direction. – Burhan Khalid Jun 02 '13 at 07:43
What does the picture have to do with the question here? Totally irrelevant. – ypercubeᵀᴹ Jun 04 '13 at 10:15

score 1 · Answer 4 · answered Jun 02 '13 at 07:42

1

I think in no join can answer this type of output so it is better to handle from code but you can use group_concat() with group by

answered Jun 02 '13 at 07:42

Muhannad A.Alhariri

3,702
4
30
46

Is it possible for SQL to make a SELECT from a "list"? Example inside

4 Answers4