英文:
How to pivot certain columns and rename column at the same time
问题
-- 创建临时表 #df 存放患者数据
DROP TABLE IF EXISTS #df
CREATE TABLE #df
(
PTID VARCHAR(10),
clmid VARCHAR(5),
clmfrom date,
Code VARCHAR(5),
CriteriaID VARCHAR(5)
);
-- 向 #df 表插入数据
INSERT INTO #df (PTID, clmid, clmfrom, Code, CriteriaID)
VALUES
('X0001', '001', '2021-07-25', 'A1109', '41'),
('X0001', '001', '2021-07-25', 'B1192', '42'),
('X0002', '002', '2021-08-25', 'A1123', '41'),
('X0002', '002', '2021-08-25', 'B2231', '42'),
('X0003', '003', '2021-09-25', 'B2312', '42');
-- 创建临时表 #finaldf 存放最终结果
CREATE TABLE #finaldf
(
PTID VARCHAR(10),
clmid VARCHAR(5),
clmfrom date,
SysCode VARCHAR(5),
DiaCode VARCHAR(5)
);
-- 向 #finaldf 表插入数据，并根据 CriteriaID 进行列名的动态生成
INSERT INTO #finaldf (PTID, clmid, clmfrom, SysCode, DiaCode)
SELECT PTID, clmid, clmfrom,
CASE WHEN CriteriaID = '41' THEN Code ELSE NULL END AS SysCode,
CASE WHEN CriteriaID = '42' THEN Code ELSE NULL END AS DiaCode
FROM #df;
-- 查询最终结果
SELECT * FROM #finaldf;
-- 删除临时表
DROP TABLE #df;
DROP TABLE #finaldf;
英文:
I have the following table that has data on patients. Each patient can show up more than once if they have more than 1 code associated with their ID. That code is also associated with a code group which is conveyed using the CriteriaID column.
DROP TABLE IF EXISTS #df
CREATE TABLE #df
(
PTID VARCHAR(10),
clmid VARCHAR(5),
clmfrom date,
Code VARCHAR(5),
CriteriaID VARCHAR(5)
);
INSERT INTO #df (PTID, clmid, clmfrom, Code, CriteriaID)
VALUES
('X0001', '001', '2021-07-25', 'A1109', '41'),
('X0001', '001', '2021-07-25', 'B1192', '42'),
('X0002', '002', '2021-08-25', 'A1123', '41'),
('X0002', '002', '2021-08-25', 'B2231', '42'),
('X0003', '003', '2021-09-25', 'B2312', '42')
SELECT * FROM #df
I would like my final table to pivot Code and rename the columns based on the CriteriaID.
Essentially, I would like my final table to look like this:
CREATE TABLE #finaldf
(
PTID VARCHAR(10),
clmid VARCHAR(5),
clmfrom date,
SysCode VARCHAR(5),
DiaCode VARCHAR(5),
);
INSERT INTO #finaldf (PTID, clmid, clmfrom, SysCode, DiaCode)
VALUES
('X0001', '001', '2021-07-25', 'A1109', 'B1192'),
('X0002', '002', '2021-08-25', 'A1123', 'B2231'),
('X0003', '003', '2021-09-25', NULL, 'B2312')
SELECT * FROM #finaldf
If CriteriaID associated with the code is 41 in #df then I would like the column to be SysCode. If the criteriaID associated with the code is 42 then I would like the column to be DiaCode. If a PTID does not have a certain CriteriaID that corresponding SysCode or DiaCode column should be NULL.
Any help would be appreciated. I believe I should be using a pivot or max function but am not sure how to go about it.
答案1
得分: 6
这被称为条件聚合：
select ptid, clmid, clmfrom,
Max(case when CriteriaID = 41 then code end) SysCode,
Max(case when CriteriaID = 42 then code end) DiaCode
from #df
group by ptid, clmid, clmfrom;
英文:
This is known as conditional aggregation:
select ptid, clmid, clmfrom,
Max(case when CriteriaID = 41 then code end) SysCode,
Max(case when CriteriaID = 42 then code end) DiaCode
from #df
group by ptid, clmid, clmfrom;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论