Concatenating Fields with a Control Structure? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Concatenating Fields with a Control Structure?

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2009-09-21 : 11:48:41

The statement I'm using only has interesting data on the Diagnosis field, while all the other rows are repeated the number of times Diagnosis gives a row. Can I concantenate each line of Diagnosis into one?

I can always do this when I know how many there are. With each PatientNumber repeating from one to any number per group, I need to create this concat to auto-populate a wide field.

There aren't groups in this SQL, but it is done in Crystal Reports nicely. Is there a good was to move rows to a long field??

select distinct
D.PatientNumber,
PostCode,
PCTName,
App.AttendanceType,
SessionName,
SessionDate,
SlotTime,
SessionCategory,
Status,
Diagnosis
from Demographic D
inner join vrAppointments App on App.PatientNumber = D.PatientNumber
inner join vrDiagnosis Dia on Dia.PatientNumber = D.PatientNumber
inner join vrGUAccessPhase2and3 GUA23 on GUA23.PatientNumber = D.PatientNumber
where SessionDate between '11-09-2009' and '18-09-2009'
and App.AttendanceType in ('New Patient','Rebook Patient','Follow-up (Old)')

Thanks

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2009-09-21 : 19:27:10

Check Garth's article on Using Coalesce to Build a Comma-Delimited String

--------------------------------------------
Brand yourself at EmeraldCityDomains.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-22 : 02:59:10

<<
There aren't groups in this SQL, but it is done in Crystal Reports nicely.
>>

Where do you want to show concatenated data?

Madhivanan

Failing to plan is Planning to fail

mikebird
Aged Yak Warrior

529 Posts

Posted - 2009-09-22 : 04:11:42

Ideally, I want to get the rows into a formula in Crystal, and replace the field listing the rows with that formula.

If difficult, I'd like to do this with the statement posted above... The grouping in Crystal is 1. SessionDate, 2. SlotTime, 3. PatientNumber. Can these be done with the statement, along with the concatenation?

mikebird
Aged Yak Warrior

529 Posts

Posted - 2009-09-22 : 04:13:22

Is PIVOT the way the get the Diagnosis rows into a concatenated field, or CSE statement?

mikebird
Aged Yak Warrior

529 Posts

Posted - 2009-09-22 : 11:01:24

Had a good look at the Coalesce function, which UI hadn't used before.

All has been very nicely solved with a set of procedures in Crystal.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources