single sql statement needed

  • 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

  • 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.

  • 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

  • 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.

  • 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

    RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

  • 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?

    RandyHelpdesk: 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