sql server (alternate): stored procedure to concatenate column value sin a single cell? -- sql server (alternate)

Re: stored procedure to concatenate column value sin a single cell?

dmarkle
1/7/2007 6:39:18 PM

You pretty much have to use a CURSOR to make this happen. (Though
there are some other tricks you can use, a cursor is your best option
here.) For optimal performance, use a FAST_FORWARD cursor.

-Dave
http://www.markleconsulting.com

[quoted text, click to view]

Re: stored procedure to concatenate column value sin a single cell?

Plamen Ratchev
1/8/2007 2:53:49 AM

Here is one way to accomplish what you need using a function. Be aware of
the limitation of 8000 characters for varchar. If you are using SQL Server
2005 you can use varchar(max) to expand beyond the 8000 character limit.

CREATE TABLE Test (
col1 INT,
col2 VARCHAR(10))

INSERT Test (col1, col2) VALUES(1, '1.1')
INSERT Test (col1, col2) VALUES(1, '1.2')
INSERT Test (col1, col2) VALUES(2, '2.1')
INSERT Test (col1, col2) VALUES(2, '2.2')
INSERT Test (col1, col2) VALUES(2, '2.3')

GO

CREATE FUNCTION Concatenate (@col1 INT)
RETURNS VARCHAR(8000)
BEGIN

DECLARE @sReturn VARCHAR(8000)
DECLARE @sDelimiter CHAR(3)

SET @sDelimiter = ' | '

SELECT @sReturn = COALESCE(@sReturn + @sDelimiter, '') + col2 FROM Test
WHERE col1 = @col1 ORDER BY col2

RETURN @sReturn
END

GO

SELECT col1,
dbo.Concatenate(col1) as col2
FROM Test
GROUP BY col1

DROP TABLE Test

DROP FUNCTION Concatenate
GO

Regards,

Plamen Ratchev
http://www.SQLStudio.com

[quoted text, click to view]

Re: stored procedure to concatenate column value sin a single cell?

Erland Sommarskog
1/8/2007 8:24:27 AM

Plamen Ratchev (Plamen@SQLStudio.com) writes:
[quoted text, click to view]

Also beware that this solution relies on undefined behaviour. This may
work - or it may not.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Re: stored procedure to concatenate column value sin a single cell?

Erland Sommarskog
1/8/2007 8:29:10 AM

parth (Parth.M@gmail.com) writes:
[quoted text, click to view]

Look up the recent thread "Script to combine multiple rows into 1 single
row", where I gave a suggestion to someone who also was doing data migration
and had the same requirement.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Re: stored procedure to concatenate column value sin a single cell?

Plamen Ratchev
1/9/2007 3:24:56 AM

Erland,

I believe you are referring to the behavior explained in this KB article:
http://support.microsoft.com/default.aspx/kb/287515

The way this query is written it follows exactly the workaround solution
explained in the article (to apply any function or expression to the SELECT
list columns rather than in the ORDER BY clause). According to the article
then the query will achieve the expected results.

This query was tested with both SQL Server 2000 and SQL Server 2005. I agree
it does not exactly comply with the ANSI specifications but seems SQL Server
handles that well.

Please correct me if I am missing the point.

I did look at the recent thread after you pointed out and the solution you
provided is in fact more standard and avoids any speculation about
unexpected behavior.

Regards,

Plamen Ratchev
http://www.SQLStudio.com

[quoted text, click to view]

Re: stored procedure to concatenate column value sin a single cell?

Erland Sommarskog
1/9/2007 9:08:55 AM

Plamen Ratchev (Plamen@SQLStudio.com) writes:
[quoted text, click to view]

But observe the first paragraph under CAUSE. The article then bends over
backwards to do it anyway, which contradicts the first paragraph. I prefer
to trust that first paragraph that says the correct behvaiour is undefined.

[quoted text, click to view]

Yes, but did you get expected results, because you should get them, or
was it mere chance? Compare the TOP 100 PERCENT .. ORDER BY in views that
many incorrectly relied on in SQL 2000, and then were bitten by in
SQL 2005.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Re: stored procedure to concatenate column value sin a single cell?

Plamen Ratchev
1/9/2007 1:39:51 PM

Yes, the wording is not very clear. I trusted the statement in the
workaround section as well as the samples provided below.

Thanks!

Plamen Ratchev
http://www.SQLStudio.com

[quoted text, click to view]

Re: stored procedure to concatenate column value sin a single cell?

vicky
1/11/2007 1:46:38 AM

Hi Plamen and Erland,
I believe and agree with the wording-- The correct behavior for an
aggregate concatenation query is undefined. at
http://support.microsoft.com/default.aspx/kb/287515 and query processor
builds different execution plan when expressions are applied to columns
in the query's ORDER BY clause....

Here is another solution(removes overhead of order by clause) to this
which I m thinking of

same TEST table I have taken for this

CREATE TABLE Test (
col1 INT,
col2 VARCHAR(max))

INSERT Test (col1, col2) VALUES(1, '1.1')
INSERT Test (col1, col2) VALUES(1, '1.2')
INSERT Test (col1, col2) VALUES(2, '2.1')
INSERT Test (col1, col2) VALUES(2, '2.2')
INSERT Test (col1, col2) VALUES(2, '2.3')

CREATE TABLE #tst (
col1 INT,
col2 VARCHAR(max))

insert into #tst
select distinct(col1),'''' as col2
from test

update t
set col2 = (select stuff((select ' | '+ col2 as 'data()'
from Test t1
where t1.col1 = t.col1
for xml path ('')),1,2,''))
from #tst t

select col1,col2
from #tst

would produce expected result :>

col1 col2
1 1.1 | 1.2
2 2.1 | 2.2 | 2.3

Thanks and regards,
Vikas
Database Developer

[quoted text, click to view]

Re: stored procedure to concatenate column value sin a single cell?

haslam007 NO[at]SPAM gmail.com
1/18/2007 9:10:58 AM

How would i do this in Crystal?

[quoted text, click to view]

[OT] Re: stored procedure to concatenate column value sin a single cell?

Ed Murphy
1/18/2007 11:48:54 AM

[quoted text, click to view]

(Don't top-post. Fixed.)

1) Group by col1

2) In the group header section, add a function "Initialize List":

Shared StringVar Col2List := "";
""

3) In the detail section, add a function "Accumulate List":

Shared StringVar Col2List;
if Col2List = ""
then Col2List := {col2}
else Col2List := Col2List + " | " + {col2};
""

and then suppress the section from printing.

4) In the group footer section, add a function "Print List":

Shared StringVar Col2List;

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server (alternate) group.