I built this UDF to get a comma-separated list from a bunch of records
with the same key, maybe you can adapt something from it.
create function DepartmentApproverList(@deptin decimal(6,0) )
returns varchar(3000)
language sql
reads sql data
deterministic
begin
declare @max$ decimal(9,2) default null;
declare @last$ decimal(9,2) default 0;
declare csv varchar(1000) default null;
set @max$ = (select max(APPAMT)from GGIDTA.DAP DA, GGIDTA.APP A where
DA.DAPAP = A.APPID and DA.DAPDPT=@deptin);
while @last$ < @max$
do
set (@last$, csv) = (select AP.APPAMT,
concat(coalesce(concat(csv,','),''), DAP.DAPAP) csv
from GGIDTA.DAP DA, GGIDTA.APP A where DA.DAPAP = A.APPID and
DA.DAPDPT=@deptin
and A.APPAMT = (select min(AP.APPAMT) from GGIDTA.DAP DA, GGIDTA.APP A
where DA.DAPAP = A.APPID and DA.DAPDPT=@deptin and A.APPAMT>@last$));
end while;
return csv;
end;
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Naughton
Sent: Wednesday, April 07, 2010 9:17 AM
To: Midrange Systems Technical Discussion
Subject: Re: SQL Question: Combine Several Rows Into One Column?
Thanks to everyone who suggested pivot tables, but I'm afraid they won't
help me with this. The problem is that the values to be concatenated are
notes -- basically, we have a notes table with each note line being one
row, and the user wants to see all the notes going horizontally across
the page. So a better example of what they want me to do is, given:
PO# Note
1 Ship the swimsuits
1 to facilty in Tampa
1 and the skis
1 to our Calgary warehouse
2 Confirmed by Irma
2 this will ship Thursday
the user wants to see this:
PO# Note
1 Ship the swimsuits to facilty in Tampa and the skis to our
Calgary warehouse
2 Confirmed by Irma this will ship Thursday
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> writes:
You might be able to write a UDF that would do it....
You can do a pivot...
Sub Type A B C D P Q
Type
1 X X X X
2 X X
Then maybe run the pivoted table through some field concatenations...
But since the i doesn't have a built-in pivot function, you have to
have a predefined set of values in sub-set to pivot on.
HTH,
Charles
Mike Naughton
Senior Programmer/Analyst
Judd Wire, Inc.
124 Turnpike Road
Turners Falls, MA 01376
413-676-3144
Internal: x 444
mnaughton@xxxxxxxxxxxx
****************************************
NOTICE: This e-mail and any files transmitted with it are confidential
and solely for the use of the intended recipient. If you are not the
intended recipient or the person responsible for delivering to the
intended recipient, be advised that any use is strictly prohibited. If
you have received this e-mail in error, please notify us immediately by
replying to it and then delete it from your computer.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
