首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >SQL一行上的一对多

问SQL一行上的一对多
EN

Stack Overflow用户
提问于 2013-11-26 19:36:26
回答 3查看 187关注 0票数 1

我有一个表格,记录了病人每次就诊的诊断结果。患者可以有多个诊断。

我希望每次出诊都有一行诊断(最多12个诊断)。下面返回了多个行。

代码语言:javascript
复制
select
    dg.AttendanceID
    , dg.PatientNumber
    , dg.DiagnosisDate
    , dg.Diagnosis
from
    Diagnosis dg


AttendanceID    PatientNumber   DiagnosisDate   Diagnosis
10001           123456          01-Oct-13   A
10001           123456          01-Oct-13   B
10002           123456          20-Oct-13   D

它的结果应该如下所示:

代码语言:javascript
复制
AttendanceID    PatientNumber   DiagnosisDate   Diagnosis 1 Diagnosis 2 Diagnosis 3
10001           123456          01-Oct-13   A            B       
10002           123456          20-Oct-13   D   

有人能帮帮忙吗?

EN

回答 3

Stack Overflow用户

发布于 2013-11-26 20:07:09

您可以通过实现PIVOT函数获得结果,但我也建议您使用窗口函数row_number()为每个attendanceid和patientnumber生成诊断次数

代码语言:javascript
复制
select attendanceid, patientnumber,
  diagnosisdate,
  Diagnosis1, Diagnosis2, Diagnosis3
from
(
  select attendanceid, patientnumber,
    diagnosisdate, diagnosis,
    'diagnosis'+
      cast(row_number() over(partition by attendanceid, patientnumber
                              order by diagnosis) as varchar(2)) seq
  from diagnosis
) d
pivot
(
  max(diagnosis)
  for seq in (Diagnosis1, Diagnosis2, Diagnosis3)
) piv;

参见SQL Fiddle with Demo。由于您知道每个患者/出诊次数最多会有12个诊断,因此可以很容易地对查询进行硬编码,以添加其他列。

但是,如果您需要代码的动态版本,则可以使用类似于以下内容的代码:

代码语言:javascript
复制
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(seq) 
                    from
                    (
                       select 'diagnosis'+
                          cast(row_number() over(partition by attendanceid, patientnumber
                               order by diagnosis) as varchar(2)) seq
                       from diagnosis
                    ) d
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT attendanceid, patientnumber,
               diagnosisdate,' + @cols + ' 
            from 
            (
              select attendanceid, patientnumber,
                diagnosisdate, diagnosis,
                ''diagnosis''+
                  cast(row_number() over(partition by attendanceid, patientnumber
                                          order by diagnosis) as varchar(2)) seq
              from diagnosis
            ) x
            pivot 
            (
                max(diagnosis)
                for seq in (' + @cols + ')
            ) p '

execute sp_executesql @query;

请参阅SQL Fiddle with Demo

两个版本都给出了一个结果:

代码语言:javascript
复制
| ATTENDANCEID | PATIENTNUMBER |                  DIAGNOSISDATE | DIAGNOSIS1 | DIAGNOSIS2 | DIAGNOSIS3 |
|--------------|---------------|--------------------------------|------------|------------|------------|
|        10001 |        123456 | October, 01 2013 00:00:00+0000 |          A |          B |     (null) |
|        10002 |        123456 | October, 20 2013 00:00:00+0000 |          D |     (null) |     (null) |
票数 2
EN

Stack Overflow用户

发布于 2013-11-26 19:52:04

我不是100%确定,但应该是这样的。

代码语言:javascript
复制
;with cte as(
select
dg.AttendanceID
, dg.PatientNumber
, dg.DiagnosisDate
, dg.Diagnosis
,ROW_NUMBER() over (partition by dg.attendanceID order by attendanceID) as seq1
,ROW_NUMBER() over (partition by dg.attendanceID, patient_no order by diagnosis) as seq2
from
Diagnosis dg
)

 select
 t1.attendanceID
 ,t1.patientNumber
 ,t1.diagnosisDate
 ,(select Diagnosis from cte t2 where t1.attendanceID=t2.attendanceID and             t1.patientNumber=t2.patientNumber and t2.seq2='1') as diag1
 ,(select Diagnosis from cte t2 where t1.attendanceID=t2.attendanceID and    t1.patientNumber=t2.patientNumber and t2.seq2='2') as diag2
 ,(select Diagnosis from cte t2 where t1.attendanceID=t2.attendanceID and t1.patientNumber=t2.patientNumber and t2.seq2='3') as diag3
 ,(select Diagnosis from cte t2 where t1.attendanceID=t2.attendanceID and t1.patientNumber=t2.patientNumber and t2.seq2='4') as diag4
 ,(select Diagnosis from cte t2 where t1.attendanceID=t2.attendanceID and t1.patientNumber=t2.patientNumber and t2.seq2='5') as diag5
  from cte t1
  where seq1= 1
票数 0
EN

Stack Overflow用户

发布于 2013-11-26 20:06:14

代码语言:javascript
复制
DECLARE @combinedString VARCHAR(MAX),
@id int,
@Diagnosis VARCHAR(MAX)


Declare @CONCATRESULT TABLE (AttendanceID int, Question VARCHAR(MAX) )

Declare Dia cursor for 
SELECT  AttendanceID FROM  Diagnosis 

open Dia 
Fetch next from Dia into @id
while @@FETCH_STATUS=0 
begin

SELECT   @combinedString = COALESCE(@combinedString + ', ', '') + Diagnosis,         
@id=AttendanceID  FROM [Prod_PostData].[dbo].[KMSFPostDataDenorm] d
WHERE AttendanceID = @id

insert into @CONCATRESULT values (  @id ,@combinedString )

SET @combinedString = null

Fetch next from Dia into @id
end
close Dia
deallocate Dia


select
dg.AttendanceID
, dg.PatientNumber
, dg.DiagnosisDate
, dg.Diagnosis
from
Diagnosis dg join (select * from @CONCATRESULT) Y on dg.AttendanceID=Y.AttendanceID 
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/20216019

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档