I have one text field called Qualifier which looks like this

Qualifier
a
b
c
d
e

I would like to produce a text string which would be the concatenation of
all fields, hence returning:

abcde

Is there a way to do this without using a cursor?

Thanks

Archer

http://www.aspfaq.com/2529




Show quote Hide quote

Since destroying First Normal Form (1NF) is a non-relational operation
and a violation of the basic principle of a tiered archirtecture which
says that you format data in the front end and not in the database.
Yes, you have to use a cursor. 

Or write good SQL code instead.

But he didn't say that he wanted to store the concactenated result in a
table, so normalization rules do not seem to have jurisdiction here. Would
the following query be a violation of 1NF ?

select FirstName + LastName as Fullname, PhoneNumber from Customers

Show quoteHide quote

declare @vcQualifier as varchar(8000)
select @vcQualifier  = ''
select @vcQualifier  = @vcQualifier  + Qualifier from MyTable


Show quoteHide quote

The results of the proprietary code are unpredictable.  This is not
good.

In what way is it "proprietary" and "unpredictable"? Most everything about
T-SQL is proprietary to SQL Server and this query will return the same
result each time it is run given the same set of rows. Right?

Show quoteHide quote

NO.

Order is not defined, and even if you use an ORDER BY statement, it is not
guaranteed.  I will argue with Joe that it is relatively and reliably
predictable, but the fact that it is not guaranteed certainly makes it a
questionable method.

A

If you do not use the "order by" clause, then you can not asure that it will
return the same result each time. If you use "order by" clause, then be
careful.

PRB: Execution Plan and Results of Aggregate Concatenation Queries Depend
Upon Expression Location
http://support.microsoft.com/default.aspx?scid=kb;en-us;287515


AMB


Show quoteHide quote

It seems to work fine.  Why do you think that it is unpredictable?

Archer

Show quoteHide quote

Never mind them, they're just miffed they didn't think of it first.    :-)

Show quoteHide quote

Normally, I wouldn't agree with Celko, but in this case he's right.  The
order of rows in a table is undefined.  Depending on the number of rows and
whether there is a clustered index on the table, the rows may be returned in
many different ways.  Generally, if there is a clustered index on the table,
a clustered index scan will scan through the table in the correct order, but
I wouldn't rely on it.  Add a column to indicate the order.

ORDER BY will work correctly if there is a column that indicates the correct
order.

Show quoteHide quote

http://groups-beta.google.com/group/microsoft.public.sqlserver.programming/browse_frm/thread/2d87fe75c32fe408/f758f03fd96c1452?q=aggregate+concatenation+author:Bob+Barrows&_done=%2Fgroups%3Fq%3Daggregate+concatenation+author:Bob+Barrows%26hl%3Den%26lr%3D%26c2coff%3D1%26rls%3DGGLD,GGLD:2003-36,GGLD:en%26sa%3DN%26tab%3Dwg%26&_doneTitle=Back+to+Search&&d#f758f03fd96c1452

bagman3rd wrote:

Although one of the links in the thread I cited would have led you to this,
I should have included it in my initial reply:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q287515

bagman3rd wrote:

I've been researching this while contemplating how to respond to JT's
assertion that we are issuing warnings because we were incapable of thinking
of his solution first (which doesn't seem to care, BTW, that if any value is
NULL then the entire result will be NULL).

The KB article is not necessarily applicable, as it seems to only indicate
that the ORDER BY behavior is undefined when an expression (even LOWER()) is
used in the ORDER BY clause.  My feeling is that there are many other
factors that can make this result unpredictable, such as the presence of a
clustered index and, more likely, the behavior of parallel scans on machines
with more than one processor in use.

But since Microsoft does not seem to have documented these possibilities, I
doubt our warnings will have any impact on those who run the query twice on
the same machine against the exact same set of data and say, "see, it works
the same every time!"

So, let them use it.  And then have to figure out a proper workaround anyway
if and when the symptom reveals itself.  Which may occur when the data
changes, the table structure and/or indexes change, when they move the code
to a different server, or even when they upgrade to a future version of SQL
Server with optimizer changes all over the place.

Is this your fancy long winded way of saying that bagman3rd should just use
a cursor?    :-)

Show quoteHide quote

That was never my intent.  Just noting caution, that is all.

Here is a set of fun examples, by the way.  The first one is based on a
query written by Umachandar.  The last one is based on an example courtesy
Steve Kass, and is the only one where you don't really have control over the
ordering (though I think it can be deemed predictable).



CREATE TABLE tbl
(
somecol VARCHAR(32),
id INT PRIMARY KEY CLUSTERED,
foo SMALLDATETIME
)
GO
SET NOCOUNT ON
INSERT tbl SELECT 'a',20005006, '20050101'
INSERT tbl SELECT 'b',2, '20051001'
INSERT tbl SELECT 'c',999, '20040601'
GO

DECLARE @Everything varchar( 8000 ) , @delim VARCHAR(1)
SET @delim = '|' -- could be , or | or CHAR(9)
UPDATE tbl
SET @Everything = COALESCE(@Everything + @delim + somecol, somecol)
SELECT 'Variable update' = @Everything

SELECT @Everything = NULL
SELECT @Everything = COALESCE(@Everything + @delim + somecol, somecol)
FROM tbl
SELECT 'No order by' = @Everything

SELECT @Everything = NULL
SELECT @Everything = COALESCE(@Everything + @delim + somecol, somecol)
FROM tbl ORDER BY somecol
SELECT 'Order by that column' = @Everything

SELECT @Everything = NULL
SELECT @Everything = COALESCE(@Everything + @delim + somecol, somecol)
FROM tbl ORDER BY id
SELECT 'Order by clustered PK' = @Everything

SELECT @Everything = NULL
SELECT @Everything = COALESCE(@Everything + @delim + somecol, somecol)
FROM tbl ORDER BY foo
SELECT 'Order by another column' = @Everything

SELECT 'Max/Case method' = LEFT(List, LEN(list)-LEN(@delim))
FROM
(
SELECT List =
  MAX(CASE WHEN rk =  1 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk =  2 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk =  3 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk =  4 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk =  5 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk =  6 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk =  7 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk =  8 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk =  9 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk = 10 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk = 11 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk = 12 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk = 13 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk = 14 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk = 15 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk = 16 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk = 17 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk = 18 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk = 19 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk = 20 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk = 21 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk = 22 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk = 23 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk = 24 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk = 25 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk = 26 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk = 27 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk = 28 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk = 29 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk = 30 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk = 31 THEN tag+@delim ELSE '' END)
+ MAX(CASE WHEN rk = 32 THEN tag+@delim ELSE '' END)
FROM (
  SELECT LTRIM(RTRIM(A.somecol)) AS tag,
         COUNT(DISTINCT B.somecol) AS rk
  FROM tbl A JOIN tbl B
  ON  A.somecol <= B.somecol
  GROUP BY A.somecol
) X ) y
GO

DROP TABLE tbl
GO

The Max/Case method is an interesting approach. It is one of the few
solutions I saw (the only one?) that is guaranteed to be correct.

Below is another approach that I think is guaranteed to be correct. It
uses both relational and procedural code. It probably brings out the
worst of both worlds :-)

But like the Max/Case method, it could be useful when many
concatenations are needed, because that is a situation where a
cursor/loop approach can become problematic. The approach below is not
limited to a maximum (of 32) values. However, it does assume unique
values.

CREATE TABLE #tbl
(
somecol VARCHAR(32),
id INT PRIMARY KEY CLUSTERED,
foo SMALLDATETIME
)

INSERT #tbl SELECT 'd',20005006, '20050101'
INSERT #tbl SELECT 'e',2, '20051001'
INSERT #tbl SELECT 'f',999, '20040601'
INSERT #tbl SELECT 'a',20005005, '20050101'
INSERT #tbl SELECT 'b',1, '20051001'
INSERT #tbl SELECT 'c',998, '20040601'

create table #tblA
(Everything varchar(8000) not null
,Rank       int
,Level      tinyint
,constraint PK_tblA primary key clustered (Rank,Level)
)
create table #tblB
(Everything varchar(8000) not null
,Rank       int
,Level      tinyint
,constraint PK_tblB primary key clustered (Rank,Level)
)

Declare @level tinyint
Set @level=0

insert into #tblA
SELECT LTRIM(RTRIM(A.somecol)),COUNT(*),@level
FROM #tbl A JOIN #tbl B
ON  A.somecol <= B.somecol
GROUP BY A.somecol
while @@rowcount>1
Begin
  DELETE FROM #tblA WHERE Level<@level
  Set @level=@level+1

  INSERT INTO #tblA
  SELECT COALESCE(T2.Everything+',','')+T1.Everything,T1.Rank,@level
  FROM      #tblA T1
  LEFT JOIN #tblA T2 ON T2.Rank = T1.Rank+POWER(2,@level-1)
  WHERE (T1.Rank%POWER(2,@level))=1
End
DELETE FROM #tblA WHERE Level<@level

select Everything from #tblA

drop table #tblA
drop table #tblB

DROP TABLE #tbl

So what the code above is doing, is use the temp tables to build a tree.
The statement outside the loop inserts the base values. Then in each
iteration in the loop, two "branches" are joined, reducing the remaining
number of rows to 50%. So if you had 200 values, then it would only take
8 such statements/iterations to create an ordered concatenation (because
2 ^ 8 = 256)

To the OP: if you need to concatenate just one set, then I would suggest
you use a cursor or loop, because that is easy to write and will
probably have good performance.

Gert-Jan

You make it sound like there are only two options.  Why can't the
concatenation be handled where it belongs, in the presentation tier?

You are assuming that he intends the string to be consumed by the
presentation tier. He didn't say what this string will be used for; perhaps
a hash code?

Show quoteHide quote

Of course, we can argue about this all day.  Or we can provide the options
and caveats to the user and he can decide.

I've run into an issue where using a UDF in the ORDER BY clause repeatedly
resets the variable to a ZLS when using this concatenation technique:
http://groups-beta.google.com/groups?as_umsgid=u4ceYT7LFHA.1***@TK2MSFTNGP09.phx.gbl

Of course that was on SP3 and we're up to SP4 now :)  Of course, since this
method is officially unsupported by MS, it's doubtful this little issue
would be fixed.

Show quoteHide quote

We're assuming the user requested the resulting list a specified order. If
the user doesn't care, then the Multiple Assignment Variable technique is
adequate.

Show quoteHide quote

bagman3rd said in the op he wants to return the following, which seems to be
in ascending order:

abcde

Show quoteHide quote

ok, that's reasonable.

Show quoteHide quote

Below is a revised version of my earlier post along with some DDL. It
insures that the members of the resulting string are sorted in the manner
you requested.

create table #MyTable
(
Qualifier char(1)
)

insert into #MyTable
select 'd'
union
select 'b'
union
select 'a'
union
select 'e'
union
select 'c'

declare @vcQualifier as varchar(8000)

select @vcQualifier  = ''
select
@vcQualifier  = @vcQualifier + Qualifier
from
(
select top 100 percent
  Qualifier
from
  #MyTable
order by
  Qualifier
) as x
order by
Qualifier

print @vcQualifier

drop table #MyTable


Show quoteHide quote

What do you get with this sample data?

insert into #MyTable
select 'd'
union
select 'b'
union
select 'a'
union
select 'e'
union
select 'c'
union
select NULL

Thanks

Show quoteHide quote

I get null, becuase null is included in this result set. So, put a check for
null in a where clause. I'm sure he's smart enough to extrapolate on the 2
minute samples we toss over the fence to meet his specific need.

Show quoteHide quote

Of course those extrapolations, like adding a COALESCE() here, an LTRIM()
there, a function in the ORDER BY clause, etc. will run him head-first into
the various issues that others have presented here.

Show quoteHide quote

@vcQualifier is NULL, which is what would be expected. If NULLs are a
concern, change the SELECT:

....
select
@vcQualifier  = @vcQualifier + Qualifier
from
.....

becomes

....
select
@vcQualifier  = @vcQualifier + LTRIM(COALESCE(Qualifier, ''))
from
....

this returns 'abcde'

Of course, you obviously knew all this :-)

Show quoteHide quote

;-)

I think someone else already posted a link to the MSKB demonstrating that
the placement of these functions can have an impact on the order of the
returned results.

Show quoteHide quote

DECLARE CURSOR ON EXEC... (Sybase does it, so why cant SQL Server?
Aaron, Itzik - about the MIN Function
IDENTITY_INSERT
Conditional select into on temp table
Inserting data to a table from other database. Help