如何在SQL Server中将同一ID的行显示为列？

Question

我希望能够将特定id的行转换为列，并制作一个平面表格。

请参见以下截图： 在这里，前两列是同一患者。但是他们的随访情况不同。

现在我想将它们分别显示在不同的列中，而不是行中。

- Sarker

---

点击表格数据 - Sarker

其他一列是什么？ - iminiki

例如PatientID，Followup1，Followup2，... FollowupN等等？ - Harsh

其他列始终为空。 - Sarker

你是对的 @Harsh - Sarker

Answer 1

我希望这能帮到你。

这是我将其视为模式：

declare @mytable as table(PateintID int, Followup Varchar(50));
Declare @FollowUps      VARCHAR(1000);

insert into @mytable values
(1, 'Follow up 1'),
(1, 'Follow up 2'),
(1, 'Follow up 3'),
(2, 'Follow up 1'),
(2, 'Follow up 2'),
(2, 'Follow up 2'),
(2, 'Follow up 3'),
(3, 'Follow up 1'),
(3, 'Follow up 2'),
(3, 'Follow up 3'),
(4, 'Follow up 1'),
(4, 'Follow up 2'),
(4, 'Follow up 2'),
(5, 'Follow up 1'),
(5, 'Follow up 2'),
(5, 'Follow up 3');

以下是用于生成输出的查询语句：

SELECT PateintID,  Followup = STUFF(
             (SELECT ',' + Followup 
              FROM @mytable t1
              WHERE t1.PateintID = t2.PateintID
              FOR XML PATH (''))
             , 1, 1, '') from @mytable t2
group by PateintID;

上面的查询会将所有后续跟踪分组，形成逗号分隔的列表。

- Harsh

Answer 2

您需要查看 UNPIVOT关键字。

- jimconstable

Answer 3

试试这个

create table #temp
(
ID int,
LabId varchar(20),
PatientId varchar(20),
TestName varchar(20),
SampleType varchar(20),
CollectionDate Date,
Followup varchar(20),
AFBResult Varchar(20),
GXMTB Varchar(20),
GXRIF Varchar(20),
CultureResult Varchar(20),
PCRResult Varchar(20),
QFTAgTB1 Varchar(20),
QFTAgTB2 Varchar(20),
QFTResult Varchar(20),
LPAMTC Varchar(20)
)

Insert into #temp values (2, 'AM-5000/18', '205181000001-9', 'AFB Microscopy', 'Urine', '2018-10-01', '1st Follow up', 
'Positive', Null, null, null, null, null, null, null, null)
Insert into #temp values (3, 'AM-5000/18', '205181000001-9', 'AFB Microscopy', 'Urine', '2018-10-01', '3rd Follow up', 
'Negative', Null, null, null, null, null, null, null, null)
Insert into #temp values (4, 'AM-5000/18', '201181000026-0', 'AFB Microscopy', 'Sputum', '2018-10-02', '', 
'Negative', Null, null, null, null, null, null, null, null)
Insert into #temp values (5, 'AM-5125/18', '201181000014-6', 'AFB Microscopy', 'Sputum', '2018-10-02', '', 
'Negative', Null, null, null, null, null, null, null, null)
Insert into #temp values (6, 'AM-5126/18', '201181000022-9', 'AFB Microscopy', 'Sputum', '2018-10-02', '', 
'Negative', Null, null, null, null, null, null, null, null)
Insert into #temp values (7, 'AM-5127/18', '201181000022-9', 'AFB Microscopy', 'Sputum', '2018-10-02', '1st Follow up', 
'Negative', Null, null, null, null, null, null, null, null)

select PatientId, TestName, SampleType, [1st Follow up], [3rd Follow up]
from
(
  select  PatientId, TestName, SampleType, CollectionDate, Followup
  from #temp where PatientId = '205181000001-9' 
) d
pivot
(
  max(CollectionDate)
  for Followup in ([1st Follow up], [3rd Follow up])
) piv;

--select * from #temp
drop table #temp

输出如下所示。

PatientId       TestName        SampleType  1st Follow up   3rd Follow up
205181000001-9  AFB Microscopy  Urine       2018-10-01      2018-10-01

- Suraj Kumar