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

3 回答

• 1

  您需要查看UNPIVOT关键字 .

  回复于 2024-05-16T03:50:50+08:00
• 0

  我希望这将有所帮助 .

  这是我作为架构：

  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;
  

  上面的查询将对所有后续分组进行分组，以形成逗号分隔列表 .

  回复于 2024-05-16T03:50:50+08:00
• 0

  试试这个

  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
  

  回复于 2024-05-16T03:50:50+08:00