Using the output of subquery as the fields of main query

Question

Is there any way that I can use the output of subquery as the fields of the next query?

SELECT TEAM_ID, ID1, ID2, ID3 FROM XYZ;

to

SELECT TEAM_ID, (SELECT IDs FROM ABC WHERE GROUP_ID="1") FROM XYZ;

Schema for XYZ

TEAM_ID, ID1, ID2, ID3
   50, B001, A003, S001 
   60, B002, A111, M001

Schema for ABC

Group_ID, IDs
1, ID1,ID2,ID3
2, ID1,ID3
3, ID2

Now, I want: TEAM_ID, ID1, ID2, ID3 50, B001, A003, S001

Based on Group_ID=1

As I understand you mean to use the value of IDs column of ABC schema as column names inorder to construct a query for schema XYZ ? — Abhay Jatin Doshi, Sep 25 '19 at 03:21
possible duplicate of: https://stackoverflow.com/questions/12896147/can-i-pass-variable-to-select-statement-as-column-name-in-sql-server — Divyesh patel, Sep 25 '19 at 04:34
@AmirCS - did you really mean `GROUP_ID="1"` - using static `"1"` here sounds as typo - please confirm — Mikhail Berlyant, Sep 25 '19 at 19:49
@MikhailBerlyant just provided more info about the example. Does that make sense? — AmirCS, Sep 25 '19 at 20:19
@AmirCS - nope. not really :o( are you saying that by group_id you want to control the columns to be in result?? that would be quite a different ask from what you originally posted (which didn't make much sense) — Mikhail Berlyant, Sep 25 '19 at 20:21
Yes, "the output of subquery as the `fields` of main query". Any suggestions? Thanks! — AmirCS, Sep 26 '19 at 06:46

score 0 · Answer 1 · answered Sep 25 '19 at 12:13

0

You can use join:

SELECT X.TEAM_ID, A.*
FROM X CROSS JOIN
     (SELECT IDs FROM ABC WHERE ABC.GROUP_ID = '1') a

answered Sep 25 '19 at 12:13

Gordon Linoff

Thanks Gordon! This returns back the field names, but it would not be able to query these fields and the content of the columns in BigQuery. I added an example to the question. Do you think this is something even possible? Thanks so much and sorry for the confusion! – AmirCS Sep 26 '19 at 15:45

1 Answers1