Combine Unique Column Values Into One to Avoid Duplicates

Question

For simplicity, assume I have two tables joined by account#. The second table has two columns, id and comment. Each account could have one or more comments and each unique comment has a unique id.

I need to write a t-sql query to generate one row for each account - which I assume means I need to combine as many comments as might exit for each account. This assumes the result set will only show the account# once. Simple?

What is exactly the expected output? And what dbms are you using? — Filipe Silva, Dec 19 '13 at 17:17

score 1 · Answer 1 · answered Dec 19 '13 at 17:28

Sql Server is a RDBMS best tuned for storing data and retrieving data, you can retrieve the desired data with one very simple query but the desired format should be handled with any of the reporting tools available like ssrs or crystal reports

Your query will be a simple inner join something like this

SELECT A.Account , B.Comment
FROM TableA AS A INNER JOIN TableB AS B
ON A.Account = B.Account

Now you can use your reporting tool to Group all the Comments by Account when Displaying data.

score 1 · Answer 2 · answered Dec 19 '13 at 17:40

1

I do agree with M. Ali, but if you don't have that option, the following will work.

SELECT [accountID]
 , [name]
 , (SELECT CAST(Comment + ', ' AS VARCHAR(MAX)) 
     FROM [comments] 
     WHERE (accountID = accounts.accountID) 
     FOR XML PATH ('')
  ) AS Comments
FROM accounts

SQL Fiddle

answered Dec 19 '13 at 17:40

SteveB

769
4
18

My actual three fields are Acctno, ID and Comment. They are from table dbo.Workout_Comments. Since I do not have rights to create functions as you suspected, I tried using my my fields and table in your query, and I get an (Invalid object name 'Comments) referencing Line 1 so I must be misinterpreting your solution. Greatly appreciate any clarification. Thanks! – Craig Dec 19 '13 at 20:31
I assume [name] would be replaced by my field Acctno? – Craig Dec 19 '13 at 20:33
Give me the breakout of each table and its fields. – SteveB Dec 19 '13 at 20:45
Select [ID], [Acctno], (Select Cast (Comment + ', ' as varchar(max)) From dbo.Workout_Group_Comments Where (ID = dbo.Workout_Group_Comments.ID) For XML Path ('') )As Comments From dbo.Workout_Group_Comments – Craig Dec 19 '13 at 21:11
The above works BUT it does not combine the unique comment fields into one so that only one row is rendered per account - if an account has 3 rows (comments) that account has 3 rows... Thanks! – Craig Dec 19 '13 at 21:13
I'm keeping this to one table to keep it simple – Craig Dec 19 '13 at 21:14
Keeping it to one table makes it interesting. I am assuming that the Acctno repeats in this table (you use it to know which comment is for which account). Try this: Select DISTINCT [Acctno], (Select Cast (Comment + ', ' as varchar(max)) From #Workout_Group_Comments Where Acctno = WGC.Acctno For XML Path ('') ) As Comments From #Workout_Group_Comments AS WGC – SteveB Dec 19 '13 at 21:40
Here's an example of the resulting data set: ID Acctno Comment 21 1005 Here is my comment 1 22 1005 Here is my comment 2 23 1005 Here is my comment 3 – Craig Dec 19 '13 at 21:42
Since your data is in one table, you can't include the ID. It should be useless anyway since it looks as if it is just an Indentity field. When asking a question like this, it is always good to give your table structures, data and expected output. This will make it easier to answer the question in the manner you need and it will happen more quickly. – SteveB Dec 19 '13 at 21:48
I think you're missing a `GROUP BY`. The nested `SELECT` should return one row for each unique AccountID so I think this should/would work. http://stackoverflow.com/questions/10381512/what-this-query-does-to-create-comma-delimited-list-sql-server – Eric J. Price Dec 19 '13 at 23:20

score 1 · Answer 3 · answered Dec 19 '13 at 17:43

In my actual project I have this exact situation. What you need is a solution to aggregate the comments in order to show only one line per account#.

I solve it by creating a function to concatenate the comments, like this:

create function dbo.aggregateComments( @accountId integer, @separator varchar( 5 ) )
as
begin;
    declare @comments varchar( max ); set @comments = '';

    select @comments = @comments + @separator + YouCommentsTableName.CommentColumn
      from dbo.YouCommentsTableNAme
     where YouCommentsTableName.AccountId = @accountId;

    return @comments;
end;

You can use it on you query this way:

select account#, dbo.aggretateComments( account#, ',' )
  from dbo.YourAccountTableName

Creating a function will give you a common place to retrieve your comments. It's a good programming practice.

Combine Unique Column Values Into One to Avoid Duplicates

3 Answers3