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
On Tue, 30 Oct 2007 11:08:37 -0700, candide_sh@yahoo.de wrote:
>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
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
>>>>>>>>>>>>>>>
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@yahoo.de wrote:
>
> 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
> >>>>>>>>>>>>>>>
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.
> 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]
Tony Rogerson wrote:
> 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)
Tony Rogerson (tonyrogerson@torver.net) writes:
>> 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@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx
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]
> 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]
On Thu, 1 Nov 2007 05:40:36 -0000, Tony Rogerson wrote:
>> 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.
Hi Tony,
Whether intuitive or not, it still is a kludge in my eyes. The phrase
FOR XML does somewhat imply that it is intended to produce, well, XML,
don't you think? And if you use that to produce straight-text non-XML
output, then I have no other word for it than "kludge".
Though I also agree with Erland that it's better than any of the
alternatives :)
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis