CODEWITHSUNDEEP
mysqlsql
aniki24680
aniki24680

Reputation: 5

Two column (different name, different table) as one row

is it possible to change these two tables

Employee  A  B  C
Alex      G  G  B
Martin    B  G  G
...
------------------------------
Employee2  AA  BB  CC
Sam        G   B   B
Max        G   G   B
...

Into one table?

List       Employee        Mark
AAA         Alex            G
BBB         Alex            G
CCC         Alex            B
AAA         Sam             G
BBB         Sam             B
CCC         Sam             B
...

AA and A are different column in different table but I want to display them in one table with the same name (e.g. "AAA") and it must be in row not in column.

Upvotes: 0

Views: 91

Answers (1)

Nick
Nick

Reputation: 147166

You can use a UNION query to unpivot the data from columns into rows:

SELECT 'AAA' AS List, Employee, A AS Mark
FROM t1
UNION ALL
SELECT 'BBB' AS List, Employee, B AS Mark
FROM t1
UNION ALL
SELECT 'CCC' AS List, Employee, C AS Mark
FROM t1
UNION ALL
SELECT 'AAA' AS List, Employee2, AA AS Mark
FROM t2
UNION ALL
SELECT 'BBB' AS List, Employee2, BB AS Mark
FROM t2
UNION ALL
SELECT 'CCC' AS List, Employee2, CC AS Mark
FROM t2
ORDER BY Employee, List

Output (for your sample data)

List    Employee    Mark
AAA     Alex        G
BBB     Alex        G
CCC     Alex        B
AAA     Martin      B
BBB     Martin      G
CCC     Martin      G
AAA     Max         G
BBB     Max         G
CCC     Max         B
AAA     Sam         G
BBB     Sam         B
CCC     Sam         B

Demo on SQLFiddle

Upvotes: 2

Related Questions