2

Background

I want to rename my case statement in sql select statement dynamically.

Eg:

SELECT (case when id= x.id then x.sums end) x.id as (select id,count(*) sums from table group by id) x

what i want the output is list of columns created ,with Labels as distinct id's from "id" column.

However,this variable x.id is not dynamically outputing values,rather i get output a single column x.id.

Eg:

Columns in table...

id---c1----c2

1----x1---x2

2----x2----x3

3----x4----x5

columns expected after running query...

1-----2----3

but actual o/p column is::

x.id

Query Any ideas,how to generate columns dynamically using select query,please correct me ,if i am wrong.

Rohan
  • 601
  • 2
  • 9
  • 21
  • 1
    having example of input table and desired columns of output - can you clarify what values in rows you expect to see? it is not clear! – Mikhail Berlyant Sep 08 '16 at 15:01
  • Look up `early binding` vs `late binding`. The very concept behind this won't work; a field's value can not be used as a field name *(or table name, etc, etc)*. If you want dynamical number of columns or dynamic field names then you need one query to read the names you want, then code to write the sql to use those. – MatBailie Sep 08 '16 at 15:22

1 Answers1

2

Below is for BigQuery!

Please note: your expectations about output column names are not correct!
Column name cannot start with digit - so in below example - i will be using id_1, id_2 and id_3 instead of 1, 2 and 3 

SELECT
  SUM(CASE WHEN id = 1 THEN 1 END) AS id_1,
  SUM(CASE WHEN id = 2 THEN 1 END) AS id_2,
  SUM(CASE WHEN id = 3 THEN 1 END) AS id_3
FROM YourTable

Above example assumes you know in advance your IDs and there are very few of them so it is not a big deal to write manually few numbers of lines with SUM(...) for each id

If this is not a case - you can first generate above query programmatically by running below query 

SELECT 'SELECT ' + 
   GROUP_CONCAT_UNQUOTED(
      'SUM(CASE WHEN id = ' + STRING(id) + ' THEN 1 END) AS id_' + STRING(id)
   ) 
   + ' FROM YourTable'
FROM (
  SELECT id FROM (
    SELECT * FROM YourTable GROUP BY id ORDER BY id
)

as a result - you will get string like below 

SELECT SUM(CASE WHEN id = 1 THEN 1 END) AS id_1,SUM(CASE WHEN id = 2 THEN 1 END) AS id_2,SUM(CASE WHEN id = 3 THEN 1 END) AS id_3 FROM YourTable

So, now just copy it and paste into Query Editor and run it

you can see similar example here - https://stackoverflow.com/a/36623258/5221944

Community
  • 1
  • 1
Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
  • Hi Mikhail, is there an equivalent function for standard SQL? – Joe Chan Jul 15 '19 at 05:10
  • @JoeChan - please post your question and I will be happy to answer it. note: it is hard to figure out what exactly your are asking when you do this in comments and even if I would got it - no way to answer within the comment. so post your question please as a new question – Mikhail Berlyant Jul 15 '19 at 05:12