get some rows into one column

#1

Hello,

can I do this via SQL:

example: tbltest has 5 rows:

col1
===
A
C
F
M
R

What I want is this:

result:
===============
A, C, F, M, R

Do I really have to go through the rows per SP? I could do this:

SELECT UDF(col1)
FROM tbltest

Ain't there a more simple way, maybe theres a T-SQL-command ?

thx
candide_sh

#2

On Tue, 30 Oct 2007 11:08:37 -0700, candide_sh (AT) yahoo (DOT) de wrote:

Quote:

Hello,

can I do this via SQL:

example: tbltest has 5 rows:

col1
===
A
C
F
M
R

What I want is this:

result:
===============
A, C, F, M, R

Do I really have to go through the rows per SP? I could do this:

Hi Candide,

On SQL Server 2000, you have to iterate over the rows. Best if you can
do it client-side! (Note that there are some kludges that appear to
work, but none of them is documented and most of them have been proven
to produce incorrect results in some -possibly rare- circumstances, so
you should not rely on them).

On SQL Server 2005, you can write your own CLR user-defined aggregate
for this. Or, if the data contains no characters that have special
meaning in XML (like <, >, and &), you can "abuse" some of the official
XML related functionality, like this:

CREATE TABLE #t
(id int NOT NULL PRIMARY KEY,
txt varchar(20) NOT NULL);
INSERT INTO #t (id, txt)
SELECT 1, 'First' UNION ALL
SELECT 2, 'word' UNION ALL
SELECT 3, 'of' UNION ALL
SELECT 4, 'the' UNION ALL
SELECT 5, 'list';
go

SELECT * FROM #t ORDER BY id;

SELECT STUFF((SELECT ', ' + txt AS "text()"
FROM #t
ORDER BY id
FOR XML PATH('')), 1, 2, '') AS "Concatenated text";

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

#3

Well, I knew I had read something about it but where? NOW I found it,
maybe from sqlservercentral.com, don't know the author, but it works
and is a straight solution.

thx for your answers
candide_sh

Quote:

It helps you in situaions whenever you wish to create commm seperated

values actually originating from multiple records. Say, your query
return three records in folloing patter:
Student_Name
=============
Ricky
Adam
Mathew

But, say you wish to have records in following patter:
Student_Name
============
Ricky, Adam, Mathew

That is how it works. Try it...I beleive it will help you a lot

create procedure sp_return_students
as
set nocount off

/* Declare variable which will store all student name */
Declare @StudentName varchar(8000)

/* Query that will return student names and at the same time
concatenate values. /*
select @StudentName = coalesce(@StudentName + ', ', '') + stu_name
from tbl_students

/* At last, you just have to define column name that will store values
*/
Select @StudentName As Student_Name

Quote:

#4

candide_sh,

There is one major problem with this solution: it might not work. It
might not work the next time you run it, after you have run a service
pack, after you have upgraded your server, after you have added another
join to your query, etc. And that is because there is no defined
behavior for this concatenation if multiple rows are involved.

So I would seriously advise you NOT to do it this way.

--
Gert-Jan

candide_sh (AT) yahoo (DOT) de wrote:

Quote:

Well, I knew I had read something about it but where? NOW I found it,
maybe from sqlservercentral.com, don't know the author, but it works
and is a straight solution.

thx for your answers
candide_sh

It helps you in situaions whenever you wish to create commm seperated
values actually originating from multiple records. Say, your query
return three records in folloing patter:
Student_Name
=============
Ricky
Adam
Mathew

But, say you wish to have records in following patter:
Student_Name
============
Ricky, Adam, Mathew

That is how it works. Try it...I beleive it will help you a lot

create procedure sp_return_students
as
set nocount off

/* Declare variable which will store all student name */
Declare @StudentName varchar(8000)

/* Query that will return student names and at the same time
concatenate values. /*
select @StudentName = coalesce(@StudentName + ', ', '') + stu_name
from tbl_students

/* At last, you just have to define column name that will store values
*/
Select @StudentName As Student_Name

#5

There are a lot of good reasons so many experienced SQL programmers
are warning you about this.

Why do you wish to destroy First Normal Form (1NF) with a concatenated
list structure? Normal forms are the foundation of RDBMS, after
all.

Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This is a more basic programming principle than just
SQL and RDBMS.

Get a reporting tool so you can do a sort for your lists and lay them
out on paper or a screen in a way that a human being can read them.
The kludges that have been posted are unpredictable, unsupported and
some are highly proprietary.

#6

Quote:

The kludges that have been posted are unpredictable, unsupported and
some are highly proprietary.

Let's make it clear, the use of text() and FOR XML is supported and fully
documented in books online, it is not a kludge; it's used by many folk to
get the functionality they need without having the added complexity and
programming requirement of a reporting tool.

Get a job in the real world; do us all a favour; your comments are novice at
best.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

#7

Tony Rogerson wrote:

Quote:

Get a job in the real world; do us all a favour; your comments are
novice at best.

That's the attitude Tony. Demonstrate your professionalism with
intentional insults. No point in adding to that old prejudice about
the English being cultured, civil, and well mannered.

Well at least you are making messes in your own bed now.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)

#8

Tony Rogerson (tonyrogerson (AT) torver (DOT) net) writes:

Quote:

The kludges that have been posted are unpredictable, unsupported and
some are highly proprietary.

Let's make it clear, the use of text() and FOR XML is supported and fully
documented in books online, it is not a kludge;

Nah, I think text() + FOR XML certainly has the flavour of a kludge over
it. The syntax is anything about intuitive, and if the data include
ampersands or angle brackets, there are a few surprising waiting for us.

It's a lot better than nothing at all, that's for sure.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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

#9

Crawl back under your rock Denial Again.

You've a cheek talking about people not being 'well mannered'.

I'm offensive to one person only - celko (well two if we are counting spats
where you spout dis information and rubbish about SQL Server) because he's
offensive to most people.

You are just offensive 'period' and people don't need to take my word for
it - you have a reputation that preceeds you....

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]

#10

Quote:

Nah, I think text() + FOR XML certainly has the flavour of a kludge over
it. The syntax is anything about intuitive, and if the data include
ampersands or angle brackets, there are a few surprising waiting for us.

I disagree, true - it's unintuitive to relational folk but isn't most XML.

This the XML side of the SQL Server data engine and until you start learning
how it works, syntax etc... then I guess anything to do with XML is a
kludge.

The ampersand and angle brackets are not suprises, books online tells us
what the behaviour is, ampersand and square brackets both have meaning
within the XML which is what text() is giving us.

Tony.

--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]