By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,248 Members | 882 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,248 IT Pros & Developers. It's quick & easy.

stored procedure to concatenate column value sin a single cell?

P: n/a
Hi

I want to achieve the following transformation of data using a stored
procedure.
Source

col1 col2(varchar)
-------------------------
1 1.1
1 1.2
2 2.1
2 2.2
2 2.3
=================

Desired Result

col1 col2(varchar)
--------------------------
1 1.1 | 1.2
2 2.1 | 2.2 | 2.3
=====================
Thanks in advance. :)

- Parth

Jan 8 '07 #1
Share this Question
Share on Google+
12 Replies


P: n/a
parth wrote:
I want to achieve the following transformation of data using a stored
procedure.
Source

col1 col2(varchar)
-------------------------
1 1.1
1 1.2
2 2.1
2 2.2
2 2.3
=================

Desired Result

col1 col2(varchar)
--------------------------
1 1.1 | 1.2
2 2.1 | 2.2 | 2.3
=====================
Eww. Are you sure you can't do this in the application layer? (It
would be simple in Crystal Reports, for instance.)
Jan 8 '07 #2

P: n/a
Its for data migration... i have to use stored proc

Jan 8 '07 #3

P: n/a
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
parth wrote:
Hi

I want to achieve the following transformation of data using a stored
procedure.
Source

col1 col2(varchar)
-------------------------
1 1.1
1 1.2
2 2.1
2 2.2
2 2.3
=================

Desired Result

col1 col2(varchar)
--------------------------
1 1.1 | 1.2
2 2.1 | 2.2 | 2.3
=====================
Thanks in advance. :)

- Parth
Jan 8 '07 #4

P: n/a
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
"parth" <Pa*****@gmail.comwrote in message
news:11*********************@51g2000cwl.googlegrou ps.com...
Hi

I want to achieve the following transformation of data using a stored
procedure.
Source

col1 col2(varchar)
-------------------------
1 1.1
1 1.2
2 2.1
2 2.2
2 2.3
=================

Desired Result

col1 col2(varchar)
--------------------------
1 1.1 | 1.2
2 2.1 | 2.2 | 2.3
=====================
Thanks in advance. :)

- Parth

Jan 8 '07 #5

P: n/a
Plamen Ratchev (Pl****@SQLStudio.com) writes:
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.
...
SELECT @sReturn = COALESCE(@sReturn + @sDelimiter, '') + col2 FROM Test
WHERE col1 = @col1 ORDER BY col2

Also beware that this solution relies on undefined behaviour. This may
work - or it may not.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jan 8 '07 #6

P: n/a
parth (Pa*****@gmail.com) writes:
I want to achieve the following transformation of data using a stored
procedure.
Source

col1 col2(varchar)
-------------------------
1 1.1
1 1.2
2 2.1
2 2.2
2 2.3
=================

Desired Result

col1 col2(varchar)
--------------------------
1 1.1 | 1.2
2 2.1 | 2.2 | 2.3
=====================
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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jan 8 '07 #7

P: n/a
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
"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn*********************@127.0.0.1...
Plamen Ratchev (Pl****@SQLStudio.com) writes:
>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.
...
SELECT @sReturn = COALESCE(@sReturn + @sDelimiter, '') + col2 FROM Test
WHERE col1 = @col1 ORDER BY col2


Also beware that this solution relies on undefined behaviour. This may
work - or it may not.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Jan 9 '07 #8

P: n/a
Plamen Ratchev (Pl****@SQLStudio.com) writes:
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.
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.
This query was tested with both SQL Server 2000 and SQL Server 2005.
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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jan 9 '07 #9

P: n/a
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

"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
Plamen Ratchev (Pl****@SQLStudio.com) writes:
>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.

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.
>This query was tested with both SQL Server 2000 and SQL Server 2005.

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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Jan 9 '07 #10

P: n/a
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
Plamen Ratchev wrote:
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

"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...
Plamen Ratchev (Pl****@SQLStudio.com) writes:
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.
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.
This query was tested with both SQL Server 2000 and SQL Server 2005.
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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jan 11 '07 #11

P: n/a
How would i do this in Crystal?
Ed Murphy wrote:
parth wrote:
I want to achieve the following transformation of data using a stored
procedure.
Source

col1 col2(varchar)
-------------------------
1 1.1
1 1.2
2 2.1
2 2.2
2 2.3
=================

Desired Result

col1 col2(varchar)
--------------------------
1 1.1 | 1.2
2 2.1 | 2.2 | 2.3
=====================

Eww. Are you sure you can't do this in the application layer? (It
would be simple in Crystal Reports, for instance.)
Jan 18 '07 #12

P: n/a
ha*******@gmail.com wrote:
Ed Murphy wrote:
>parth wrote:
>>I want to achieve the following transformation of data using a stored
procedure.
Source

col1 col2(varchar)
-------------------------
1 1.1
1 1.2
2 2.1
2 2.2
2 2.3
=================

Desired Result

col1 col2(varchar)
--------------------------
1 1.1 | 1.2
2 2.1 | 2.2 | 2.3
=====================
>Eww. Are you sure you can't do this in the application layer? (It
would be simple in Crystal Reports, for instance.)
How would i do this in Crystal?
(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;
Col2List
Jan 18 '07 #13

This discussion thread is closed

Replies have been disabled for this discussion.