Re: Hierarchy

SQLNull (ramonjor@xxxxxxxxx) 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@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: Combining records from two queries
    ... combining UNION and SELECT queries to best effect. ... The list in each case has to have the same number & types of fields (for example, if the first Query begins with a Date/Time field, the second one should do so as well). ... I like to keep my Union Queries short and simple and do the rest of the work elsewhere. ... tblMentors comprises Subject Mentors and Professional Mentors and the Placement subform has a combo for each - the Subject Mentor combo puts the chosen MentorID in the SubjectMentorID field and the Professional Mentor combo puts the chosen MentorID in the ProfessionalMentorID field. ...
    (microsoft.public.access.queries)
  • Re: Graphical Union-Query Builder?
    ... If you are limiting the input in each of the sub-queries in your Union ... Use the filtered queries as the input to your Union query: ... Most "functional IT users" will not know anything about SQL. ... I didn't know that fields of subsequent queries in a Union could have ...
    (microsoft.public.access.queries)
  • Re: Help! Union Query has started crashing!
    ... It's tblSupport on the RLR_SUPPORT_INFOTERRA.mdb database. ... I think I have tracked the problem down to the query ... I also tried a UNION ALL, ... the actual structure of the queries as they have been running fine for weeks. ...
    (microsoft.public.access.queries)
  • Re: How to get a distinct count of result set of multople table joins?
    ... since the UNION syntax removes duplicate rows automatically. ...   "SORT " in your execution plan. ... The base query is an outer join. ...
    (comp.databases.oracle.misc)
  • Re: Sum of numbers
    ... "Evi" wrote: ... You say the union query 'only shows fields from the first table'. ... Do you mean that you want a multicolumn report with all customers' names ...
    (microsoft.public.access.reports)