October 8, 2003 at 2:31 am
Is there a single sql statement that will produce the results below from the data below.
Note: I can achieve it by using a user defined function or a stored proc but I was wondering if there was a single sql statement that would achieve the results as well. Thanks.
Table Data:
Manufacturer-----Color
----------------------
Toyota----Blue
Honda----Red
Honda----Green
Ford----White
Ford----Red
Nissan----Black
Ford----Green
Results Desired:
Manufacturer----ColorsAvailable
---------------------------
Toyota----Blue
Honda----Red, Green
Ford----White, Red, Green
Nissan----Black
Edited by - eamonroche on 10/08/2003 02:32:52 AM
Edited by - eamonroche on 10/08/2003 02:35:11 AM
Edited by - eamonroche on 10/08/2003 02:35:37 AM
October 8, 2003 at 3:22 am
This is something that I myself have been trying for quite some time. I don't think there is a way out. The "result set" defies the RDBMS concepts.
October 8, 2003 at 4:30 am
If you are running SQL 2K then you can use a user defined function to concatenate the data:
CREATE function [dbo].fn_CreateString(id int)
RETURNS varchar(4000)
AS
BEGIN
declare @output_string varchar(4000)
set @output_string = ''
select @output_string = @output_string +
case
when @output_string > '' then ','
else ''
end
+ field_value
from <table_name>
where id = @id
return @output_string
END
You can include the UDF in a select statement:
select distinct manufacturer, dbo.fn_CreateString(id)
from <table>
group by manufacturer
You will need to tweak it to fit your data but this works well.
Jeremy
October 8, 2003 at 6:50 am
If you know the max no of colours (3 in your example) then, not elegant but....
select b.Manufacturer,b.c1+
coalesce(', '+b.c2,'')+
coalesce(', '+b.c3,'') as 'ColorsAvailable'
from (select a.Manufacturer,
max(case when a.Colorct = 1 then a.Color else '' end) as 'c1',
max(case when a.Colorct = 2 then a.Color else null end) as 'c2',
max(case when a.Colorct = 3 then a.Color else null end) as 'c3'
from (select t.Manufacturer,t.Color,
(select count(*)
from TableData
where Manufacturer = t.Manufacturer
and Color <= t.Color) as 'Colorct'
from TableData t) a
group by a.Manufacturer) b
Far away is close at hand in the images of elsewhere.
Anon.
October 9, 2003 at 11:16 am
It would be nice to have an aggregate "SUM" that worked on strings. I've needed it before. Perhaps a pivot table like approach? I use the trim and replace to put in the commas. The trim gets rid of that last ",".
create table #TableData (
Manufacturer varchar(20) NOT NULL,
Color varchar (20) NOT NULL
) ON [PRIMARY]
insert into #TableData (Manufacturer, Color)
select 'Toyota','Blue'
union all
select 'Toyota','Blue'
union all
select 'Honda','Red'
union all
select 'Honda','Red'
union all
select 'Honda','Green'
union all
select 'Ford','White'
union all
select 'Ford','Red'
union all
select 'Ford','Red'
union all
select 'Nissan','Black'
union all
select 'Ford','Green'
select a.Manufacturer,
left(replace(rtrim(
max(case when a.Color = 'Black' then a.Color + ' ' else '' end)
+ max(case when a.Color = 'Blue' then a.Color + ' ' else '' end)
+ max(case when a.Color = 'Green' then a.Color + ' ' else '' end)
+ max(case when a.Color = 'Orange' then a.Color + ' ' else '' end)
+ max(case when a.Color = 'Red' then a.Color + ' ' else '' end)
+ max(case when a.Color = 'White' then a.Color + ' ' else '' end)
+ max(case when a.Color = 'Yellow' then a.Color + ' ' else '' end)
), ' ', ', '), 50) as [ColorsAvailable]
from #TableData a
group by Manufacturer
order by Manufacturer
drop table #TableData
Randy
Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
October 9, 2003 at 11:23 am
More fun...
create table #TableData (
Manufacturer varchar(12) NOT NULL,
Color varchar (15) NOT NULL
) ON [PRIMARY]
insert into #TableData (Manufacturer, Color)
select 'Toyota','Blue'
union all
select 'Toyota','Blue'
union all
select 'Honda','Red'
union all
select 'Honda','Red'
union all
select 'Honda','Green'
union all
select 'Ford','White'
union all
select 'Ford','Red'
union all
select 'Ford','Red'
union all
select 'Nissan','Black'
union all
select 'Ford','Green'
union all
select 'Chevy','Purple'
select a.Manufacturer,
left(replace(rtrim(
max(case when a.Color = 'Black' then a.Color + ' ' else '' end)
+ max(case when a.Color = 'Blue' then a.Color + ' ' else '' end)
+ max(case when a.Color = 'Green' then a.Color + ' ' else '' end)
+ max(case when a.Color = 'Orange' then a.Color + ' ' else '' end)
+ max(case when a.Color = 'Red' then a.Color + ' ' else '' end)
+ max(case when a.Color = 'White' then a.Color + ' ' else '' end)
+ max(case when a.Color = 'Yellow' then a.Color + ' ' else '' end)
), ' ', ', '), 18) as [ColorsAvailable],
left(ltrim(str(count(distinct a.color)))
+ case when count(distinct a.color) = 1 then ' color is'
else ' colors are' end
+ ' available', 22) as [CountAvailable],
left(case when min(a.color) is null then 'no colors available'
when min(a.color) = max(a.color) then 'only ' + min(a.color)
+ ' is available'
else 'colors ' + min(a.color) + ' to ' + max(a.color)
+ ' are available'
end, 35) as [ColorRange]
from #TableData a
group by Manufacturer
order by Manufacturer
drop table #TableData
Results...
Manufacturer ColorsAvailable CountAvailable ColorRange
------------ ------------------ ---------------------- -----------------------------------
Chevy 1 color is available only Purple is available
Ford Green, Red, White 3 colors are available colors Green to White are available
Honda Green, Red 2 colors are available colors Green to Red are available
Nissan Black 1 color is available only Black is available
Toyota Blue 1 color is available only Blue is available
(5 row(s) affected)
Sorry about the formatting - any way to prevent the loss of formating spaces when posting?
Randy
Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply