December 17, 2015 at 1:52 am
Hi,
I have a diary table in my database that splits the diary note across multiple lines. I need to work out a way to concatenate the contents for every unique diary note.
The notealtkey shows you the unique diary note , and you will see a lineno (line number) is included, which could be as high as 10 lines (but this could in theory be any number). So I need a way that each diary note is just on one row and not multiple lines.
Thanks
Table below
/****** Object: Table [dbo].
Script Date: 15/10/2015******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE
(
[notealtkey] [VARCHAR](50)
,[notecode] [VARCHAR](10)
,[notedate] date
,[lineno] int
,[linetext] [varchar](200)
,[id] [INT] IDENTITY(1, 1)
)
GO
SET ANSI_PADDING OFF
GO
CREATE INDEX [IDIndex] ON
(id)
GO
Some data
INSERT INTO
(notealtkey,notecode,notedate,[lineno],linetext)
SELECT '1769','DI','2001-10-04','1','FTL2 SENT 04.10.01' UNION ALL
SELECT '10286','DI','2010-03-30','1','paying more than £15 arrangement at the mo monitor' UNION ALL
SELECT '10286','DI','2010-03-30','2',' carefully' UNION ALL
SELECT '10862','DI','2008-06-12','1','PLSE READ PREVIOUS NOTES - RP CARD ORDER CANCELLED' UNION ALL
SELECT '10862','DI','2008-06-12','2','.' UNION ALL
SELECT '13678','DI','2012-12-10','1','still monitoring' UNION ALL
SELECT '14574','DI','2008-09-24','1','confirmed with tenant £29.20 in arrears. PD £4 24/' UNION ALL
SELECT '14574','DI','2008-09-24','2','09 and £8 25/09 aim to clear the remain balance of' UNION ALL
SELECT '14574','DI','2008-09-24','3','17.2' UNION ALL
SELECT '15918','DI','2012-07-02','1','has been on holiday for 2 weeks - will pay outstan' UNION ALL
SELECT '15918','DI','2012-07-02','2','ding balance tomorrow' UNION ALL
SELECT '17070','DI','2010-03-17','1','Tel call from Keith Bally Social Worker 018737359' UNION ALL
SELECT '17070','DI','2010-03-17','2','00. Dealing with Danny Hughes, David agrees to pay' UNION ALL
SELECT '17070','DI','2010-03-17','3',' half at £5 per week by payment card. In relation' UNION ALL
SELECT '17070','DI','2010-03-17','4',' to former joint tenant Dave, don''t know where' UNION ALL
SELECT '17070','DI','2010-03-17','5','he is at moment last heard living in a caravan on' UNION ALL
SELECT '17070','DI','2010-03-17','6','a farm near Bath. Payment card to be sent.'
December 17, 2015 at 3:26 am
CONCAT is designed for stuff on the same row. For concatenating stuff from different rows you need an alternative approach, most of which are covered in Aaron Bertrand's article here.
Try this:
SELECT notealtkey, notecode,notedate, x.linetext
FROM
AS p
CROSS APPLY (
SELECT linetext = (
SELECT linetext
FROM
AS p2
WHERE p2.notealtkey = p.notealtkey
ORDER BY [lineno]
FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'
)
) x
GROUP BY notealtkey, notecode, notedate, x.linetext
ORDER BY notedate;
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 17, 2015 at 3:29 am
You could use a variant of this code which uses SELECT...FOR XML
http://sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path/
There are many other examples of this online
--
Scott
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply