0

I have two requirements

1) Use table columns names (INFORMATION_SCHEMA.COLUMNS) NOT DATA in where clause query.

2) Transform the final output columns

Table-A:

id column-A column-B column-C ... column-N

1   11      12        13          20
2   21      22        23          20

Table-B:

id    name        label
1     column A    CA
2     column B    CB
3     column C    CC

RAW MySQL query

select a.* from table A a inner join table B b
where b.name IN (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='Table-A' AND
COLUMN_NAME NOT IN ('id');

ALSO, I want to transform final output of Table-A and that should be:

id  CA  CB  CC
1   11  12  13
2   21  22  23

I don't want column-A column-B column-C in my final query result. Can someone please help me in this?

Hassan Kashif
  • 435
  • 1
  • 7
  • 26
  • You're missing the `ON` clause in your `INNER JOIN` to specify the relationship between the tables. – Barmar May 25 '17 at 08:40
  • You need to use dynamic SQL with `PREPARE`. – Barmar May 25 '17 at 08:41
  • Can you give me hint using `PREPARE` statement? – Hassan Kashif May 26 '17 at 06:24
  • You can find some examples of using `PREPARE` here: http://stackoverflow.com/questions/7674786/mysql-pivot-table. They use it to pivot a table using data in the table, but it should give you good ideas of how to do similar things using data from `INFORMATION_SCHEMA.COLUMNS`. – Barmar May 26 '17 at 15:39

0 Answers0