|
|
Posted by SQLNull on 07/12/06 14:34
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
Navigation:
[Reply to this message]
|