MySQL query to expand a bitfield into individual named columns?

Question

I have the following problem i have two tables the first table contains a string like 01001101110 each digit represents a skill mentioned in table two.

So the first digit shows a zero that means that person does not have the skill with id 1. the second digit shows that person does have skill with id 2

table 1:

|-----------|-----------------------------|
|  name  |             skillset            |
|-----------|-----------------------------|
|   John  | 01001101110             |
|-----------|-----------------------------|

table 2:

|-----------|-----------------------------|
|    id       |              skill              |
|-----------|-----------------------------|
|       1    | polite                         |
|-----------|-----------------------------|
|      2     | easy going                 |
|-----------|-----------------------------|

now i need to create i query with outcome

|-----------|-------------------|------------------|
|  name  |      polite        |   easy going  |
|-----------|-------------------|------------------|
|  John   |          0            |            1        |
|-----------|-------------------|------------------|

Why on earth did you design the tables like this? Normalize your data and querying it becomes trivial. — David, Feb 16 '17 at 22:42
i didn't a scripter who formerly worked for the company did and we have to live with it now. but i understand redesigning would be an option but not the one i am looking for right now. — jan deeg, Feb 16 '17 at 23:08
the skill list is 158 items long and is being matched against two tables. the idea to look at it like this is quite ingenious but maybe not the best way to solve it. all credits to the former coder — jan deeg, Feb 16 '17 at 23:17
You can get a character of a string with `substring`. And putting rows into columns is called a pivot, have a look e.g. [here](http://stackoverflow.com/questions/7674786/mysql-pivot-table). If you are thinking now: "omg, do i have to write 158 case statements?". No, you don't have to do that. You don't even have to join your 2nd table. You just have to write 158 substring statements, because that tabledesign is not too bad for doing a pivot. For not much else, but at least for doing a pivot. (As it basically already is a pivot). — Solarflare, Feb 17 '17 at 00:17
I reworded your question's title. Stack Overflow prefers that the title describes or summarizes the question, instead of "Noob using MySQL" which only describes you. :-) — Bill Karwin, Feb 17 '17 at 06:11

Bill Karwin · Accepted Answer · 2017-02-17T15:36:09.093

I have seen many stories like this, "a former coder implemented this and now we're stuck with it." I imagine there's just one individual coder, who moves from company to company, implementing things in the most clever and least maintainable manner possible, and then quickly moving on to his next victim company.

I also find it ironic that someone would implement a bitfield in a varchar, because they end up using a full byte (8 bits) to store each 1 or 0. :facepalm:

Anyway, to solve your task you have to use a dynamic SQL query.

In fact, any pivot table query requires dynamic SQL, because you don't know the number of columns until you inspect the table that lists your skills, but you can't run a query without first knowing the number of columns. So you need to run at least two queries.

Here's the test data:

create table table1 (name varchar(20), skillset varchar(200));

insert into table1 values ('John', '01001101110110');

create table table2 (id int, skill varchar(20));

insert into table2 values
    (1, 'polite'),
    (2, 'easy going'),
    (3, 'trustworthy'),
    (4, 'loyal'),
    (5, 'helpful'),
    (6, 'friendly'),
    (7, 'courteous'),
    (8, 'kind'),
    (9, 'obedient'),
    (10, 'cheerful'),
    (11, 'thrifty'),
    (12, 'brave'),
    (13, 'clean'),
    (14, 'reverent');

Now is a clever query that produces the SQL for a dynamic query, by appending one field in the select-list for each entry in your skills table. The key is MySQL's GROUP_CONCAT() function.

select concat(
    'select name,',
    group_concat(concat(' mid(skillset,',id,',1) as `',skill,'`')),
    ' from table1;') as _sql
from table2;

The output of the above query is the following:

select name, 
 mid(skillset,1,1) as `polite`, 
 mid(skillset,2,1) as `easy going`, 
 mid(skillset,3,1) as `trustworthy`, 
 mid(skillset,4,1) as `loyal`, 
 mid(skillset,5,1) as `helpful`, 
 mid(skillset,6,1) as `friendly`, 
 mid(skillset,7,1) as `courteous`, 
 mid(skillset,8,1) as `kind`, 
 mid(skillset,9,1) as `obedient`, 
 mid(skillset,10,1) as `cheerful`, 
 mid(skillset,11,1) as `thrifty`, 
 mid(skillset,12,1) as `brave`, 
 mid(skillset,13,1) as `clean`, 
 mid(skillset,14,1) as `reverent` 
from table1;

I made sure to delimit the column aliases with back-ticks, just in case one of the skill names contains special characters or whitespace or conflicts with an SQL reserved word.

Then this can be run as a second query, which has the following result:

+------+--------+------------+-------------+-------+---------+----------+-----------+------+----------+----------+---------+-------+-------+----------+
| name | polite | easy going | trustworthy | loyal | helpful | friendly | courteous | kind | obedient | cheerful | thrifty | brave | clean | reverent |
+------+--------+------------+-------------+-------+---------+----------+-----------+------+----------+----------+---------+-------+-------+----------+
| John | 0      | 1          | 0           | 0     | 1       | 1        | 0         | 1    | 1        | 1        | 0       | 1     | 1     | 0        |
+------+--------+------------+-------------+-------+---------+----------+-----------+------+----------+----------+---------+-------+-------+----------+

score 0 · Answer 2 · answered Feb 18 '17 at 00:17

Make it a SET datatype; this gives you the best of both worlds -- storage of any combination of up to 64 pre-defined options in bits, yet read/write as strings.

The simple SELECT skillset FROM tbl WHERE name = 'John' would come back as the string

"easy going,helpful,friendly,kind"  (etc)

Reference

MySQL query to expand a bitfield into individual named columns?

2 Answers2