Within a SQL Server view - how to combine multiple column results into one column

Question

I have a SQL Server database with the following 2 tables:

I have created a view with the following query and results:

My question is what query would bring the (3) ID columns in 'Table2' into one master ID List to where the final result would look like this:

ID    Table1ID
test1   1
test1   4
test2   1
test2   2
test3   1
test3   2
test3   3

Note: here is the view as shown above:

SELECT        
   dbo.Table1.Description, Table2_1.ID AS Table2ID_1, Table2_2.ID AS Table2ID_2, 
   dbo.Table2.ID AS Table2ID_3
FROM            
   dbo.Table1 
LEFT OUTER JOIN
   dbo.Table2 ON dbo.Table1.ID = dbo.Table2.Table1ID3 
LEFT OUTER JOIN
   dbo.Table2 AS Table2_2 ON dbo.Table1.ID = Table2_2.Table1ID2 
LEFT OUTER JOIN
   dbo.Table2 AS Table2_1 ON dbo.Table1.ID = Table2_1.Table1ID1

Answer 1

Microsoft SQL Server 2005 and higher support an UNPIVOT statement making the CROSS APPLY unnecessary.

SELECT Description AS [ID], Table1ID
FROM         (SELECT     Table1.Description, Table2_1.ID AS Table2ID_1, Table2_2.ID AS Table2ID_2, Table2.ID AS Table2ID_3
              FROM         Table1 LEFT OUTER JOIN
                                    Table2 ON Table1.ID = Table2.Table1ID3 LEFT OUTER JOIN
                                    Table2 AS Table2_2 ON Table1.ID = Table2_2.Table1ID2 LEFT OUTER JOIN
                                    Table2 AS Table2_1 ON Table1.ID = Table2_1.Table1ID1) AS pvttbl
     UNPIVOT ( Table1ID FOR ID IN (Table2ID_1, Table2ID_2, Table2ID_3)) AS unpvttbl
ORDER BY Description, Table1ID

See Using PIVOT and UNPIVOT on MSDN.

Answer 2

My suggestion would be to UNPIVOT the data in Table2 so you can easily join on the data, then you can return the table1 description and the table2 id. The UNPIVOT portion of this query using CROSS APPLY:

select col, value, t2.Id
from table2 t2
cross apply
(
  select 'table1id1', table1id1 union all
  select 'table1id2', table1id2 union all
  select 'table1id3', table1id3
 ) c (col, value);

See SQL Fiddle with Demo. This gives a result:

|       COL |  VALUE | ID |
---------------------------
| table1id1 |      1 |  1 |
| table1id2 |      2 |  1 |
| table1id3 |      3 |  1 |
| table1id1 |      2 |  2 |
| table1id2 |      3 |  2 |
| table1id3 | (null) |  2 |
| table1id1 |      3 |  3 |

Now that you have the data in rows, you can easily join on the value column to return the id:

select t1.description,
  d.id
from table1 t1
inner join
(
  select col, value, t2.Id
  from table2 t2
  cross apply
  (
    select 'table1id1', table1id1 union all
    select 'table1id2', table1id2 union all
    select 'table1id3', table1id3
   ) c (col, value)
) d
  on t1.id = d.value
order by t1.description, d.id;

See SQL Fiddle with Demo

If you really want to use UNPIVOT, then you can use the following which doesn't join on each table multiple times to get the result:

select t1.description, t2.id
from table1 t1
inner join
(
  select id, col, value
  from 
  (
    select id, [Table1ID1], [Table1ID2], [Table1ID3]
    from table2
  ) d
  unpivot
  (
    value for col in ([Table1ID1], [Table1ID2], [Table1ID3])
  ) unpiv
) t2
  on t1.id = t2.value
order by t1.description, t2.id;

See SQL Fiddle with Demo.

The UNPIVOT and the CROSS APPLY is doing the same thing as a UNION ALL query:

select t1.description, t2.id
from table1 t1
inner join
(
  select id, 'table1id1' col, table1id1 value
  from table2
  union all
  select id, 'table1id2' col, table1id2
  from table2
  union all
  select id, 'table1id3' col, table1id3
  from table2
) t2
  on t1.id = t2.value
order by t1.description, t2.id;

See SQL Fiddle with Demo