CODEWITHSUNDEEP
sqlsql-servert-sql
Craig
Craig

Reputation: 33

Combine Unique Column Values Into One to Avoid Duplicates

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?

Upvotes: 0

Views: 3139

Answers (3)

aledpardo
aledpardo

Reputation: 761

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.

Upvotes: 1

SteveB
SteveB

Reputation: 799

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

Upvotes: 1

M.Ali
M.Ali

Reputation: 69504

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.

Upvotes: 1

Related Questions