Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SQL Union with empty columns

Answered SQL Union with empty columns

  • Thursday, February 14, 2013 3:02 PM
     
     
    Sign In to Vote
    0

    I need to make a UNION query where 1 table has more columns that the other. How can I create empty columns so that the number of columns can match? For example, let's view at the data :

    table 1

    [Item ID]  [Item_number]  [Item]

    table 2

    [ID]  [Reference Document] [Document Type] ...

    My output should looke like this :

    [Item ID]  [Item_number]  [Item]  [ID]  [Reference Document] [Document Type] ...

    In conclusion, UNION should return all columns from the 1st table, first, from left to right, then, all columns on the 2nd table.

    Adrian Hernandez

     

All Replies

  • Thursday, February 14, 2013 3:06 PM
     
     
    Sign In to Vote
    0

    That's not what UNION does.  In your proposed result, how many rows would you have?  And post DDL and sample data if you want a real answer.

    David

    David http://blogs.msdn.com/b/dbrowne/

     
  • Thursday, February 14, 2013 3:08 PM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    1
     
    SELECT [Item ID],  [Item_number],  [Item],  null as [ID],  null as  [Reference Document],  null as [Document Type]  FROM table1
UNION ALL
SELECT null as [Item ID],  null as [Item_number], null as [Item],  [ID],  [Reference Document], [Document Type]  FROM table2

    • Marked As Answer by ahpitre Thursday, February 14, 2013 4:38 PM
    •  
     
  • Thursday, February 14, 2013 3:08 PM
     
     
    Sign In to Vote
    1

    Like this.....

    /***************************************************/
    select [Item ID] , [Item_number],  [Item] , '' as  [ID] , '' as  [Reference Document], '' as [Document Type]  from table1
    union all 
    select  '' as [Item ID] , '' as [Item_number], '' as  [Item] ,  [ID] ,  [Reference Document], [Document Type]  from Table2 

    /***************************************************/

    Thanks,

    Saurabh

    http://www.linkedin.com/profile/view?id=36482856&trk=tab_pro http://www.experts-exchange.com/M_6313078.html


     
  • Thursday, February 14, 2013 3:28 PM
     
     
    Sign In to Vote
    0
    In my example I am only giving you the column names of both tables, plus resulting columns of UNION (not using the actual values/rows).

    Adrian Hernandez

     
  • Thursday, February 14, 2013 4:00 PM
     
     
    Sign In to Vote
    0

    Providing the data from  the two result sets relates to each other, then you will want to use either a JOIN or a SUBQUERY to get the columns sitting next to each other in a single results set...

    So assuming table1.[Item ID] and table2.ID are referring to the same keys values then you can  join them... i.e.

    Select

    t1.[Item ID], t1.[Item_number], t1. [Item], t2.[ID], t2.[Reference Document], t2.[Document Type]

    from table1 as t1

    join table 2 as t2

    on t1.[item ID] = t2.ID

    UNIONs stack the result sets on top of each other... which is not what you are after.

     
  • Thursday, February 14, 2013 4:38 PM
     
     
    Sign In to Vote
    0

    I already got my SQL code for this. What I actually meant is getting all rows from table 1, and table 2, with an output format of table 1's columns then table 2's columns next to each other. Anyway, thanks.

    Thanks to all for your valuable input. Great forum. Special thanks to Jingyang Li and Saurabh Bhadauria.

    Adrian Hernandez