How to Maintain unique records and concatenate

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

General SQL Server Forums

New to SQL Server Programming

How to Maintain unique records and concatenate

Author

Topic

torero13
Starting Member

7 Posts

Posted - 2010-02-04 : 11:35:58

Hello All:
I have a table REMARKS in SQL Server 200 that was imported from AS400 with the following columns.
YEAR
MARKET
INVOICE#
DOC TYPE
REMARKS
On AS400 when you include the remarks sometimes it creates more than one line of comments, so I have some duplicated records because for the same year, market, invoice# and doc type, there are 2 or 3 lines of remarks.
My question is: Is there a way to eliminate duplicated records and concatenate the remarks in the same field?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 11:43:46

yup. but are you using sql 2005?

torero13
Starting Member

7 Posts

Posted - 2010-02-04 : 11:44:33

SQL 2000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-04 : 11:53:28

quote:
Originally posted by torero13

SQL 2000

then you can do like this



CREATE FUNCTION dbo.ConcatRemarks
(
@Year datatype,
@Market datatype,
@INVOICENo datatype,
@DOC_TYPE datatype
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @Remarks varchar(8000)

SELECT @Remarks=COALESCE(@Remarks+ ' ','') + REMARKS
FROM REMARKS
WHERE YEAR=@Year
AND MARKET= @Market
AND [INVOICE#]=@INVOICENo
AND [DOC TYPE]= @DOC_TYPE

RETURN @Remarks
END

then call it like

SELECT DISTINCT YEAR,
MARKET,
[INVOICE#],
[DOC TYPE],
dbo.ConcatRemarks(YEAR,MARKET,[INVOICE#],[DOC TYPE]) AS Remarks
FROM REMARKS

i dont know your actual datatypes so make sure you fill it correctly in parameter definition

torero13
Starting Member

7 Posts

Posted - 2010-02-04 : 18:43:55

I needed to change a couple of things but it worked as a charm. Thank you so much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-05 : 08:44:47

welcome

vcs1161
Starting Member

10 Posts

Posted - 2010-02-08 : 14:59:01

Would this same method work by keeping the lines in ascending order before you do the coalesce? What if each line number is unique for the duplicate reords like they are here?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources