Create a table with every possible input in SQL Server

Question

I am new to SQL and having a hard time figuring out how to implement what I am trying to do. Currently my testers manually create a file with every possible input to put through our programs. What I want to do is create a stored proc that takes in some input tables and sets up every possible input for them. Currently I have two tables. The first is TestFileColumnHeaders with two columns, ColumnID and ColumnName with the format

ColumnID ColumnName
_ _ _ _ _ _ _ _ _
1 Foo
2 Bar
3 Baz

This table is for the headers on the input file that is being created. Then I have a table for the possible inputs called TestFileColumnInfo with columns ColumnID and ColumnInfo with the format

 ColumnID  ColumnInfo  
 _ _ _ _   _ _ _ _ _  
 1         Foo1  
 1         Foo2  
 2         Bar1  
 2         Bar2  
 3         Baz1

The output I want for my proc is a table like

Foo Bar Baz
_ _ _ _ _ _
Foo1 Bar1 Baz1
Foo1 Bar2 Baz1
Foo2 Bar1 Baz1
Foo2 Bar2 Baz1

I tried a Cartesian join but I dont want to have Foo1 and Foo2 to show up in the same row on the output so thats where I am stuck. Only for an unlimitied number of headers and inputs. Is there a way to do this in T-SQL? Do I need to change the design of my tables to something else? Also Sorry If the editing is off. I cant figure out how to keep the correct spacing for my headers and underlineing

Answer 1

You can try something like this (simple version):

 SELECT * FROM 
    (SELECT DISTINCT ColumnInfo AS foo
    FROM TestFileColumnHeaders  h
    INNER JOIN dbo.TestFileColumnInfo i ON h.ColumnID = i.ColumnID
    WHERE ColumnName = 'Foo') foo
CROSS JOIN 
    (SELECT DISTINCT ColumnInfo AS bar
    FROM TestFileColumnHeaders  h
    INNER JOIN dbo.TestFileColumnInfo i ON h.ColumnID = i.ColumnID
    WHERE ColumnName = 'Bar') bar
CROSS JOIN
    (SELECT DISTINCT ColumnInfo AS baz
    FROM TestFileColumnHeaders  h
    INNER JOIN dbo.TestFileColumnInfo i ON h.ColumnID = i.ColumnID
    WHERE ColumnName = 'Baz') baz;

SQLFiddle DEMO

If your number of columns are not fixed, you will have to do a dynamic sql.