如何在SQL 2005中将数据从列连接到单行? [英] How to Concat Data From a Column to a Single Row In SQL 2005 ?

问题描述

大家好，
我想知道如何将数据从一列连接到单行.

例如我的病人表为:

Hi all,
I wanna know how can i concat data from a column to a single row.

e.g. I have Patient Table as :

Patient ID          Patient Name
    1                 Mr. John 

然后，我的患者历史记录表为:

Then I have Patient History Table as :

PatientID    PatientHistoryID             Diagnosis          
   1                1                       Asthma
   1                2                       diabetes   
   1                3                       Cystic Fibrosis  

我想要这样的患者数据:

I want to get Patient Data like that :

Patient ID          Patient Name                Diagnosis           
   1                   Mr. John                Asthma, diabetes, Cystic Fibrosis

我无法根据需要检索诊断列表.请大家帮帮我.

问候，
nway nge

I can''t retrieve Diagnosis List as I want. Please help me everyone.

regards,
nway nge

推荐答案

尝试

SELECT patientid + patientname from table

.

这里仅是一条SQL语句所需要的内容:

Here is what you wanted in only one SQL statement:

with a(i, j, n, d, e)
as
(
select p.PatientID, ph.PatientHistoryID , cast(ph.Diagnosis as varchar(max)), 1 , p.PatientName from  Patient p inner join [Patient History] ph on p.PatientID = ph.PatientID where ph.PatientHistoryID = ( select max(ph.PatientHistoryID)from [Patient History] ph where ph.PatientID = p.PatientID)
union all
select a.i, ph.PatientHistoryID , cast(ph.Diagnosis + ', ' + a.n as varchar(max)), d+1, a.e  from a inner join [Patient History] ph on a.i = ph.PatientID where ph.PatientHistoryID<a.j
)
select 'Patient ID :'+ cast(patientid as varchar(max))+' , Patient Name : '+ e +' , Diagnosis : '+ n report from
(select i patientid, MAX(d) diagid from a group by i) g , (  select * from a  ) h
where g.patientid = h.i and diagid = h.d
order by patientid

对不起，我使用了不方便的命名方式，但是要迅速回答，对我来说是必要的:)

希望对您有所帮助.

Excuse me for inconvenient naming style but for reaching to answer quickly it was necessary for me :)

Hope it helps.

这篇关于如何在SQL 2005中将数据从列连接到单行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！