sql server关于合并列的问题

Question

试试这个：select distinct dbo.PatientInfo.ID AS 序号, dbo.PatientInfo.Name AS 姓名, dbo.PatientInfo.Age AS 年龄, dbo.PatientInfo.CaseNO AS 病历号, dbo.PatientInfo.RegistrationNO AS 登记号,dbo.PatientInfo.SerialNO AS 编号, dbo.PatientInfo.IC AS 身份证号 ,(select SUM(DateNO)  from Hospitalization where Hospitalization.PatientInfoID=PatientInfo.ID)  住院天数 ,
STUFF((Select isnull(','+'宫腔粘连','') from GongQiangZhanLian where PatientInfo.ID = GongQiangZhanLian.PatientInfoID  for xml path ('')),1,1,'')   as 病种 FROM         dbo.PatientInfo
left join GongQiangZhanLian on PatientInfo.ID = GongQiangZhanLian.PatientInfoID  
GO

Answer 1

我在后面追加了一条STUFF((Select isnull(','+'宫内病变','') from GongQiangNeiBingBian where PatientInfo.ID = GongQiangNeiBingBian.PatientInfo_ID  for xml path ('')),1,1,'')， 用+号连接的，还是不行这是原没有追加宫内病变的数据显示序号          姓名                   年龄   病历号                                                登记号                                                编号                                                 身份证号                                               住院天数        病种
----------- -------------------- ---- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
218         张氏                   21   2012-00001                                         20120918054511                                     P002161                                            00000000y01                                        10          宫腔粘连,宫腔粘连
220         秦女士                  28   20120920001                                        20120920103202                                     P002181                                            12345678987654327890                               47          宫腔粘连,宫腔粘连
221         郝淑萍                  38   446825                                             20121227095258                                     P002201                                            152701197203200629                                 7           宫腔粘连
222         刘婧歆                  30   J447339                                            20121227104459                                     P002211                                            43040219800225302x                                 NULL        NULL
223         钱岩                   28   477337                                             20130325045056                                     P002221                                            370802198501285128                                 NULL        NULL
227         王银龙                  18   0000000012                                         20130401092517                                     P002231                                            329812199304010012                                 5           NULL
229         张四娥                  26   477540                                             20130401035320                                     P002281                                            142223198605187424                                 NULL        宫腔粘连,宫腔粘连,宫腔粘连
230         秦春皊                  28   472564                                             20130401044028                                     P002291                                            341222198503208503                                 32          宫腔粘连,宫腔粘连
251         test001              25   1234567                                            20131024065357                                     P002301                                            110104198904085987                                 7           宫腔粘连,宫腔粘连,宫腔粘连
256         呆蓝                   28   010001000001                                       20131127043403                                     P002511                                            372301198601020700                                 20          宫腔粘连(10 行受影响)这是追加宫内病变之后的数据显示序号          姓名                   年龄   病历号                                                登记号                                                编号                                                 身份证号                                               住院天数        病种
----------- -------------------- ---- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
218         张氏                   21   2012-00001                                         20120918054511                                     P002161                                            00000000y01                                        10          NULL
220         秦女士                  28   20120920001                                        20120920103202                                     P002181                                            12345678987654327890                               47          NULL
221         郝淑萍                  38   446825                                             20121227095258                                     P002201                                            152701197203200629                                 7           NULL
222         刘婧歆                  30   J447339                                            20121227104459                                     P002211                                            43040219800225302x                                 NULL        NULL
223         钱岩                   28   477337                                             20130325045056                                     P002221                                            370802198501285128                                 NULL        NULL
227         王银龙                  18   0000000012                                         20130401092517                                     P002231                                            329812199304010012                                 5           NULL
229         张四娥                  26   477540                                             20130401035320                                     P002281                                            142223198605187424                                 NULL        NULL
230         秦春皊                  28   472564                                             20130401044028                                     P002291                                            341222198503208503                                 32          NULL
251         test001              25   1234567                                            20131024065357                                     P002301                                            110104198904085987                                 7           NULL
256         呆蓝                   28   010001000001                                       20131127043403                                     P002511                                            372301198601020700                                 20          宫腔粘连宫内病变,宫内病变,宫内病变(10 行受影响)

Answer 2

我在后面追加了一条STUFF((Select isnull(','+'宫内病变','') from GongQiangNeiBingBian where PatientInfo.ID = GongQiangNeiBingBian.PatientInfo_ID  for xml path ('')),1,1,'')， 用+号连接的，还是不行这是原没有追加宫内病变的数据显示序号          姓名                   年龄   病历号                                                登记号                                                编号                                                 身份证号                                               住院天数        病种
----------- -------------------- ---- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
218         张氏                   21   2012-00001                                         20120918054511                                     P002161                                            00000000y01                                        10          宫腔粘连,宫腔粘连
220         秦女士                  28   20120920001                                        20120920103202                                     P002181                                            12345678987654327890                               47          宫腔粘连,宫腔粘连
221         郝淑萍                  38   446825                                             20121227095258                                     P002201                                            152701197203200629                                 7           宫腔粘连
222         刘婧歆                  30   J447339                                            20121227104459                                     P002211                                            43040219800225302x                                 NULL        NULL
223         钱岩                   28   477337                                             20130325045056                                     P002221                                            370802198501285128                                 NULL        NULL
227         王银龙                  18   0000000012                                         20130401092517                                     P002231                                            329812199304010012                                 5           NULL
229         张四娥                  26   477540                                             20130401035320                                     P002281                                            142223198605187424                                 NULL        宫腔粘连,宫腔粘连,宫腔粘连
230         秦春皊                  28   472564                                             20130401044028                                     P002291                                            341222198503208503                                 32          宫腔粘连,宫腔粘连
251         test001              25   1234567                                            20131024065357                                     P002301                                            110104198904085987                                 7           宫腔粘连,宫腔粘连,宫腔粘连
256         呆蓝                   28   010001000001                                       20131127043403                                     P002511                                            372301198601020700                                 20          宫腔粘连(10 行受影响)这是追加宫内病变之后的数据显示序号          姓名                   年龄   病历号                                                登记号                                                编号                                                 身份证号                                               住院天数        病种
----------- -------------------- ---- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
218         张氏                   21   2012-00001                                         20120918054511                                     P002161                                            00000000y01                                        10          NULL
220         秦女士                  28   20120920001                                        20120920103202                                     P002181                                            12345678987654327890                               47          NULL
221         郝淑萍                  38   446825                                             20121227095258                                     P002201                                            152701197203200629                                 7           NULL
222         刘婧歆                  30   J447339                                            20121227104459                                     P002211                                            43040219800225302x                                 NULL        NULL
223         钱岩                   28   477337                                             20130325045056                                     P002221                                            370802198501285128                                 NULL        NULL
227         王银龙                  18   0000000012                                         20130401092517                                     P002231                                            329812199304010012                                 5           NULL
229         张四娥                  26   477540                                             20130401035320                                     P002281                                            142223198605187424                                 NULL        NULL
230         秦春皊                  28   472564                                             20130401044028                                     P002291                                            341222198503208503                                 32          NULL
251         test001              25   1234567                                            20131024065357                                     P002301                                            110104198904085987                                 7           NULL
256         呆蓝                   28   010001000001                                       20131127043403                                     P002511                                            372301198601020700                                 20          宫腔粘连宫内病变,宫内病变,宫内病变(10 行受影响)
对了，把你加了1条stuff... 后的完整语句贴出来看看

Answer 3

select distinct dbo.PatientInfo.ID AS 序号, dbo.PatientInfo.Name AS 姓名, dbo.PatientInfo.Age AS 年龄, dbo.PatientInfo.CaseNO AS 病历号, dbo.PatientInfo.RegistrationNO AS 登记号,dbo.PatientInfo.SerialNO AS 编号, dbo.PatientInfo.IC AS 身份证号 ,(select SUM(DateNO)  from Hospitalization where Hospitalization.PatientInfoID=PatientInfo.ID)  住院天数 ,
STUFF((Select isnull(','+'宫腔粘连','') from GongQiangZhanLian where PatientInfo.ID = GongQiangZhanLian.PatientInfoID  for xml path ('')),1,1,'') + STUFF((Select isnull(','+'宫内病变','') from GongQiangNeiBingBian where PatientInfo.ID = GongQiangNeiBingBian.PatientInfo_ID  for xml path ('')),1,1,'')   as 病种 FROM         dbo.PatientInfo
left join GongQiangZhanLian on PatientInfo.ID = GongQiangZhanLian.PatientInfoID  
GO
上面的数据太多了  我就不引用了，这就是加了stuff之后的查询

Answer 4

试试这个：
select distinct dbo.PatientInfo.ID AS 序号, dbo.PatientInfo.Name AS 姓名, dbo.PatientInfo.Age AS 年龄, dbo.PatientInfo.CaseNO AS 病历号, dbo.PatientInfo.RegistrationNO AS 登记号,dbo.PatientInfo.SerialNO AS 编号, dbo.PatientInfo.IC AS 身份证号 ,(select SUM(DateNO)  from Hospitalization where Hospitalization.PatientInfoID=PatientInfo.ID)  住院天数 ,
isnull(STUFF((Select ','+'宫腔粘连' from GongQiangZhanLian where PatientInfo.ID = GongQiangZhanLian.PatientInfoID  for xml path ('')),1,1,''),'') + 
isnull(STUFF((Select ','+'宫内病变' from GongQiangNeiBingBian where PatientInfo.ID = GongQiangNeiBingBian.PatientInfo_ID  for xml path ('')),1,1,''),'')   as 病种 FROM         dbo.PatientInfo
left join GongQiangZhanLian on PatientInfo.ID = GongQiangZhanLian.PatientInfoID  
GO

Answer 5

试试这个：
select distinct dbo.PatientInfo.ID AS 序号, dbo.PatientInfo.Name AS 姓名, dbo.PatientInfo.Age AS 年龄, dbo.PatientInfo.CaseNO AS 病历号, dbo.PatientInfo.RegistrationNO AS 登记号,dbo.PatientInfo.SerialNO AS 编号, dbo.PatientInfo.IC AS 身份证号 ,(select SUM(DateNO)  from Hospitalization where Hospitalization.PatientInfoID=PatientInfo.ID)  住院天数 ,
isnull(STUFF((Select ','+'宫腔粘连' from GongQiangZhanLian where PatientInfo.ID = GongQiangZhanLian.PatientInfoID  for xml path ('')),1,1,''),'') + 
isnull(STUFF((Select ','+'宫内病变' from GongQiangNeiBingBian where PatientInfo.ID = GongQiangNeiBingBian.PatientInfo_ID  for xml path ('')),1,1,''),'')   as 病种 FROM         dbo.PatientInfo
left join GongQiangZhanLian on PatientInfo.ID = GongQiangZhanLian.PatientInfoID  
GO
大神 太感谢了！！！成功了！！