Re: Difficult Query: is this possible in SQL?
Date: 5 Jul 2004 10:32:25 -0700
Message-ID: <bfbb57f7.0407050932.4cc43ea7_at_posting.google.com>
Hi Robert,
Here are 2 more solutions...
create table Foobar
(
level int NOT NULL , -- pk color varchar(10) NULL , length int NULL , width int NULL , hgt int NULL ,
primary key clustered
( level )
)
insert Foobar
( level, color, length, width, hgt )
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
(select color from Foobar where level =
(select max(level) from Foobar where color is not null)) as color , (select length from Foobar where level =
(select max(level) from Foobar where length is not null)) as length , (select width from Foobar where level =
(select max(level) from Foobar where width is not null)) as width , (select hgt from Foobar where level =
(select max(level) from Foobar where hgt is not null)) as hgt
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!
>
>
> - Robert
Received on Mon Jul 05 2004 - 19:32:25 CEST