-2

I'am tying to get the specific columns whose name starts with some patterns.

table_1

abcA| abcB | abcC | xyD | mnE

1 | 2 | 3 | 4 | 5

6 | 7 | 8 | 9 | 10

11 | 12 | 13 | 14 | 15

From the above table i'am in need of the Output Like

abcA | abcB | abcC

1 | 2 | 3

6 | 7 | 8

11 | 12 | 13

The columns should be selected DYNAMICALLY by filtering like any column name starts with abc should me selected.

I Tried this Query 

"select column_name from information_schema.columns 
where table_name='table_1' and column_name like 'abc%';"

It gives a another table only with column names

column_name

abcA

abcB

abcC

But I want to get the values of that Column names.

Thanks

Shadow
  • 33,525
  • 10
  • 51
  • 64
ARAV
  • 9
  • 1
  • @Shadow I am reopening because the link you gave talks about how to do what the OP is trying to do, which in fact is bad practice, and ought not to be done. – Tim Biegeleisen Aug 29 '19 at 06:29
  • @TimBiegeleisen which is pointed out both in comments and the accepted answer of the duplicate question. But you had to get your answer in at all costs... – Shadow Aug 29 '19 at 06:32

1 Answers1

2

This is poor table design, and it is fairly difficult to write code which can select a dynamic column name. Here is the design I would suggest to you:

ID  | name | pos
1   | abcA | 1
2   | abcB | 1
3   | abcC | 1
4   | xyD  | 1
5   | mnE  | 1
6   | abcA | 2
7   | abcB | 2
8   | abcC | 2
9   | xyD  | 2
10  | mnE  | 2
11  | abcA | 3
12  | abcB | 3
13  | abcC | 3
14  | xyD  | 3
15  | mnE  | 3

With this design in place, you only need a very simple query:

SELECT pos, GROUP_CONCAT(ID) AS ids
FROM yourTable
WHERE name LIKE 'abc%'
GROUP BY pos;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360