I have two tables, table A contains the data in generic named columns. Which in other table B, I have a comma separated list of column names of table A.
Sample Data:
Table A:
+------+-----+-----+-----+-----+-----+------+-----+-----+-----+-----+-----+----+------+
| CODE | C1 | C2 | C3 | C4 | C5 | C6 | C7 | M1 | M2 | M3 | M4 | M5 | M6 |
+------+-----+-----+-----+-----+-----+------+-----+-----+-----+-----+-----+----+------+
| A1 | 102 | 275 | 400 | -55 | -1 | 2779 | 5 | 157 | 236 | 332 | -94 | 7 | 2209 |
| A2 | 162 | 209 | 330 | -71 | -4 | 1041 | 7 | 188 | 264 | 349 | -3 | -8 | 3863 |
| A3 | 124 | 237 | 334 | -71 | 3 | 2086 | 24 | 126 | 299 | 366 | -36 | -8 | 1416 |
| A4 | 154 | 267 | 360 | -15 | 7 | 3900 | 24 | 103 | 260 | 316 | -92 | 2 | 3247 |
| A5 | 157 | 240 | 361 | -86 | 8 | 3871 | -13 | 123 | 296 | 380 | -80 | -6 | 3743 |
| A6 | 138 | 268 | 388 | -27 | -10 | 2939 | -23 | 181 | 229 | 393 | -46 | -6 | 1355 |
| A7 | 117 | 267 | 353 | -92 | -6 | 3273 | 10 | 194 | 243 | 317 | -91 | 4 | 2508 |
| A8 | 138 | 207 | 343 | -67 | 3 | 1558 | -18 | 143 | 242 | 308 | -51 | 4 | 3955 |
| A9 | 175 | 225 | 359 | -11 | -1 | 2344 | 13 | 174 | 229 | 380 | -3 | 4 | 2611 |
| B1 | 137 | 295 | 364 | -48 | -2 | 3827 | 24 | 169 | 257 | 304 | -58 | 9 | 1534 |
| B2 | 155 | 231 | 339 | -39 | -1 | 2895 | -19 | 107 | 251 | 305 | -1 | 2 | 2208 |
| B3 | 166 | 251 | 363 | -42 | -2 | 1766 | 6 | 128 | 272 | 371 | -27 | 7 | 3460 |
| B4 | 116 | 200 | 317 | -72 | -6 | 1073 | -13 | 160 | 230 | 307 | -73 | -8 | 3393 |
+------+-----+-----+-----+-----+-----+------+-----+-----+-----+-----+-----+----+------+
Table B:
+------+-------------------+
| CODE | SELECT_COLS |
+------+-------------------+
| A1 | C1,C2,C3,M1,M2,M3 |
| A2 | C1,C2,C3,M1,M2,M3 |
| A3 | C1,C2,C3,M1,M2,M3 |
| A4 | C1,C2,C3,C4,M1 |
| A5 | C1,C2,C3,C4,C5,M1 |
| A6 | C1,C2,C3,M1,M2 |
| A7 | C1,C2,C3,C4,M1,M2 |
| A8 | C1,C2,C3,C4,M1,M2 |
| A9 | C1,C2,C3,C4,M1,M2 |
| B1 | C1,C2,C3,C4,M1 |
| B2 | C1,C2,C3,C4,M1 |
| B3 | C1,C2,M1 |
| B4 | C1,M1 |
+------+-------------------+
Is there any way I can select only Columns specified in SELECT_COLS column of table B from table A?
Neither of the two potential solutions here require dynamic SQL or the use of PL/SQL and they will get you the data you require.
Oracle does not support selecting from dynamic columns; yes, you could use PL/SQL to dynamically build an SQL statement but when that statement runs it will have fixed columns. Also, if you have one set of data that wants C1,C2,C3,M1,M2,M3 and another that wants C1,C2,C3,C4,C5,M1 then do you want 6 columns of data (plus the code) and, if so, how will you know what the columns contain? Or do you want 8 columns of data (6 from the first and the extra 2 from the second) and, if so, how do you know that you don't just want all the columns; so wouldn't it be easier just to get the data for every column and set the values to NULL that aren't meant to be shown and then you can hide columns which are all NULL in your application tier.
So, instead of trying to get dynamic columns, select them as rows and then if you need to pivot them back to columns do it in whatever application tier you are using to access the database.
SELECT a.*
FROM (
SELECT Code, Key, Value
FROM tableA
UNPIVOT (
VALUE FOR KEY IN (
C1, C2, C3, C4, C5, C6, C7, M1, M2, M3, M3, M4, M5, M6
)
)
) a
INNER JOIN (
TableB
) B
ON ( a.code = b.code AND INSTR( b.select_cols, a.key ) > 0 )
Which, for your sample data:
CREATE TABLE tableA ( CODE, C1, C2, C3, C4, C5, C6, C7, M1, M2, M3, M4, M5, M6 ) AS
SELECT 'A1', 102, 275, 400, -55, -1, 2779, 5, 157, 236, 332, -94, 7, 2209 FROM DUAL UNION ALL
SELECT 'A2', 162, 209, 330, -71, -4, 1041, 7, 188, 264, 349, -3, -8, 3863 FROM DUAL UNION ALL
SELECT 'A3', 124, 237, 334, -71, 3, 2086, 24, 126, 299, 366, -36, -8, 1416 FROM DUAL UNION ALL
SELECT 'A4', 154, 267, 360, -15, 7, 3900, 24, 103, 260, 316, -92, 2, 3247 FROM DUAL UNION ALL
SELECT 'A5', 157, 240, 361, -86, 8, 3871, -13, 123, 296, 380, -80, -6, 3743 FROM DUAL UNION ALL
SELECT 'A6', 138, 268, 388, -27, -10, 2939, -23, 181, 229, 393, -46, -6, 1355 FROM DUAL UNION ALL
SELECT 'A7', 117, 267, 353, -92, -6, 3273, 10, 194, 243, 317, -91, 4, 2508 FROM DUAL UNION ALL
SELECT 'A8', 138, 207, 343, -67, 3, 1558, -18, 143, 242, 308, -51, 4, 3955 FROM DUAL UNION ALL
SELECT 'A9', 175, 225, 359, -11, -1, 2344, 13, 174, 229, 380, -3, 4, 2611 FROM DUAL UNION ALL
SELECT 'B1', 137, 295, 364, -48, -2, 3827, 24, 169, 257, 304, -58, 9, 1534 FROM DUAL UNION ALL
SELECT 'B2', 155, 231, 339, -39, -1, 2895, -19, 107, 251, 305, -1, 2, 2208 FROM DUAL UNION ALL
SELECT 'B3', 166, 251, 363, -42, -2, 1766, 6, 128, 272, 371, -27, 7, 3460 FROM DUAL UNION ALL
SELECT 'B4', 116, 200, 317, -72, -6, 1073, -13, 160, 230, 307, -73, -8, 3393 FROM DUAL;
CREATE TABLE tableB ( CODE, SELECT_COLS ) AS
SELECT 'A1', 'C1,C2,C3,M1,M2,M3' FROM DUAL UNION ALL
SELECT 'A2', 'C1,C2,C3,M1,M2,M3' FROM DUAL UNION ALL
SELECT 'A3', 'C1,C2,C3,M1,M2,M3' FROM DUAL UNION ALL
SELECT 'A4', 'C1,C2,C3,C4,M1' FROM DUAL UNION ALL
SELECT 'A5', 'C1,C2,C3,C4,C5,M1' FROM DUAL UNION ALL
SELECT 'A6', 'C1,C2,C3,M1,M2' FROM DUAL UNION ALL
SELECT 'A7', 'C1,C2,C3,C4,M1,M2' FROM DUAL UNION ALL
SELECT 'A8', 'C1,C2,C3,C4,M1,M2' FROM DUAL UNION ALL
SELECT 'A9', 'C1,C2,C3,C4,M1,M2' FROM DUAL UNION ALL
SELECT 'B1', 'C1,C2,C3,C4,M1' FROM DUAL UNION ALL
SELECT 'B2', 'C1,C2,C3,C4,M1' FROM DUAL UNION ALL
SELECT 'B3', 'C1,C2,M1' FROM DUAL UNION ALL
SELECT 'B4', 'C1,M1' FROM DUAL;
Outputs:
CODE | KEY | VALUE :--- | :-- | ----: A1 | C1 | 102 A1 | C2 | 275 A1 | C3 | 400 A1 | M1 | 157 A1 | M2 | 236 A1 | M3 | 332 A1 | M3 | 332 A2 | C1 | 162 A2 | C2 | 209 A2 | C3 | 330 A2 | M1 | 188 A2 | M2 | 264 A2 | M3 | 349 A2 | M3 | 349 A3 | C1 | 124 A3 | C2 | 237 A3 | C3 | 334 A3 | M1 | 126 A3 | M2 | 299 A3 | M3 | 366 A3 | M3 | 366 A4 | C1 | 154 A4 | C2 | 267 A4 | C3 | 360 A4 | C4 | -15 A4 | M1 | 103 A5 | C1 | 157 A5 | C2 | 240 A5 | C3 | 361 A5 | C4 | -86 A5 | C5 | 8 A5 | M1 | 123 A6 | C1 | 138 A6 | C2 | 268 A6 | C3 | 388 A6 | M1 | 181 A6 | M2 | 229 A7 | C1 | 117 A7 | C2 | 267 A7 | C3 | 353 A7 | C4 | -92 A7 | M1 | 194 A7 | M2 | 243 A8 | C1 | 138 A8 | C2 | 207 A8 | C3 | 343 A8 | C4 | -67 A8 | M1 | 143 A8 | M2 | 242 A9 | C1 | 175 A9 | C2 | 225 A9 | C3 | 359 A9 | C4 | -11 A9 | M1 | 174 A9 | M2 | 229 B1 | C1 | 137 B1 | C2 | 295 B1 | C3 | 364 B1 | C4 | -48 B1 | M1 | 169 B2 | C1 | 155 B2 | C2 | 231 B2 | C3 | 339 B2 | C4 | -39 B2 | M1 | 107 B3 | C1 | 166 B3 | C2 | 251 B3 | M1 | 128 B4 | C1 | 116 B4 | M1 | 160
If you could have any of the rows and want them all as columns then just get them all and set the values to NULL if they aren't in the select_cols column:
SELECT a.code,
CASE WHEN INSTR( b.select_cols, 'C1' ) > 0 THEN C1 END AS C1,
CASE WHEN INSTR( b.select_cols, 'C2' ) > 0 THEN C2 END AS C2,
CASE WHEN INSTR( b.select_cols, 'C3' ) > 0 THEN C3 END AS C3,
CASE WHEN INSTR( b.select_cols, 'C4' ) > 0 THEN C4 END AS C4,
CASE WHEN INSTR( b.select_cols, 'C5' ) > 0 THEN C5 END AS C5,
CASE WHEN INSTR( b.select_cols, 'C6' ) > 0 THEN C6 END AS C6,
CASE WHEN INSTR( b.select_cols, 'C7' ) > 0 THEN C7 END AS C7,
CASE WHEN INSTR( b.select_cols, 'M1' ) > 0 THEN M1 END AS M1,
CASE WHEN INSTR( b.select_cols, 'M2' ) > 0 THEN M2 END AS M2,
CASE WHEN INSTR( b.select_cols, 'M3' ) > 0 THEN M3 END AS M3,
CASE WHEN INSTR( b.select_cols, 'M4' ) > 0 THEN M4 END AS M4,
CASE WHEN INSTR( b.select_cols, 'M5' ) > 0 THEN M5 END AS M5,
CASE WHEN INSTR( b.select_cols, 'M6' ) > 0 THEN M6 END AS M6
FROM tableA a
INNER JOIN tableB b
ON ( a.code = b.code )
Which outputs:
CODE | C1 | C2 | C3 | C4 | C5 | C6 | C7 | M1 | M2 | M3 | M4 | M5 | M6 :--- | --: | ---: | ---: | ---: | ---: | ---: | ---: | --: | ---: | ---: | ---: | ---: | ---: A1 | 102 | 275 | 400 | null | null | null | null | 157 | 236 | 332 | null | null | null A2 | 162 | 209 | 330 | null | null | null | null | 188 | 264 | 349 | null | null | null A3 | 124 | 237 | 334 | null | null | null | null | 126 | 299 | 366 | null | null | null A4 | 154 | 267 | 360 | -15 | null | null | null | 103 | null | null | null | null | null A5 | 157 | 240 | 361 | -86 | 8 | null | null | 123 | null | null | null | null | null A6 | 138 | 268 | 388 | null | null | null | null | 181 | 229 | null | null | null | null A7 | 117 | 267 | 353 | -92 | null | null | null | 194 | 243 | null | null | null | null A8 | 138 | 207 | 343 | -67 | null | null | null | 143 | 242 | null | null | null | null A9 | 175 | 225 | 359 | -11 | null | null | null | 174 | 229 | null | null | null | null B1 | 137 | 295 | 364 | -48 | null | null | null | 169 | null | null | null | null | null B2 | 155 | 231 | 339 | -39 | null | null | null | 107 | null | null | null | null | null B3 | 166 | 251 | null | null | null | null | null | 128 | null | null | null | null | null B4 | 116 | null | null | null | null | null | null | 160 | null | null | null | null | null
db<>fiddle here