SUM() on it.
Is there a way to do this for a varchar type so that all the values are conc
atenated
and separated by a comma for example?
And it needs to be a single select statement. Is this possible?
Jiho Han
Senior Software Engineer
Infinity Info Systems
The Sales Technology Experts
Tel: 212.563.4400 x216
Fax: 212.760.0540
jhan@.infinityinfo.com
www.infinityinfo.com> Is there a way to do this for a varchar type so that all the values are
> concatenated and separated by a comma for example?
http://www.aspfaq.com/2529|||Thanks but none of those will work for me. It's a shame that SQL standard
doesn't have a aggregate function for something like this.
It's required often enough and it would probably be so simple to do.
If we can,
SELECT PRODUCTNAME, SUM(PRICE)
FROM SALESPRODUCT
GROUP BY PRODUCTNAME
why can't we have,
SELECT PRODUCTFAMILY, CONCAT(PRODUCTNAME, ',')
FROM SALESPRODUCT
GROUP BY PRODUCTFAMILY
I mean is that so hard?
> http://www.aspfaq.com/2529
>|||> Thanks but none of those will work for me.
Can you be more specific?
> I mean is that so hard?
The SQL Server team will have to answer that.
A question for you: Is it so hard to do this outside the database? The
result is being used outside of the database, isn't it?|||>> I mean is that so hard?
Unlike summation, concatenation requires some specific order of the
constituent items to form the csv list. Since any subset of rows in a table
are sets, they do not have any inherent order associated with it. So asking
the DBMS to provide you with an ordered list when no order exists, is
meaningless.
The workarounds involve using SQL a ordered resultset & then concatenting
the values. Some of them can be found at:
http://groups.google.com/group/micr...3e?dmode=source
With SQL 2005 you will have some more options in generating such lists,
though in most cases as Aaron mentioned, retrieving the resultset to the
client and formatting it there might be a better option.
Anith|||Ummm... wrong poster!
Anith|||I am programming against a third party OLE DB Provider such that:
- I cannot create a UDF - which would be easier.
- No Case statements
- No Declares nor multiple statements
Basically it needs to be a standard ANSI SQL and a single statement.
It's not hard to do it outside the db. And I've already done it in the pres
entation
layer. But it is more lines of code doing what seems to be a mundane task.
> Can you be more specific?
>
> The SQL Server team will have to answer that.
> A question for you: Is it so hard to do this outside the database?
> The result is being used outside of the database, isn't it?
>|||Thanks for the link. I've seen some of that. Unfortunately I'm still on
SQL 2000.
I don't think I understand your statement regarding concatenation requiring
a specific order. Why is that?
I said nothing about the order of the result set. In fact, even if it came
in no particular order, it would be ok.
But even if I needed them in a certain order, once I get a single recordset
that contain this concatenated column, it'd be a few lines of coding that
can sort the particular column in the recordset. vs. having to parse out
the rows to concatenate everything and sorting it.
> Unlike summation, concatenation requires some specific order of the
> constituent items to form the csv list. Since any subset of rows in a
> table are sets, they do not have any inherent order associated with
> it. So asking the DBMS to provide you with an ordered list when no
> order exists, is meaningless.
> The workarounds involve using SQL a ordered resultset & then
> concatenting
> the values. Some of them can be found at:
> http://groups.google.com/group/micr...er.programming/
> msg/2d85bf366dd9e73e?dmode=source
> With SQL 2005 you will have some more options in generating such
> lists, though in most cases as Aaron mentioned, retrieving the
> resultset to the client and formatting it there might be a better
> option.
>|||> Basically it needs to be a standard ANSI SQL and a single statement.
If you can't create a UDF then I'm afraid you're out of luck. This is like
saying you need a car, and you know that cars contain many parts, but you
want a car made of only a single part. Not going to happen.
> It's not hard to do it outside the db. And I've already done it in the
> presentation layer. But it is more lines of code doing what seems to be a
> mundane task.
Yep. Driving to work every morning is a mundane task too. Can't wait until
the producers of Star Trek reveal their patent-protected "beam-me-up"
technology. Until then, if I want to get to work, I still have to use the
old fashioned automobile.|||> I said nothing about the order of the result set. In fact, even if it
> came in no particular order, it would be ok.
> But even if I needed them in a certain order, once I get a single
> recordset that contain this concatenated column, it'd be a few lines of
> coding that can sort the particular column in the recordset. vs. having to
> parse out the rows to concatenate everything and sorting it.
I'm
. There is ordering of the result, e.g. if you have:1 aaron,bob,frank
2 tommy,frank,george
3 frank,bob,aaron
You'd want them listed alphabetically based on the first member in each set,
e.g.
1 aaron,bob,frank
3 frank,bob,aaron
2 tommy,frank,george
What I believe Anith is talking about is ordering of each "column", e.g. an
ordered concatenation would produce this slightly different set:
1 aaron,bob,frank
2 frank,george,tommy
3 aaron,bob,frank
Which cannot be guaranteed by SQL Server, and even when it does work, it
will have two side effects (which may or may not be desirable):
(a) it will create "order doesn't matter" duplicates (1 and 3 are now the
same)
(b) it will change the alphabetical ordering, now 1 or 3 could be first...sql
No comments:
Post a Comment