Select out rows into one result - Similar Problem

  • Hello Forum Members - I have a similar problem that I can't figure out.

    I have a table, with the following format:

    item color

    361 BLACK

    361 BLUE

    361 GREEN

    361 RED.

    I'm trying to construct a query that yields for a given item

    BLACK|BLUE|GREEN|RED.

    I'm trying to avoid the use of cursors. Any Ideas?  Thanks in advance for your help.

  • I can't get my brain around it right now, but you may be able to do something with min or max and derived tables and maybe isnull, assuming you have a limited quantity of colors.

    something along the lines of

    select

       min(c1.color) + '|' +

       (select isnull(min(c2.color,'')) from table c2 where c2.color <> c1.color) +

       (select...) ...

    from table c1

    Note, this isn't presented as code that's going to work.  Maybe it will get you started or spark someone else????

    I gotta get back to my migration....

    Steve

  • I believe you are basically after the following. If you have a limited number of colors you can avoid the #temp table and dynamic SQL version and require only the single varable table approach.

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=132980

  •  i think u may be want to get that like this:

    declare @STR

    select @STR=@str+colour +'|' from youtablename order by id

  • Hi jgljgl,

    I've created a select that returns the desired result in 1 query. The only caveat is that you must know up front how many different colors there are, for you have to outer join the table to itsself as many times as there are colors. If you do not know this, I think there is no other way than to use dynamic sql and generate the outer join clauses.

    Here's the test code:

    create table test_tab

    (item  int

    ,color varchar(20)

    )

    go

    insert into test_tab (item, color) values (361, 'BLACK')

    insert into test_tab (item, color) values (361, 'BLUE')

    insert into test_tab (item, color) values (361, 'GREEN')

    insert into test_tab (item, color) values (361, 'RED')

    insert into test_tab (item, color) values (362, 'BLACK')

    insert into test_tab (item, color) values (362, 'BLUE')

    insert into test_tab (item, color) values (363, 'GREEN')

    insert into test_tab (item, color) values (363, 'RED')

    select nocolor.item

    ,      left(

           isnull(black.color + '|', '') +

           isnull(blue.color + '|', '') +

           isnull(green.color + '|', '') +

           isnull(red.color + '|', '')

           , len(

             isnull(black.color + '|', '') +

             isnull(blue.color + '|', '') +

             isnull(green.color + '|', '') +

             isnull(red.color + '|', '')

                ) - 1

           ) colors

    from   test_tab nocolor

           left outer join test_tab black

           on  nocolor.item = black.item

           and black.color = 'BLACK'

           left outer join test_tab blue

           on  nocolor.item = blue.item

           and blue.color = 'BLUE'

           left outer join test_tab green

           on  nocolor.item = green.item

           and green.color = 'GREEN'

           left outer join test_tab red

           on  nocolor.item = red.item

           and red.color = 'RED'

    where  nocolor.color =

          (select min(color)

           from   test_tab

           where  item = nocolor.item

          )

    drop table test_tab

    go

    Some explanation:

    First we need to make sure that we get a record for each item, regardless of the color. This is what the nocolor alias is for. Note that if you leave out all the outer joins, that the nocolor part will select one record for each present item. Next, we'll make a self outer join on item and one specific color for each present color. Outer join combined with the isnull calls make sure that an empty string is returned if that specific color isn't present for an item. Finally, we have to get rid of the trailing pipe character. That is where the left function comes in. Note that we can safely assume that the len(..) - 1 will never be smaller than 0 because only items that have at least one color are selected. Good luck with the query.

    Cheers,

    Henk

  • Depends on how you will use the result... if you need this summary of available colors in several queries, then the best solution could be a UDF:

    CREATE FUNCTION dbo.f_get_item_colors (@item INT)

    RETURNS VARCHAR(256)

    AS

    BEGIN

    DECLARE @colors VARCHAR(256)

    SELECT @colors = '' /*eliminating NULL value*/

    SELECT @colors = @colors + it.color + '/'

    FROM item_table it

    WHERE it.item = @item

    ORDER BY it.color /*to order colors alphabetically*/

    /*strip away the delimiter after last color*/

    IF @colors <> '' SELECT @colors = (LEFT(@colors,LEN(@colors)-1))

    RETURN @colors

    END

    Then you can simply call this function whenever you need to display all colors - hypothetical example:

    select it.item, it.item_name, dbo.f_get_item_colors(it.item) as colors_available, sum(ISNULL(o.amount),0) as amount_ordered

    from item_table it

    left join orders o on o.item = it.item

    group by it.item, it.item_name

    cheers, Vladan

  • I'm sure you'll agree the following code is simple and elegant. Hope it helps. Assuming there is a table COLORTABLE ( item int, color varchar(10) )

    declare @colorlist varchar(1000)

    select @colorlist  = isnull(@colorlist + '|','')  + color

    from COLORTABLE

    where item=361

    select @colorlist

  • I think that one option is to make use of temp tables, and if you reuse the result, persist the data in a global temp table rather than a local temp table

    1 table for each color, each table has the item id as a way to later join them all into the table structure you desire.

    Another option is to make use a function, but use the multistatement function, new to SQL 2000. The advantage is the use of table variables instead of temp tables.

    For me the choice would be based on how big the original table is.

    create procedure color_dance

    as

    Select item,color

    into ##color1

    from originaltable

    where color =

    Select item,color

    into ##color2

    from originaltable

    where color =

    Select item,color

    into ##color3

    from originaltable

    where color =

    Select item,color

    into ##color4

    from originaltable

    where color =

    --Now put it all together in to one happy table---

    Select ot.item

    ,c1.color

    ,c2.color

    ,c3.color

    ,c4.color

    from originaltable as ot

    inner join color1 as c1 on ot.item = c1.item

    inner join color2 as c2 on ot.item = c2.item

    inner join color3 as c3 on ot.item = c3.item

    inner join color4 as c4 on ot.item = c4.item

    order by ot.item asc

    Another option, if you create the table and query it all day long is to persist it in a regular table, so instead of the last step, do this:

    Select ot.item

    ,c1.color

    ,c2.color

    ,c3.color

    ,c4.color

    into itemcolors

    from originaltable as ot

    inner join color1 as c1 on ot.item = c1.item

    inner join color2 as c2 on ot.item = c2.item

    inner join color3 as c3 on ot.item = c3.item

    inner join color4 as c4 on ot.item = c4.item

    order by ot.item asc

    The above script, of course was written for temp tables, but could be adapted for the table variable in the multstatment valued functions as well.

    Yes, there is a bit of IO here, but it allows for separate columns, and can be reused in the blink of an eye as it is also a stored procedure. The weakness here is the fixed number of colors.

    Later.

    Michael

  • I think that one option is to make use of temp tables, and if you reuse the result, persist the data in a global temp table rather than a local temp table

    1 table for each color, each table has the item id as a way to later join them all into the table structure you desire.

    Another option is to make use a function, but use the multistatement function, new to SQL 2000. The advantage is the use of table variables instead of temp tables.

    For me the choice would be based on how big the original table is.

    create procedure color_dance

    as

    drop table ##color1

    drop table ##color2

    drop table ##color3

    drop table ##color4

    Select item,color

    into ##color1

    from originaltable

    where color =

    Select item,color

    into ##color2

    from originaltable

    where color =

    Select item,color

    into ##color3

    from originaltable

    where color =

    Select item,color

    into ##color4

    from originaltable

    where color =

    --Now put it all together in to one happy table---

    Select ot.item

    ,c1.color

    ,c2.color

    ,c3.color

    ,c4.color

    from originaltable as ot

    inner join color1 as c1 on ot.item = c1.item

    inner join color2 as c2 on ot.item = c2.item

    inner join color3 as c3 on ot.item = c3.item

    inner join color4 as c4 on ot.item = c4.item

    order by ot.item asc

    Another option, if you create the table and query it all day long is to persist it in a regular table, so instead of the last step, do this:

    Select ot.item

    ,c1.color

    ,c2.color

    ,c3.color

    ,c4.color

    into itemcolors

    from originaltable as ot

    inner join color1 as c1 on ot.item = c1.item

    inner join color2 as c2 on ot.item = c2.item

    inner join color3 as c3 on ot.item = c3.item

    inner join color4 as c4 on ot.item = c4.item

    order by ot.item asc

    The above script, of course was written for temp tables, but could be adapted for the table variable in the multstatment valued functions as well.

    Yes, there is a bit of IO here, but it allows for separate columns, and can be reused in the blink of an eye as it is also a stored procedure. The weakness here is the fixed number of colors.

    Later.

    Michael

  • How are the results being displayed to the user? Webform, VB App, Crystal Report, etc?

    Corie Curcillo
    MCT, MCDBA, MCSD

  • I am not sure if i understand the question right. But I like the power of "case" when it works with "group by". pls see below sql:

    create table test_tab

    (item  int

    ,color varchar(20)

    )

    insert into test_tab (item, color) values (361, 'BLACK')

    insert into test_tab (item, color) values (361, 'BLUE')

    insert into test_tab (item, color) values (361, 'GREEN')

    insert into test_tab (item, color) values (361, 'RED')

    insert into test_tab (item, color) values (362, 'BLACK')

    insert into test_tab (item, color) values (362, 'BLUE')

    insert into test_tab (item, color) values (363, 'GREEN')

    insert into test_tab (item, color) values (363, 'RED')

    select item, black =sum(case when color='black' then 1 else 0 end),

    blue =sum(case when color='blue' then 1 else 0 end),

    green =sum(case when color='green' then 1 else 0 end),

    red =sum(case when color='red' then 1 else 0 end)

    from test_tab

    group by item

  • Thanks to all who replied.  I really appreciate your help, time & energy devoted to my issue.

    I will go back and test all of these solutions and let you know the results.

    Regarding  "how will it be displayed", this is just for some backend data processing and is part of a much larger problem.

    Thanks

    jgl

  • Hi all,

    I posted a solution before, but I think this one is better. I expanded Vincent's solution with another case statement (Vincent is right, this is the statement of choice for all "row to column"-like actions). Again, you must know all the possible colors when writing the statement, or generate the select part of the statement for dynamic sql.

    Assuming there is a table with data as defined in my previous and Vincent's post, here's the select:

    select item

    ,      case

           when sum(case when color='BLACK' then 1 else 0 end) > 0

           then 'BLACK|' else '' end +

           case

           when sum(case when color='BLUE' then 1 else 0 end) > 0

           then 'BLUE|' else '' end +

           case

           when sum(case when color='GREED' then 1 else 0 end) > 0

           then 'GREEN|' else '' end +

           case

           when sum(case when color='RED' then 1 else 0 end) > 0

           then 'RED' else '' end colors

    from   test_tab

    group by item

    As you can see, I copied Vincent's statement and wrapped his color columns in yet another case statement and concatinated them (I also wrote the color names in uppercase, so the statement should work on case-sensitive databases as well). Note that the last color name is not pre- or postfixed with "|".

    Cheers,

    Henk

  • Hey guys. Not to jump to conclusions here, but it seems to me that we are trying to address presentation issues at the data layer. In most cases, I would consider it the responsibility of the client app that is receiving the record set to format it as a column delimited list if that is what the user (be it person or other system) wants to see.

    Corie Curcillo
    MCT, MCDBA, MCSD

  • --Create test table

    create table test_tab

    (item  int

    ,color varchar(20)

    )

    go

    --insert temp data

    insert into test_tab (item, color) values (361, 'BLACK')

    insert into test_tab (item, color) values (361, 'BLUE')

    insert into test_tab (item, color) values (361, 'GREEN')

    insert into test_tab (item, color) values (361, 'RED')

    insert into test_tab (item, color) values (362, 'BLACK')

    insert into test_tab (item, color) values (362, 'BLUE')

    insert into test_tab (item, color) values (363, 'GREEN')

    insert into test_tab (item, color) values (363, 'RED')

    -----------------------------------------------------------------------

    -- Query to get the string

    -----------------------------------------------------------------------

    declare @pResult varchar(2000)

    set @pResult = ''

    update test_tab

    set @pResult = @pResult + color + '|' where item=361

    select top 1 @pResult = @pResult from test_tab where

    item=361

    Select @pResult

    -----------------------------------------------------------------------

    -----------------------------------------------------------------------

    Hope this helps,

    Sanjay Gangwal

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply