Avatar of dprovencher
dprovencher asked on

Output grid-like table from many unlinked tables

Hi, I've been searching and thinking of a way to do this all day yesterday and I'm about to give up and do it in C#.  But before I do I'd really like to know if anyone can think of a way to do this in SQL.  What I want to do is make a query that returns rows based on other tables.  That's not clear, let's say I've got the following tables:

tbl1
a b c
d e f
g h i

tbl2
1 2 3
4 5 6
7 8 9
0 1 2

What I want is to get something like this:

a b c 1 2 3
d e f 4 5 6
g h i 7 8 9
NULL NULL NULL 0 1 2

I don't want to use the CREATE TABLE by the way, I hate the idea of temp tables, they should never be used :)

Thanks for any input!

I use SQL Server 2000.
Microsoft SQL ServerMicrosoft SQL Server 2005SQL

Avatar of undefined
Last Comment
dprovencher

8/22/2022 - Mon
JCinDE

You generally need to have some sort of key to identify which row in tbl1 goes with which row in tbl2. (Unless you're looking for a matrix of the two, which is pretty simple to get with just "select * from tbl1, tbl2" but I don't get the sense that's what you want.)

I really need more information to go on. The question is pretty vague.

By the way: Never say never. Temp tables have their place
ASKER
dprovencher

There is no common key in either table, the resulting records will be used in an horizontal report, that's why I need this.  Thanks for the reply :)
ASKER CERTIFIED SOLUTION
Patrick Matthews

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
dprovencher

Damn thanks!  All I was missing was the ORDER BY COALESCE(#t1.ID, #t2.ID) all my records were mixed up :D

I'll post my solution without temp tables and give you the points.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
dprovencher

Here's my code, first a UDF for each table (don't know how else to do this):

CREATE FUNCTION fct_tbl_cie_identity ()
RETURNS @Table TABLE  (NoCompagnie NVARCHAR(1024), Nom NVARCHAR(1024), RowIndex INT IDENTITY) AS
BEGIN
INSERT INTO @Table
SELECT NoCompagnie, Nom FROM tbl_cie
RETURN
END

And the actual query:

SELECT *
FROM
fct_tbl_cie_identity() cie FULL OUTER JOIN
fct_tbl_contrat_identity() contrat ON cie.RowIndex = contrat.RowIndex FULL OUTER JOIN
fct_tbl_chantier_identity() chantier ON contrat.RowIndex = chantier.RowIndex FULL OUTER JOIN
fct_tbl_activite_identity() activite ON chantier.RowIndex = activite.RowIndex
ORDER BY COALESCE(cie.RowIndex, contrat.RowIndex, chantier.RowIndex, activite.RowIndex)
Patrick Matthews

dprovencher,

Nicely done :)  I would be curious to know if there were any difference in performance when comparing our
two approaches...

Regards,

Patrick
ASKER
dprovencher

The tables are not big enough I don't think so, but I'd rather use memory instead of the hard disk :)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.