Return Results Horizontally

21 Oct 2005 12:47 AM

David

All

I have a table as illustrated with the DDL below and I would like to group
the records and return the results horizontally as per the desired results
below. Any assistance would be appreciated.

Thanks

CREATE TABLE colours
    (
        i INT,
        colour VARCHAR(10)
    )

INSERT INTO colours SELECT 1, 'Red'
INSERT INTO colours SELECT 1, 'Blue'
INSERT INTO colours SELECT 2, 'Green'
INSERT INTO colours SELECT 3, 'Green'
INSERT INTO colours SELECT 3, 'Orange'
INSERT INTO colours SELECT 3, 'Yellow'
INSERT INTO colours SELECT 3, 'Blue'

I would like to return the following results:

1 Red, Blue
2 Green
3 Green, Orange, Yellow, Blue

21 Oct 2005 2:05 AM

--CELKO--

>> I have a table as illustrated with the DDL below and I would like to group the records [sic] and return the results horizontally [sic] as per the desired results below. <<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. The second basic principle you
missed is that display is done in the front end and NEVER in the
backend.

You will wind up with Kludges, cursors and other crap.

Learn RDBMS instead.

21 Oct 2005 2:43 AM

Jerry Spivey

David,

Might look here:

http://www.rac4sql.net/
http://www.umachandar.com/technical/SQL6x70Scripts/Main44.htm
http://aspfaq.com/show.asp?id=2529

HTH

Jerry

Show quote

"David" <Da***@discussions.microsoft.com> wrote in message
news:80C77855-DC57-462E-9D2B-7B892F208620@microsoft.com...
> All
>
> I have a table as illustrated with the DDL below and I would like to group
> the records and return the results horizontally as per the desired results
> below. Any assistance would be appreciated.
>
> Thanks
>
> CREATE TABLE colours
> (
> i INT,
> colour VARCHAR(10)
> )
>
> INSERT INTO colours SELECT 1, 'Red'
> INSERT INTO colours SELECT 1, 'Blue'
> INSERT INTO colours SELECT 2, 'Green'
> INSERT INTO colours SELECT 3, 'Green'
> INSERT INTO colours SELECT 3, 'Orange'
> INSERT INTO colours SELECT 3, 'Yellow'
> INSERT INTO colours SELECT 3, 'Blue'
>
> I would like to return the following results:
>
> 1 Red, Blue
> 2 Green
> 3 Green, Orange, Yellow, Blue
>
>

21 Oct 2005 7:09 AM

sudarshan selvaraja

Try this out......u will get the results expected

create function fn_string(@i int)
returns varchar(50)
as
begin
    declare @temp varchar(50)
    set @temp = ''
    select @temp = @temp + colour + ', ' from colours where i=@i
    select     @temp = substring(@temp,1,len(@temp)-1)
    return @temp
end
go

select
    i,
    dbo.fn_string(i) as colours
from colours
group by i

Regards
sudarshan selvaraja

Show quote

"David" wrote:

> All
>
> I have a table as illustrated with the DDL below and I would like to group
> the records and return the results horizontally as per the desired results
> below. Any assistance would be appreciated.
>
> Thanks
>
> CREATE TABLE colours
>     (
>         i INT,
>         colour VARCHAR(10)
>     )
>
> INSERT INTO colours SELECT 1, 'Red'
> INSERT INTO colours SELECT 1, 'Blue'
> INSERT INTO colours SELECT 2, 'Green'
> INSERT INTO colours SELECT 3, 'Green'
> INSERT INTO colours SELECT 3, 'Orange'
> INSERT INTO colours SELECT 3, 'Yellow'
> INSERT INTO colours SELECT 3, 'Blue'
>
> I would like to return the following results:
>
> 1 Red, Blue
> 2 Green
> 3 Green, Orange, Yellow, Blue
>
>

21 Oct 2005 12:25 PM

sudarshan selvaraja

correction..... check for isnull for the field colour.....

create function fn_string(@i int)
returns varchar(50)
as
begin
    declare @temp varchar(50)
    set @temp = ''
    select @temp = @temp + isnull(colour,'Null') + ', ' from colours where i=@i
    select     @temp = substring(@temp,1,len(@temp)-1)
    return @temp
end
go

select
    i,
    dbo.fn_string(i) as colours
from colours
group by i

Regards
sudarshan selvaraja

Show quote

"David" wrote:

> All
>
> I have a table as illustrated with the DDL below and I would like to group
> the records and return the results horizontally as per the desired results
> below. Any assistance would be appreciated.
>
> Thanks
>
> CREATE TABLE colours
>     (
>         i INT,
>         colour VARCHAR(10)
>     )
>
> INSERT INTO colours SELECT 1, 'Red'
> INSERT INTO colours SELECT 1, 'Blue'
> INSERT INTO colours SELECT 2, 'Green'
> INSERT INTO colours SELECT 3, 'Green'
> INSERT INTO colours SELECT 3, 'Orange'
> INSERT INTO colours SELECT 3, 'Yellow'
> INSERT INTO colours SELECT 3, 'Blue'
>
> I would like to return the following results:
>
> 1 Red, Blue
> 2 Green
> 3 Green, Orange, Yellow, Blue
>
>