SQLNull (ra******@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,
es****@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx 
