Path: news.easynews.com!core-easynews!newsfeed3.easynews.com!easynews.com!easynews!news.glorb.com!postnews2.google.com!not-for-mail
From: rromley@optonline.net (Richard)
Newsgroups: comp.databases,comp.databases.theory,comp.databases.oracle.server,comp.databases.ms-sqlserver
Subject: Re: Difficult Query: is this possible in SQL?
Date: 5 Jul 2004 10:32:25 -0700
Organization: http://groups.google.com
Lines: 125
Message-ID: <bfbb57f7.0407050932.4cc43ea7@posting.google.com>
References: <240a4d09.0407021517.1ee87cdb@posting.google.com>
NNTP-Posting-Host: 68.198.190.183
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1089048745 1717 127.0.0.1 (5 Jul 2004 17:32:25 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Mon, 5 Jul 2004 17:32:25 +0000 (UTC)
Xref: core-easynews comp.databases:105816 comp.databases.theory:36624 comp.databases.oracle.server:225015 comp.databases.ms-sqlserver:108546
X-Received-Date: Mon, 05 Jul 2004 10:31:35 MST (news.easynews.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@yahoo.com (Robert Brown) wrote in message news:<240a4d09.0407021517.1ee87cdb@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@yahoo.com (Robert Brown) wrote in message news:<240a4d09.0407021517.1ee87cdb@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