Re: Difficult Query: is this possible in SQL?

"Robert Brown" <robertbrown1971_at_yahoo.com> 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

CREATE TABLE T
(
level INT NOT NULL PRIMARY KEY,
color VARCHAR(10) NULL,
length INT NULL,
width INT NULL,
height INT NULL
)

• Option 1 SELECT (SELECT color FROM T WHERE level = M.LC) AS color, (SELECT length FROM T WHERE level = M.LL) AS length, (SELECT width FROM T WHERE level = M.LW) AS width, (SELECT height FROM T WHERE level = M.LH) AS height FROM (SELECT MAX(CASE WHEN color IS NOT NULL THEN level END) AS LC, MAX(CASE WHEN length IS NOT NULL THEN level END) AS LL, MAX(CASE WHEN width IS NOT NULL THEN level END) AS LW, MAX(CASE WHEN height IS NOT NULL THEN level END) AS LH FROM T) AS M
• Option 2 SELECT MIN(CASE WHEN T.level = M.LC THEN T.color END) AS color, MIN(CASE WHEN T.level = M.LL THEN T.length END) AS length, MIN(CASE WHEN T.level = M.LW THEN T.width END) AS width, MIN(CASE WHEN T.level = M.LH THEN T.height END) AS height FROM (SELECT MAX(CASE WHEN color IS NOT NULL THEN level END) AS LC, MAX(CASE WHEN length IS NOT NULL THEN level END) AS LL, MAX(CASE WHEN width IS NOT NULL THEN level END) AS LW, MAX(CASE WHEN height IS NOT NULL THEN level END) AS LH FROM T) AS M INNER JOIN T ON T.level IN (M.LC, M.LL, M.LW, M.LH)

--
JAG