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_shWell, 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:
Originally Posted by
Quote:
Originally Posted by
Quote:
Originally Posted by
>>>>
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:
Originally Posted by
Quote:
Originally Posted by
Quote:
Originally Posted by
>>>>
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:
Quote:
Originally Posted by
>
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:
Originally Posted by
Quote:
Originally Posted by
>>>>
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:
Originally Posted by
Quote:
Originally Posted by
>>>>
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.|||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
Quote:
Originally Posted by
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:
Quote:
Originally Posted by
Quote:
Originally Posted by
>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
No comments:
Post a Comment