returning multiple values in a subquery

I have a report that I've looked at with regards to a crosstab query -
which doesn't really give me what I need.
I have to produce a report where I provide some customer information,
and the channels in which they purchase items from us. A customer can
purchase items under several different channels, and what I need to do
is show a list of them.
The desired report would be something like:
Cust No Cust Name BA01 BA02 BA03
12345 CUST01 WF WA,WF WF
12333 CUST02 DL WA DL
12205 CUST03 N/A WA N/A
66412 CUST04 CH CH CH
What I was doing before (trying to get dollar totals in those columns) I
found I didn't have to do. However, I do have to return multiple values
into those columns, and thought I could do it with a subquery. Error
messages have led me to find I cannot - at least now how I was figuring.
The rows are just a straight select, and the columns BA01, BA02, BA03
are where I have the subselects. It's select distr_channel from
source.table where [all required fields match each other] group by
distr_channel). It's in a case statement to put in the distr_channel or
an N/A when there's no match.
How can I return these multiple values into columns BA01, BA02 and BA03
when necessary?
BCEnjoy!
create table Customer (customerno int identity(1, 1), customername
varchar(20))
create table Attributes1 (customerno int, attribute varchar(10))
create table Attributes2 (customerno int, attribute varchar(10))
insert into customer (customername) values('Customer A')
insert into customer (customername) values('Customer B')
insert into customer (customername) values('Customer C')
insert into customer (customername) values('Customer D')
insert into attributes1 (customerno, attribute) values(1, 'WF')
insert into attributes1 (customerno, attribute) values(1, 'DL')
insert into attributes1 (customerno, attribute) values(2, 'WF')
insert into attributes1 (customerno, attribute) values(3, 'WF')
insert into attributes1 (customerno, attribute) values(5, 'DL')
insert into attributes1 (customerno, attribute) values(5, 'WF')
insert into attributes2 (customerno, attribute) values(1, 'DL')
insert into attributes2 (customerno, attribute) values(1, 'WF')
insert into attributes2 (customerno, attribute) values(2, 'DL')
insert into attributes2 (customerno, attribute) values(3, 'DL')
insert into attributes2 (customerno, attribute) values(4, 'DL')
insert into attributes2 (customerno, attribute) values(4, 'WF')
if object_id('dbo.GetAttributesList') > 0
drop function dbo.AttributesList
go
create function dbo.AttributesList (@.customerno int, @.attr int)
returns varchar(1000)
as
begin
declare @.sOut varchar(1000)
set @.sOut = ''
if @.attr = 1
begin
-- attribute table 1
select @.sOut = @.sOut + ', ' + a.attribute
from attributes1 a
where a.customerno = @.customerno
end
if @.attr = 2
begin
-- attribute table 2
-- attribute table 1
select @.sOut = @.sOut + ', ' + a.attribute
from attributes2 a
where a.customerno = @.customerno
end
if len(@.sOut) > 2
set @.sOut = substring(@.sOut, 3, len(@.sOut) - 2)
return @.sOut
end
go
select c.customername, dbo.AttributesList(c.customerno, 1),
dbo.AttributesList(c.customerno, 2)
from customer c
go
"Blasting Cap" wrote:

> I have a report that I've looked at with regards to a crosstab query -
> which doesn't really give me what I need.
> I have to produce a report where I provide some customer information,
> and the channels in which they purchase items from us. A customer can
> purchase items under several different channels, and what I need to do
> is show a list of them.
> The desired report would be something like:
> Cust No Cust Name BA01 BA02 BA03
> 12345 CUST01 WF WA,WF WF
> 12333 CUST02 DL WA DL
> 12205 CUST03 N/A WA N/A
> 66412 CUST04 CH CH CH
>
> What I was doing before (trying to get dollar totals in those columns) I
> found I didn't have to do. However, I do have to return multiple values
> into those columns, and thought I could do it with a subquery. Error
> messages have led me to find I cannot - at least now how I was figuring.
> The rows are just a straight select, and the columns BA01, BA02, BA03
> are where I have the subselects. It's select distr_channel from
> source.table where [all required fields match each other] group by
> distr_channel). It's in a case statement to put in the distr_channel or
> an N/A when there's no match.
> How can I return these multiple values into columns BA01, BA02 and BA03
> when necessary?
> BC
>|||Cris:
Thanks for the help. It's so simple to see what it does.
I'm adapting it for my application now.
One question - and it may be that I'm just missing something. My
customer number is a varchar field (it can have letters or numbers in
it), and so is the attribute field.
In the function, I've changed both the customerno and attr to a varchar,
created the attribute tables based on whether they were in busunit01
02 or whatever, and call it with a customer I know is in the fa_channels
table (it has 2 entries a CH and PR).
I'm calling it (testing) like this:
select c.cust_no, c.cust_name, fa = dbo.AttributesList('R0461275', '1'),
am = dbo.AttributesList(c.cust_no, '2')
from sales_customer_list c
where c.cust_no = 'R0461275'
group by
c.cust_no, c.cust_name
The result of it gives a custno & cust_name, but the fa from above
returns blank.
Only when I change the statement below from:
where x.cust_no = @.customerno
to
where x.cust_no = 'R0461275'
will it return the 2 channels it should.
The only thing I know is that the cust_no in the sales_customer_list is
a char(10), and in my fa_channels table it's varchar(10).
Am I missing something?
BC
create function dbo.AttributesList (@.customerno varchar, @.attr varchar)
returns varchar(1000)
as
begin
declare @.sOut varchar(1000)
set @.sOut = ''
if @.attr = '1'
begin
-- attribute table 1
select @.sOut = @.sOut + ', ' + x.distr_channel
from fa_channels x
where x.cust_no = @.customerno
end
if @.attr = '2'
begin
-- attribute table 2
-- attribute table 2
select @.sOut = @.sOut + ', ' + y.distr_channel
from am_channels y
where y.cust_no = @.customerno
end
Cris_Benge wrote:
> Enjoy!
> create table Customer (customerno int identity(1, 1), customername
> varchar(20))
> create table Attributes1 (customerno int, attribute varchar(10))
> create table Attributes2 (customerno int, attribute varchar(10))
> insert into customer (customername) values('Customer A')
> insert into customer (customername) values('Customer B')
> insert into customer (customername) values('Customer C')
> insert into customer (customername) values('Customer D')
> insert into attributes1 (customerno, attribute) values(1, 'WF')
> insert into attributes1 (customerno, attribute) values(1, 'DL')
> insert into attributes1 (customerno, attribute) values(2, 'WF')
> insert into attributes1 (customerno, attribute) values(3, 'WF')
> insert into attributes1 (customerno, attribute) values(5, 'DL')
> insert into attributes1 (customerno, attribute) values(5, 'WF')
> insert into attributes2 (customerno, attribute) values(1, 'DL')
> insert into attributes2 (customerno, attribute) values(1, 'WF')
> insert into attributes2 (customerno, attribute) values(2, 'DL')
> insert into attributes2 (customerno, attribute) values(3, 'DL')
> insert into attributes2 (customerno, attribute) values(4, 'DL')
> insert into attributes2 (customerno, attribute) values(4, 'WF')
> if object_id('dbo.GetAttributesList') > 0
> drop function dbo.AttributesList
> go
> create function dbo.AttributesList (@.customerno int, @.attr int)
> returns varchar(1000)
> as
> begin
> declare @.sOut varchar(1000)
> set @.sOut = ''
> if @.attr = 1
> begin
> -- attribute table 1
> select @.sOut = @.sOut + ', ' + a.attribute
> from attributes1 a
> where a.customerno = @.customerno
> end
> if @.attr = 2
> begin
> -- attribute table 2
> -- attribute table 1
> select @.sOut = @.sOut + ', ' + a.attribute
> from attributes2 a
> where a.customerno = @.customerno
> end
> if len(@.sOut) > 2
> set @.sOut = substring(@.sOut, 3, len(@.sOut) - 2)
> return @.sOut
> end
> go
> select c.customername, dbo.AttributesList(c.customerno, 1),
> dbo.AttributesList(c.customerno, 2)
> from customer c
> go
>
> "Blasting Cap" wrote:
>|||Never mind!!!
I tried one other thing I hadn't before - I gave the varchar in the
function the same length as the input, and it worked!!
Thanks again for the very simple solution to my problem.
BC
Blasting Cap wrote:
> Cris:
> Thanks for the help. It's so simple to see what it does.
> I'm adapting it for my application now.
> One question - and it may be that I'm just missing something. My
> customer number is a varchar field (it can have letters or numbers in
> it), and so is the attribute field.
> In the function, I've changed both the customerno and attr to a varchar,
> created the attribute tables based on whether they were in busunit01 02
> or whatever, and call it with a customer I know is in the fa_channels
> table (it has 2 entries a CH and PR).
> I'm calling it (testing) like this:
> select c.cust_no, c.cust_name, fa = dbo.AttributesList('R0461275',
> '1'), am = dbo.AttributesList(c.cust_no, '2')
> from sales_customer_list c
> where c.cust_no = 'R0461275'
> group by
> c.cust_no, c.cust_name
>
> The result of it gives a custno & cust_name, but the fa from above
> returns blank.
> Only when I change the statement below from:
> where x.cust_no = @.customerno
> to
> where x.cust_no = 'R0461275'
> will it return the 2 channels it should.
> The only thing I know is that the cust_no in the sales_customer_list is
> a char(10), and in my fa_channels table it's varchar(10).
> Am I missing something?
> BC
> create function dbo.AttributesList (@.customerno varchar, @.attr varchar)
> returns varchar(1000)
> as
> begin
> declare @.sOut varchar(1000)
> set @.sOut = ''
> if @.attr = '1'
> begin
> -- attribute table 1
> select @.sOut = @.sOut + ', ' + x.distr_channel
> from fa_channels x
> where x.cust_no = @.customerno
> end
> if @.attr = '2'
> begin
> -- attribute table 2
> -- attribute table 2
> select @.sOut = @.sOut + ', ' + y.distr_channel
> from am_channels y
> where y.cust_no = @.customerno
> end
> Cris_Benge wrote:
>