Re: Difficult Query: is this possible in SQL?

Hi Aaron,
That's an excellent technique, but to use it in this example you need to take the max() of ONLY the levels for which the tested columns are NOT NULL. Otherwise you will always get the values for level 5 which is incorrect. This is best done by adding a case statement inside the aggregate. When you add that logic, and the conversions back and forth from integer to char, it starts to get a bit messy.

Here is a working solution for this problem using that technique:

select color = substring(max(cast(case when color IS NOT NULL then level else 0 end as char(1))+color),2,10),

       length = cast(substring(max(cast(case when length IS NOT NULL then level else 0 end as char(1))+cast(length as char(9))),2,9) as integer),

       width = cast(substring(max(cast(case when width IS NOT NULL then level else 0 end as char(1))+cast(width as char(9))),2,9) as integer),

       hgt = cast(substring(max(cast(case when hgt IS NOT NULL then level else 0 end as char(1))+cast(hgt as char(9))),2,9) as integer)   from Foobar

It works, but at some point you need to question whether any benefit achieved is worth having unreadable code.

Richard

"Aaron W. West" <tallpeak_at_hotmail.NO.SPAM> wrote in message news:<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)
> ,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
>
> -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
> (
> 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 - 22:25:45 CDT