CODEWITHSUNDEEP
sqlsql-server
edezzie
edezzie

Reputation: 1717

Union of two columns in the same table

Say I have the following two columns in the same table

Column 1
--------
1
2
3

Column 2
--------
4
5
6

How do I get a result which gives me:

Columns
--------
1
2
3
4
5
6

Edit

What I'm really looking for is to make sure there is not a more efficient way of typing a union query on multiple columns in the same table without having to repeat which table it is multiple times and repeating a where condition for each union multiple times.

The actual query looks more like this:

WITH T1 AS 
( SELECT [Col1] FROM [Table1] 
)
SELECT * FROM (
    SELECT [Cols1-100], COUNT(*) as "Count" FROM (

        SELECT [Col-1] FROM [Table2] AS [Cols1-100], [T1] 
        WHERE [Table2].[Col-1] = [T1].[Col-1] 
        AND [Col-1] != '2' AND [Col-2] != '2' ..... etc ..... AND [Col-100] != '2'
        UNION ALL

        SELECT [Col-2] FROM [Table2] AS [Cols1-100], [T1] 
        WHERE [Table2].[Col-1] = [T1].[Col-1] 
        AND [Col-1] != '2' AND [Col-2] != '2' ..... etc ..... AND [Col-100] != '2'
        UNION ALL

        ....................... etc
        ....................... etc
        ....................... etc
        .... etc

        SELECT [Col-100] FROM [Table2] AS [Cols1-100], [T1] 
        WHERE [Table2].[Col-1] = [T1].[Col-1] 
        AND [Col-1] != '2' AND [Col-2] != '2' ...... etc .... AND [Col-100] != '2'

    ) as [Temp1]
    GROUP BY [Cols1-100]    
) as [Temp2]

Using @Bohemian Outer Query I can do the following but testing the two query's, it seems a lot slower.

WITH T1 AS 
( SELECT [Col1] FROM [Table1] 
)
SELECT * FROM (
    SELECT [Cols1-100], COUNT(*) as "Count" FROM (
            SELECT * FROM (
                SELECT [Col-1] AS [Cols1-100], [Col-1], [Col-2], ..etc.. [Col-100] FROM [Table2] 
                UNION ALL
                SELECT [Col-2] AS [Cols1-100], [Col-1], [Col-2], ..etc.. [Col-100] FROM [Table2] 
                UNION ALL
                ....................... etc
                .... etc
                SELECT [Col-100] AS [Cols1-100], [Col-1], [Col-2], ..... etc ..... [Col-100] FROM [Table2] 
            ) AS SUBQUERY WHERE [Col-1] IN (SELECT [Col1] FROM [T1])
            AND [Col-1] != '2' AND [Col-2] != '2' ..... etc ..... AND [Col-100] != '2' 
        ) as [Temp1]
    GROUP BY [Cols1-100]    
) as [Temp2]

Upvotes: 6

Views: 64351

Answers (3)

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

Have you tried UNPIVOT? It depends on your version of SQL Server, but the following example will work on SQL 2008:

DECLARE @t TABLE (Col1 INT, col2 INT, col3 INT)
INSERT INTO @t
        ( Col1, col2, col3 )
VALUES  ( 1, -- Col1 - int
          2, -- col2 - int
          3  -- col3 - int
          ),
          ( 4, -- Col1 - int
          5, -- col2 - int
          6  -- col3 - int
          )


 SELECT  cols
 FROM (SELECT col1, col2, col3 FROM @t) pvt
 UNPIVOT
 (Cols FOR ID IN (col1, col2, col3))  unpvt     
 WHERE cols <> 2

Upvotes: 0

Bohemian
Bohemian

Reputation: 424983

select column1 as columns from mytable
union
select column2 from mytable

Using union removes duplicates (and on some databases also sorts).
If you want to preserve duplicates, use union all:

select column1 as columns from mytable
union all
select column2 from mytable

Edit:

To add a where clause, the easy but inefficient execution way is to add it as an outer query:

select * from (
    select column1 as columns from mytable
    union
    select column2 from mytable ) x
where columns ...

The more efficient execution way, but a painfully long query, is to put it on each subquery:

select column1 as columns from mytable
where ....
union
select column2 from mytable
where ...

Upvotes: 17

beder
beder

Reputation: 1074

If you don't want to use union because you would have to repeat the same where clause multiple times, there is a [very bad] work around for that:

select decode(j.col, 1, column1, 2, column2)
from table t
join (select 1 as col from dual union select 2 from dual) j
 on 1 = 1
where (your where clause)

This example is from oracle, on SQLServer you wouldn't need the "from dual"

Also, if you have a LOT of columns to join (shouldn't happen, really) you could use a hierarchical query inside the "join" to avoid a ton of "unions"

Upvotes: 3

Related Questions