Hierarchy

**Erland Sommarskog** · Jul 10 '06, 11:55 AM

Re: Hierarchy

SQLNull (ramonjor@yahoo .com) writes:

And I need a query (not a procedure) that shows me this:
38; NULL; NULL; NULL; NULL
NULL; 75; NULL; NULL; NULL
NULL; NULL; 233; NULL; NULL
NULL; NULL; NULL; 916; NULL
NULL; NULL; NULL; NULL; 2770
NULL; NULL; NULL; NULL; 2771
NULL; NULL; NULL; NULL; 2772
NULL; NULL; NULL; 923; NULL
NULL; NULL; NULL; NULL; 2654
NULL; NULL; NULL; NULL; 2655
NULL; NULL; 245; NULL; NULL
NULL; NULL; NULL; 913; NULL
NULL; NULL; NULL; NULL; 2454
NULL; NULL; NULL; NULL; 2456
...
>
Does anybody know how i can get this result? How?
>...
ps: SQL-Server 2000

You are going to regret this...

The query is below, and I encourage you to study it closely to see what
is going on. The query makes use of derived tables - a derived table is
a temp table within the query so to speak, but not necessarily
materialsed. All SELECTs are derived tables, execpt the two SELECT
COUNT - they are correlated subqueries.

Had you been on SQL 2005, it would have been possible to write the
query more compactly with help of a CTE - Common Table Expression.
Also the row_number() function would have come in handy.

The keystr that appears in the query is a simplifcation that I could
permit myself, when all columns where numeric. It may not work well,
if your actual table have different data types. But they query could
be written without keystr. (Which is left as an exercise to the reader.)

Performance is not likely to be good.

CREATE TABLE h (col1 int NOT NULL,
col2 int NOT NULL,
col3 int NOT NULL,
col4 int NOT NULL,
col5 int NOT NULL,
PRIMARY KEY (col1, col2, col3, col4, col5))
go
INSERT h (col1, col2, col3, col4, col5)
EXEC ('SELECT 38, 75, 233, 916, 2770
SELECT 38, 75, 233, 916, 2771
SELECT 38, 75, 233, 916, 2772
SELECT 38, 75, 233, 923, 2654
SELECT 38, 75, 233, 923, 2655
SELECT 38, 75, 245, 913, 2454
SELECT 38, 75, 245, 913, 2456')
go
SELECT keystr, col1, col2, col3, col4, col5
FROM (SELECT a.keystr,
col1 = CASE WHEN a.col1 <b.col1 OR b.col1 IS NULL
THEN a.col1
END,
col2 = CASE WHEN a.col2 <b.col2 OR b.col2 IS NULL
THEN a.col2
END,
col3 = CASE WHEN a.col3 <b.col3 OR b.col3 IS NULL
THEN a.col3
END,
col4 = CASE WHEN a.col4 <b.col4 OR b.col4 IS NULL
THEN a.col4
END,
col5 = CASE WHEN a.col5 <b.col5 OR b.col5 IS NULL
THEN a.col5
END
FROM (SELECT keystr,
rowno = (SELECT COUNT(*)
FROM h AS h1
WHERE str(h1.col1) + str(h1.col2) +
str(h1.col3) + str(h1.col4) +
str(h1.col5) <=
str(h.col1) + str(h.col2) +
str(h.col3) + str(h.col4) +
str(h.col5)),
col1 = CASE n WHEN 1 THEN h.col1 END,
col2 = CASE n WHEN 2 THEN h.col2 END,
col3 = CASE n WHEN 3 THEN h.col3 END,
col4 = CASE n WHEN 4 THEN h.col4 END,
col5 = CASE n WHEN 5 THEN h.col5 END,
v.n
FROM (SELECT keystr = str(h.col1) + str(h.col2) +
str(h.col3) + str(h.col4) +
str(h.col5),
col1, col2, col3, col4, col5
FROM h) AS h
CROSS JOIN (SELECT n = 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5) AS v) AS a
LEFT JOIN
(SELECT keystr = str(h.col1) + str(h.col2) + str(h.col3) +
str(h.col4) + str(h.col5),
rowno = (SELECT COUNT(*)
FROM h AS h1
WHERE str(h1.col1) + str(h1.col2) +
str(h1.col3) + str(h1.col4) +
str(h1.col5) <=
str(h.col1) + str(h.col2) +
str(h.col3) + str(h.col4) +
str(h.col5)),
col1 = CASE n WHEN 1 THEN h.col1 END,
col2 = CASE n WHEN 2 THEN h.col2 END,
col3 = CASE n WHEN 3 THEN h.col3 END,
col4 = CASE n WHEN 4 THEN h.col4 END,
col5 = CASE n WHEN 5 THEN h.col5 END,
v.n
FROM h
CROSS JOIN (SELECT n = 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5) AS v) AS b
ON a.rowno = b.rowno + 1
AND a.n = b.n) AS final
WHERE col1 IS NOT NULL OR
col2 IS NOT NULL OR
col3 IS NOT NULL OR
col4 IS NOT NULL OR
col5 IS NOT NULL
ORDER BY keystr
go
DROP TABLE h

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**SQLNull** · Jul 12 '06, 02:35 PM

Re: Hierarchy

You are right, I'm going to regret your query... :-) Thank you!
This one ist very simple n fast!
The difference is that it will be sorted. But it doesn't matter, I just
need the hierarchy.
Thank you!
SQLNULL

CREATE TABLE h (col1 int NOT NULL,
col2 int NOT NULL,
col3 int NOT NULL,
col4 int NOT NULL,
col5 int NOT NULL,
PRIMARY KEY (col1, col2, col3, col4, col5))
go
INSERT h (col1, col2, col3, col4, col5)
EXEC ('SELECT 38, 75, 233, 916, 2770
SELECT 38, 75, 233, 916, 2771
SELECT 38, 75, 233, 916, 2772
SELECT 38, 75, 233, 923, 2654
SELECT 38, 75, 233, 923, 2655
SELECT 38, 75, 245, 913, 2454
SELECT 38, 75, 245, 913, 2456')
go
SELECT c1 as col1, c2 as col2, c3 as col3, c4 as col4, c5 as col5
FROM (
SELECT DISTINCT col1 AS c1, c2=NULL, c3=NULL, c4=NULL, c5=NULL,
col1, col2=NULL, col3=NULL, col4=NULL,
col5=NULL
FROM h
UNION
SELECT DISTINCT NULL, col2, NULL, NULL, NULL,
col1, col2, NULL, NULL, NULL
FROM h
UNION
SELECT DISTINCT NULL, NULL, col3, NULL, NULL,
col1, col2, col3, NULL, NULL
FROM h
UNION
SELECT DISTINCT NULL, NULL, NULL, col4, NULL,
col1, col2, col3, col4, NULL
FROM h
UNION
SELECT NULL, NULL, NULL, NULL, col5,
col1, col2, col3, col4, col5
FROM h
) BIGUNION
ORDER BY col1, col2, col3, col4, col5
go
DROP TABLE h

**--CELKO--** · Jul 15 '06, 06:15 PM

Re: Hierarchy

Let me do what I always do and suggest that you get a copy of TREES &
HIERARCHIES IN SQL for several better approachs for modeling this kind
of structure. Right now, you are destroying information with false
NULLs. What were you trying to do?

Hierarchy

Hierarchy

Comment

Comment

Comment