concatunate comments - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

concatunate comments

Author

Topic

abenitez77
Yak Posting Veteran

53 Posts

Posted - 2011-03-28 : 14:59:02

I have 2 tables. 1 - header table 2- comments table.
There are multiple comments in the comments table that are related to 1 header record. I want to select fields from header table and where I have multiple records in the comments table, I want to view that as 1 record, by concatunating the records to look like 1 record. How can i do this?

ie:
header
ID vendor# Vendor name Year
1 12345 ABC 2009
2 23453 ACME 2010

comments table:
ID vendor# Comments
1 12345 This is a test comment to see if it works.
2 12345 Add this to make it 1 record.
3 12345 Yes, it works.
4 23453 Another comments line.
5 23453 See if it works now?

Results
ID Vendor# Vendor name Year Comments
1 12345 ABC 2009 This is a test comment to see if it works. Add this to make it 1 record. Yes, it works.
4 23453 ACME 2010 Another comments line. See if it works now?

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2011-03-28 : 16:13:55

This looks similar to what you are doing
http://www.projectdmx.com/tsql/rowconcatenate.aspx

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2011-03-28 : 16:36:47

I have drafted this based on the url given, you can go ahead and tweek it.

WITH CTE ( ID, COMMENT_LIST,COMMENTS,VENDOR_#,VENDOR_NAME,YEAR,len )
AS ( SELECT s.ID, CAST( '' AS VARCHAR(8000) ), CAST( '' AS VARCHAR(8000) ),
h.VENDOR_#,h.VENDOR_NAME,h.YEAR,0
FROM dbo.COMMENTS s INNER JOIN dbo.HEADER h ON s.ID=h.ID
GROUP BY s.ID,h.VENDOR_#,h.VENDOR_NAME,h.YEAR
UNION ALL
SELECT p.ID, CAST( COMMENT_LIST +
CASE WHEN len(p.COMMENTS) > 0 THEN ' ' ELSE ', ' END + p.COMMENTS AS VARCHAR(8000) ),
CAST( p.COMMENTS AS VARCHAR(8000)),
h.VENDOR_#,h.VENDOR_NAME,h.YEAR,len + 1
FROM CTE c
INNER JOIN dbo.COMMENTS p ON c.ID = p.ID
INNER JOIN dbo.HEADER h ON c.ID=h.ID
WHERE p.COMMENTS > c.COMMENTS )
SELECT ID, VENDOR_#,VENDOR_NAME,YEAR,COMMENT_LIST
FROM ( SELECT ID, COMMENT_LIST,VENDOR_#,VENDOR_NAME,YEAR,
RANK() OVER ( PARTITION BY ID ORDER BY len DESC)
FROM CTE ) D ( ID, COMMENT_LIST,VENDOR_#,VENDOR_NAME,YEAR, rank )
WHERE rank = 1 ;

abenitez77
Yak Posting Veteran

53 Posts

Posted - 2011-03-29 : 18:21:33

Worked like a charm!
Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources