July 15, 2014 at 2:24 am
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.
July 15, 2014 at 2:30 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 15, 2014 at 2:55 am
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
July 15, 2014 at 2:58 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 15, 2014 at 3:51 am
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.
July 15, 2014 at 4:18 am
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
July 22, 2014 at 6:34 am
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 :angry:) 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?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply