Re: Difficult Query: is this possible in SQL?
Date: Mon, 5 Jul 2004 15:59:30 -0700
Message-ID: <TOGdnVYREfTFQHTdRVn-tw_at_speakeasy.net>
There's a technique of taking a max of two values concatenated then taking a
substring, which avoids the join needed in the below techniques.
Try this:
select color = substring(max(cast(level as char(1))+color),2,10)
,length = cast(substring(max(cast(level as char(1))+cast(length as
char(9))),2,9) as integer)
-aaron
"Richard" <rromley_at_optonline.net> wrote in message
news:bfbb57f7.0407050932.4cc43ea7_at_posting.google.com...
Hi Robert,
Here are 2 more solutions...
create table Foobar
primary key clustered
insert Foobar
( level, color, length, width, hgt )
select
(select max(level) from Foobar where color is not null)) as color ,
(select max(level) from Foobar where length is not null)) as length ,
(select max(level) from Foobar where width is not null)) as width ,
(select max(level) from Foobar where hgt is not null)) as hgt
,width = cast(substring(max(cast(level as char(1))+cast(width as
char(9))),2,9) as integer)
,hgt = cast(substring(max(cast(level as char(1))+cast(hgt as char(9))),2,9)
as integer)
from Foobar
(
level int NOT NULL , -- pk
color varchar(10) NULL ,
length int NULL ,
width int NULL ,
hgt int NULL ,
( level )
)
select 1,'RED',8,10,12 UNION ALL
select 2,NULL,NULL,NULL,20 UNION ALL
select 3,NULL,9,82,25 UNION ALL
select 4,'BLUE',NULL,67,NULL UNION ALL
select 5,'GRAY',NULL,NULL,NULL
(select color from Foobar where level =
(select length from Foobar where level =
(select width from Foobar where level =
(select hgt from Foobar where level =
select max(case when f.level = t.col then f.color end) as color ,
max(case when f.level = t.lth then f.length end) as length ,
max(case when f.level = t.wth then f.width end) as width ,
max(case when f.level = t.hgt then f.hgt end) as hgt
from (select max(case when color IS NOT NULL then level end),
max(case when length IS NOT NULL then level end),
max(case when width IS NOT NULL then level end),
max(case when hgt IS NOT NULL then level end)
from Foobar) as t(col,lth,wth,hgt), Foobar as f
Richard
robertbrown1971_at_yahoo.com (Robert Brown) wrote in message news:<240a4d09.0407021517.1ee87cdb_at_posting.google.com>...
> suppose I have the following table: > > CREATE TABLE (int level, color varchar, length int, width int, height > int) > > It has the following rows > > 1, "RED", 8, 10, 12 > 2, NULL, NULL, NULL, 20 > 3, NULL, 9, 82, 25 > 4, "BLUE", NULL, 67, NULL > 5, "GRAY", NULL NULL, NULL > > I want to write a query that will return me a view collapsed from > "bottom-to-top" in order of level (level 1 is top, level 5 is bottom) > > So I want a query that will return > > GRAY, 9, 67, 25 > > The principle is that looking from the bottom level up in each column > we first see GRAY for color, 9 for length, 67 for width, 25 for > height. In other words, any non-NULL row in a lower level overrides > the value set at a higher level. > > Is this possible in SQL without using stored procedures? > > > > Thanks! > > > - Robert
robertbrown1971_at_yahoo.com (Robert Brown) wrote in message news:<240a4d09.0407021517.1ee87cdb_at_posting.google.com>...
> suppose I have the following table: > > CREATE TABLE (int level, color varchar, length int, width int, height > int) > > It has the following rows > > 1, "RED", 8, 10, 12 > 2, NULL, NULL, NULL, 20 > 3, NULL, 9, 82, 25 > 4, "BLUE", NULL, 67, NULL > 5, "GRAY", NULL NULL, NULL > > I want to write a query that will return me a view collapsed from > "bottom-to-top" in order of level (level 1 is top, level 5 is bottom) > > So I want a query that will return > > GRAY, 9, 67, 25 > > The principle is that looking from the bottom level up in each column > we first see GRAY for color, 9 for length, 67 for width, 25 for > height. In other words, any non-NULL row in a lower level overrides > the value set at a higher level. > > Is this possible in SQL without using stored procedures? > > > > Thanks! > > > - RobertReceived on Tue Jul 06 2004 - 00:59:30 CEST