August 25, 2004 at 3:31 pm
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.
August 25, 2004 at 4:24 pm
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
August 25, 2004 at 5:54 pm
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://qa.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=132980
August 26, 2004 at 2:17 am
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
August 26, 2004 at 2:36 am
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
August 26, 2004 at 6:46 am
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
August 26, 2004 at 6:59 am
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
August 26, 2004 at 7:03 am
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
August 26, 2004 at 8:04 am
How are the results being displayed to the user? Webform, VB App, Crystal Report, etc?
Corie Curcillo
MCT, MCDBA, MCSD
August 26, 2004 at 10:22 am
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
August 26, 2004 at 11:35 am
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
August 27, 2004 at 1:44 am
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
August 27, 2004 at 8:48 am
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
August 27, 2004 at 9:59 pm
--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