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 |
|-----------|-------------------|------------------|
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 |
+------+--------+------------+-------------+-------+---------+----------+-----------+------+----------+----------+---------+-------+-------+----------+