英文:
Convert row into columns with duplicates
问题
我需要将SQL Server中的行转换为列，同时包括值和日期。
这是我的示例数据集：
我希望显示如下：
我尝试了case when条件，并获得了以下结果。但我不想创建更多的重复项。
代码：
SELECT
id,
CASE WHEN name = 'BP' THEN value ELSE NULL END AS BP_value,
CASE WHEN name = 'BP' THEN date ELSE NULL END AS BP_date,
CASE WHEN name = 'Pulse' THEN value ELSE NULL END AS Pulse_value,
CASE WHEN name = 'Pulse' THEN date ELSE NULL END AS Pulse_date
FROM
table_name
结果：
请帮助我解决这个问题。
英文:
I need to convert SQL Server rows into columns along with it value and dates.
Here is my sample dataset:
I required it to be shown like this:
I tried case when conditions and I got this results. But I don't want to make more duplicates.
Code:
SELECT
id,
CASE WHEN name = 'BP' THEN value ELSE NULL AS END AS BP_value,
CASE WHEN name = 'BP' THEN date ELSE NULL AS END AS BP_date,
CASE WHEN name = 'Pulse' THEN value ELSE NULL AS END AS Pulse_value,
CASE WHEN name = 'Pulse' THEN date ELSE NULL AS END AS Pulse_date
FROM
table_name
Result:
Please help me with this.
答案1
得分: 2
使用聚合功能：
SELECT
id,
date,
MAX(CASE WHEN name = 'BP' THEN value ELSE NULL END) AS BP_value,
MAX(CASE WHEN name = 'BP' THEN date ELSE NULL END) AS BP_date,
MAX(CASE WHEN name = 'Pulse' THEN value ELSE NULL END) AS Pulse_value,
MAX(CASE WHEN name = 'Pulse' THEN date ELSE NULL END) AS Pulse_date
FROM
table_name
GROUP BY id, date
英文:
Use aggregation:
SELECT
id,
date,
MAX(case when name = 'BP' THEN value ELSE NULL as end) as BP_value,
MAX(case when name = 'BP' THEN date ELSE NULL as end) as BP_date,
MAX(case when name = 'Pulse' THEN value ELSE NULL as end) as Pulse_value,
MAX(case when name = 'Pulse' THEN date ELSE NULL as end) as Pulse_date
FROM
table_name
GROUP BY id, date
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论