Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Concatenating fields into one Expand / Collapse
Author
Message
Posted Tuesday, July 15, 2014 2:24 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:42 AM
Points: 334, Visits: 406
Hi all

I'm running SQL 2005 with SSMS 2012.
I've got a table with the following fields:
Visit ID
Abstract ID
QuerySeqID (Query Sequence ID)
Response


The Visit ID and Abstract are unique to a patient/visit, the QuerySeqID increases by 1 up to a possibly infinite number (the highest I've got so far is 18).
The Response field conains the notes I need to concatenate.

I've tried the follwoing code:-
;with cte as
(select
*
from
[livedb_daily].[dbo].[AbsProjectsQueriesMultCs] mult1
where
mult1.[Query]='Additional Notes'
)

select
c.VisitID
,c.AbstractID
,c.QuerySeqID
,c.Response+c1.Response
from
cte c
left outer join cte c1
on c.VisitID=c1.VisitID
and c.AbstractID=c1.AbstractID
and c1.QuerySeqID=c.QuerySeqID+1
where
c1.response is not null
order by
c.VisitID
,c.AbstractID
,c.QuerySeqID

and it's getting me close, but......
If there's a record with 3 lots of notes, I'm getting the following:
Note1 + Note2
Note2 + Note3

What I want to get to is:-
Note1 + Note2 + Note3 + ....... Note(n)

I don't think I'm far off with what I'm doing but I can't seem to get everything into one record.

Would someone be kind enough to have a look and see where I'm going wrong/where the code needs a tweak?

I'd post some data but it's sensitive.
Post #1592447
Posted Tuesday, July 15, 2014 2:30 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:29 AM
Points: 43,022, Visits: 36,182
Have a look at this (the XML method mainly): https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1592448
Posted Tuesday, July 15, 2014 2:55 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:42 AM
Points: 334, Visits: 406
Thanks for that Gail
I've brought back one set of records using each method and this is what I'm getting for the Response field....
My CTE:-
4 Cant change status to final
Cant change status to final Procedure needs removing

The XML Path method:-
UNFIN,4 ,CANT CHANGE STATUS TO FINAL ,PROCEDURES NEED REMOVING,


I can remove the final comma easily enough, but I'm not sure where the UNFIN and the "&xOD;" bit's are coming from (I'm assuming "&xOD;" is a carriage returns, line feed or similar).

Any ideas?


::edit::
Forgot to include my updated code:-
select
mult1.VisitID
,mult1.AbstractID
,(select
Response + ','
from
[livedb_daily].[dbo].[AbsProjectsQueriesMultCs] mult2
where
mult2.VisitID=mult1.VisitID
and mult2.AbstractID=mult1.AbstractID
order by
mult2.VisitID
,mult2.AbstractID
for xml path ('')) as overall
from
[livedb_daily].[dbo].[AbsProjectsQueriesMultCs] mult1
where
mult1.VisitID='RA0-20120603025910735'
and mult1.[Query]='Additional Notes'

group by
mult1.VisitID
,mult1.AbstractID

Post #1592452
Posted Tuesday, July 15, 2014 2:58 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:29 AM
Points: 43,022, Visits: 36,182
The 0x... Could be XML encoding. REPLACEs are often necessary to fix that. The unfin not coming from the data? Check filters.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1592453
Posted Tuesday, July 15, 2014 3:51 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:42 AM
Points: 334, Visits: 406
Thanks Gail.
I've written the REPLACE to get rid of the x0D's and that bit's worked.

You were right about the UNFIN, it was picking up a previous field that I hadn't filtered out in the sub-select query.

Thanks for all your help.
Post #1592475
Posted Tuesday, July 15, 2014 4:18 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:58 AM
Points: 1,974, Visits: 5,131
Quick suggestion, add the .value() method to get the correct output

select
mult1.VisitID
,mult1.AbstractID
,(select
Response + ','
from
[livedb_daily].[dbo].[AbsProjectsQueriesMultCs] mult2
where
mult2.VisitID=mult1.VisitID
and mult2.AbstractID=mult1.AbstractID
order by
mult2.VisitID
,mult2.AbstractID
for xml path (''), TYPE).value('.[1]','NVARCHAR(MAX)') as overall
from
[livedb_daily].[dbo].[AbsProjectsQueriesMultCs] mult1
where
mult1.VisitID='RA0-20120603025910735'
and mult1.[Query]='Additional Notes'

group by
mult1.VisitID
,mult1.AbstractID

Post #1592483
Posted Tuesday, July 22, 2014 6:34 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:42 AM
Points: 334, Visits: 406
Hi again

The powers that be have now decided they only need the first 300 characters from the XML-based code I'm currently using.
I've left the code as-is and I'm using
left([Additional Notes].Comments,300)

to get the bit I need.
Is there any way of restricting the code below to just return 300 characters (hopefully it will speed things up as well):-
select
mult1.VisitID
,mult1.AbstractID
,replace(replace(replace(replace((
select
Response + ','
from
[livedb_daily].[dbo].[AbsProjectsQueriesMultCs] mult2
where
mult2.VisitID=mult1.VisitID
and mult2.AbstractID=mult1.AbstractID
and mult2.[Query]='Additional Notes'
order by
mult2.VisitID
,mult2.AbstractID
for xml path ('')),' ',''),'&','&'),char(13),''),char(10),'') as Comments
from
[livedb_daily].[dbo].[AbsProjectsQueriesMultCs] mult1
where
mult1.[Query]='Additional Notes'
group by
mult1.VisitID
,mult1.AbstractID) [Additional Notes]

It doesn't take too long (about 20 minutes) to sort out just over 1,000,000 records (there are quite a few joins) but a saving is a saving.

I've already enquired about adding indexes (there aren't any ) and that should speed things up as well.
I can't add the indexes directly as this is a third-part app that writes to a SQL database.

Any pointers gratefully received).

As an aside, I don't quite understand how the FOR XML PATH thing works and the link didn't really explain it (nor does any site I've found courtesy of Google). Would someone kindly explain?
Post #1595057
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse