Concate all rows into single one row

Question

Post reply

Concate all rows into single one row

gksharmaajmer

Ten Centuries

Points: 1120
More actions
November 10, 2014 at 10:06 pm

#99985

Using SQL Server 2000 EE On Winodws Server 2000
create table abcx
(
deptname varchar(10),
doctorid int,
patient_name varchar(20)
)
insert into abcx values ('ICU-1',10,'ABC')
insert into abcx values ('ICU-2',10,'H.')
insert into abcx values ('ICU-2',10,'ASLAM')
insert into abcx values ('ICU-3',10,'HEENA')
insert into abcx values ('KPM',10,'SURESH')
insert into abcx values ('CCU',10,'MUKESH')
insert into abcx values ('CCU',10,'RAM')
Required Ouput :
'Dear New Doctor Name:' + 'following patients have been transferred order No:' + 'from Old Doctor Name:' + ICU-1:ABC.ICU-2:H.,ASLAM.ICU-3:HEENA.KPM:SURESH.CCU:MUKESH,RAM.
User will provide New Doctor Name, Order No, and Old Doctor Name from front end combo box and it should be generated above one single line.
For this purpose I followed below link:
http://www.sqlservercentral.com/Forums/Topic862480-169-1.aspx
and I created function like this :
create function dbo.concate (@company varchar(4))
returns nvarchar(1000)
as
begin
declare @concat nvarchar(1000)
select @concat=coalesce(@concat,'')+Patient_Name+',' from ABCX where deptname =@company
select @concat = substring(@concat,1,len(@concat)-1)
return (@concat)
end
I am doing something like this :
DECLARE @line VARCHAR(1024)
SELECT @line = COALESCE(@line + ',', '') + deptname + '-' + dbo.concate(deptname) FROM abcx
group by deptname
SELECT line = 'Dear New Doctor Name'+' following patients have been transferred order: ' + @line
GO
and getting :
Dear New Doctor Name following patients have been transferred order: KPM-SURESH
I don't know why it is not returning all the rows....
Kindly help me to write a procedure like this one :
exec myproc('New Doctor Name','Order No','Old Doctor Name').
Thank you for viewing the question.
Kindly tell me, if I am unclear in my question, so that I may provide more details.
Regards
Girish Sharma

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

daniarchitect SSCommitted Points: 1727 More actions · Answer 1

create table #abcx

(

deptname varchar(10),

doctorid int,

patient_name varchar(20)

)

insert into #abcx values ('ICU-1',10,'ABC')

insert into #abcx values ('ICU-2',10,'H.')

insert into #abcx values ('ICU-2',10,'ASLAM')

insert into #abcx values ('ICU-3',10,'HEENA')

insert into #abcx values ('KPM',10,'SURESH')

insert into #abcx values ('CCU',10,'MUKESH')

insert into #abcx values ('CCU',10,'RAM')

--http://stackoverflow.com/questions/12668528/sql-server-group-by-clause-to-get-comma-separated-values

SELECT a.deptname ,

STUFF((SELECT ', ' + patient_name

FROM #abcx b

WHERE b.deptname = a.deptname

FOR XML PATH('')), 1, 2, '')

FROM #abcx a

GROUP BY deptname

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 2

Steve Jones - SSC Editor

SSC Guru

Points: 734459

December 30, 2014 at 2:20 pm

#1767083

I'd use the solution above to concatenate the values.

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 3

Steve Jones - SSC Editor (12/30/2014)
I'd use the solution above to concatenate the values.

Agreed. Just make sure that you add TYPE correctly to de-entitize the return if it contains the XML "special characters".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)