问题描述
背景
我想动态地重命名我在 sql select 语句中的 case 语句.
I want to rename my case statement in sql select statement dynamically.
例如:
SELECT (case when id= x.id then x.sums end) x.idas (select id,count(*) sums from tablegroup by id) x
我想要的输出是创建的列列表，标签与id"列不同.
what i want the output is list of columns created ,with Labels as distinct id's from "id" column.
但是，这个变量 x.id 不是动态输出值，而是我输出单列 x.id.
However,this variable x.id is not dynamically outputing values,rather i get output a single column x.id.
例如:
表中的列...
1----x1---x2
1----x1---x2
2----x2----x3
2----x2----x3
3----x4----x5
3----x4----x5
运行查询后预期的列...
columns expected after running query...
但实际的 o/p 列是::
but actual o/p column is::
查询任何想法，如何使用选择查询动态生成列，如果我错了，请纠正我.
Query Any ideas,how to generate columns dynamically using select query,please correct me ,if i am wrong.
推荐答案
以下是 BigQuery！
Below is for BigQuery!
请注意:您对输出列名称的期望不正确！
列名不能以数字开头 - 所以在下面的例子中 - 我将使用 id_1、id_2 和 id_3 而不是 1、2 和 3
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
上面的例子假设你事先知道你的 ID 并且它们很少，所以为每个 ID 手动编写几行 SUM(...) 并不是什么大问题
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
你可以在这里看到类似的例子 - https://stackoverflow.com/a/36623258/5221944
you can see similar example here - https://stackoverflow.com/a/36623258/5221944
这篇关于使用 Bigquery 中的 Select 从行数据动态创建列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！