英文:
SQL query to help transpose data
问题
我正在尝试编写一个SQL查询，将数据转置到一个新列中，我已经进行了一些与pivot相关的研究，但我不确定我是否正确理解如何使用它，或者也许我不应该使用它，查询可以用不同的格式编写。感谢任何帮助。谢谢。
英文:
I'm trying to write a SQL query to transpose the data into a new column, I've done some research with pivot, but I'm not sure I understand how to use it properly, or maybe I shouldn't be using that and the query can be written in a different format. Any help is appreciated. Thanks
答案1
得分: 1
这可以使用条件聚合来完成：
select serial,
MAX(case when externalID <> 47 then MyValues end) as MyValues,
MAX(case when externalID = 47 then MyValues end) as NuwColumn
from mytable
group by serial
这段代码适用于主要的数据库，如mysql和postgresql...
英文:
This can be done using the conditional aggregation :
select serial,
MAX(case when externalID <> 47 then MyValues end) as MyValues,
MAX(case when externalID = 47 then MyValues end) as NuwColumn
from mytable
group by serial
This code works on major databases such as mysql and postgresql...
答案2
得分: 0
作为@SelVazi版本的替代（因为我不喜欢聚合函数）：
SELECT t1.[Serial], t1.[MyValues], t2.[MyValues] AS [NewColumn]
FROM [Table1] t1
INNER JOIN [Table1] t2
ON t1.Serial = t2.Serial
WHERE t1.ExternalID = 43 AND t2.ExternalID = 47
（注意：这是MS SQL Server版本）
英文:
As an alternative to @SelVazi version (because I hate aggregating functions):
SELECT t1.[Serial], t1.[MyValues], t2.[MyValues] AS [NewColumn]
FROM [Table1] t1
INNER JOIN [Table1] t2
ON t1.Serial = t2.Serial
WHERE t1.ExternalID = 43 AND t2.ExternalID = 47
(NOTE: this is an MS SQL Server version)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论