Concatenating like records

pvasudevan · November 18th, 2003, 02:33 AM

I got records like this:

ColumnA ColumnB
---------- -----------------------
aa This is
aa Thursday morning
bb Friday morning
cc This is
cc Saturday morning

Is it possible to write a select query to return rows like the following:
ColumnA ColumnB
---------- ------------------------------
aa This is Thursday morning
bb Friday morning
cc This is Saturday morning

Kindly help. TIA

Code for creating/insertion:
create table #temp1 (a char(10), b varchar(4000))

insert into #temp1 values ('aa', 'This is ')
insert into #temp1 values ('aa', 'Thursday morning')
insert into #temp1 values ('bb', 'Friday morning')
insert into #temp1 values ('cc', 'This is ')
insert into #temp1 values ('cc', 'Saturday morning')

defiant · November 18th, 2003, 03:27 AM

As far as i know, there are two ways to accomplish this:
- using a cursor (i hate cursors). this works in SQL Server 2000 or 7, maybe even earlier;
- using a user-defined function (udf). This works in SQL Server 2000 only.

Here is the solution with the udf:

Code:

create function dbo.fnTmp (
@ColumnAVal nvarchar(50)
) returns nvarchar(100)
as
begin
    declare @RetVal nvarchar(100)

    select @RetVal = isnull(@RetVal, '') + ColumnB
    from tmp
    where ColumnA = @ColumnAVal

    return @RetVal
end

go

select ColumnA, dbo.fnTmp(ColumnA)
from tmp
group by ColumnA

Above, I'm using a 'normal' table called tmp for the data. I don't know if it will work with a temporary table (#temp). That's for you to figure out.

Hope this helps,

defiant.

Jeff Mason · November 18th, 2003, 07:35 AM

Note that the "string aggregation" being done in the UDF here is an 'undocumented feature' of SQL Server. It is undocumented because there is no way to guarantee the order in which the strings in the group are returned. This means you cannot always depend on the resultant string (e.g. for Group 'aa') being "This is Thursday morning" - it could just as easily be "Thursday morning This is". It might be one way on one server one day and the other way on a different server the next.

Having said that, the reality is that it is fairly predictable as long as the operation is "simple" - that is, there are no involved JOINS or ORDER BYs or other similar operations. In the straightforward case the order of concatenation will be in the order of the clustered index.

If you want to always guarantee the order of concatenation, you must use a cursor, I'm afraid.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

defiant · November 18th, 2003, 08:09 AM

Knowing this now, I recommend using a cursor.

defiant.

chrislepingwell · November 18th, 2003, 08:19 AM

Could you give me a quick explantion of why/how cursors fix the ordering, please.

PS disregard previous mailed reply, I meant to hit backspace and delete everything, not return to send it!

There are two secrets to success in this world:
1. Never tell everything you know

alyeng2000 · November 18th, 2003, 05:56 PM

instead of cursors
it is possible to put a third col with concatination order so order is done then concatenation like follows

create table temp1 (a char(10), b varchar(4000),c int)

insert into temp1 values ('aa', 'This is ',1)
insert into temp1 values ('aa', 'Thursday morning',2)
insert into temp1 values ('bb', 'Friday morning',1)
insert into temp1 values ('cc', 'This is ',2)
insert into temp1 values ('cc', 'Saturday morning',1)

create function fnTmp (
@ColumnAVal nvarchar(50)
) returns nvarchar(100)
as
begin
    declare @RetVal nvarchar(100)

    select @RetVal = isnull(@RetVal, '') + b
    from temp1
    where a = @ColumnAVal
    order by c --- here is the change

    return @RetVal
end

select a, fnTmp(a)
from temp1
group by a

Ahmed Ali
Software Developer

Jeff Mason · November 18th, 2003, 08:26 PM

Adding the ORDER BY clause will not (necessarily) work.

The reason is that an ORDER BY clause is the very last bit of processing which is done when a SELECT statement is executed. That is, the entire resultset as specified by the SELECT statement is constructed, and then it is sorted.

So, the string aggregation happens before the ORDER BY clause is even executed. There is no way to specify the order in which rows are processed "inside" the execution of the SELECT statement, and that is why this approach won't work. Of course, it may look like it works, and that is why this is somewhat of an insidious problem (and why it is 'officially' undocumented behavior).

alyeng2000 correctly points out that an ordering column (within the group) is needed, though. The OP's table design only lists two columns, thus there is no way to specify the ordering of the individual strings of the group's rows, even if you do use a cursor.

If such an ordering column existed, then defining a cursor would be along the lines of:

Code:

DECLARE mycursor CURSOR FOR
    SELECT ColumnA, ColumnB, OrderingCol
    FROM yourtable
    GROUP BY ColumnA, ColumnB, OrderingCol
    ORDER BY ColumnA, OrderingCol

You would then process this cursor sequentially, one row at a time (which is what cursors do), "watch" when the grouping column changes, and construct the aggregate by concatenating the string column when the grouping column is the same as the prior row, and resetting the variable to a zero length string when the grouping value does change. You also have to put the intermediate results somewhere, presumably into a temporary table, then SELECT that table as a resultset back to the client.

This whole process can be a bit of a pain, and probably not very efficient, since TSQL is not the most efficient procedural language, so this may very well be best handled at the client for all but the simplest cases...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

defiant · November 19th, 2003, 04:00 AM

I agree with Jeff Mason. If the string concatenation inside a group must be done in a certain order, then you a cursor with an ORDER BY clause is the only 100% working approach.

defiant.

msa · March 28th, 2007, 10:52 PM

hi everybody,ive got the same problem here.i am a newby in sql.
supposed i have this records

Table1
StudId StudentName
1 AB
1 BC
2 FG
3 ED
2 MN

Table 2
StudID
1
1
2
3
2

i need to have a condition where table2.StudID is equal to Table1.StudID then the output will look like this

StudID StudName
1 AB,BC
2 FG,MN
3 ED

hope to hear from anyone..thanks for the help in advance

Peso · March 28th, 2007, 11:47 PM

How can you tell which of the AA records to fetch first?