locked
How can I query two different columns that make up one column? RRS feed

  • Question

  • I have a Report Request that gets relatively the same information from two different tables and two different columns and will be identifiable as the same column within a report. I don't think I can do this as a UNION or UNION ALL because the column names are different and their data types might be different as well. I thought about breaking each separate piece into Common Table Expressions but I'm not sure that that's the answer either. I am now thinking of doing it via a CASE Statement but I don't know how I would instruct the CASE to take its value from one place and then the other.

    Any ideas as to how I can try and make this happen?

    Thanks in advance for your review and am hopeful for a reply.

    PSULionRP

    Thursday, June 6, 2013 8:09 PM

Answers

  • Possibly with a Union.  The column names don't have to be the same.  If the datatypes are different you need to implicitly or explicitly cast one or both of them to a compatible datatype.  For example

    Declare @TableA Table(MyData int);
    Insert @TableA(MyData) Values(25);
    Declare @TableB Table(OtherData varchar(20));
    Insert @TableB(OtherData) Values('Abcdef');
    Select Cast(MyData As varchar(12)) As MyColumn
    From @TableA
    Union
    Select OtherData As MyColumn
    From @TableB;
    

    If that's not what you need, posting sample data and the result you would want from that sample data would probably help us help you.

    Tom

    • Proposed as answer by anu a Thursday, June 6, 2013 10:37 PM
    • Marked as answer by Allen Li - MSFT Friday, June 14, 2013 3:22 AM
    Thursday, June 6, 2013 8:19 PM

All replies

  • Possibly with a Union.  The column names don't have to be the same.  If the datatypes are different you need to implicitly or explicitly cast one or both of them to a compatible datatype.  For example

    Declare @TableA Table(MyData int);
    Insert @TableA(MyData) Values(25);
    Declare @TableB Table(OtherData varchar(20));
    Insert @TableB(OtherData) Values('Abcdef');
    Select Cast(MyData As varchar(12)) As MyColumn
    From @TableA
    Union
    Select OtherData As MyColumn
    From @TableB;
    

    If that's not what you need, posting sample data and the result you would want from that sample data would probably help us help you.

    Tom

    • Proposed as answer by anu a Thursday, June 6, 2013 10:37 PM
    • Marked as answer by Allen Li - MSFT Friday, June 14, 2013 3:22 AM
    Thursday, June 6, 2013 8:19 PM
  • Ok...that's a good idea...I'll run with that...And see what happens...
    Thursday, June 6, 2013 8:31 PM
  • >> I have a Report Request that gets relatively the same information from two different tables and two different columns and will be identifiable as the same column within a report. <<

    The first reason for databases (not just RDBMS!) was to reduce redundancy. This design an example attribute splitting; it is a common design flaw done by old mag tape programmers who skipped that data modeling class they were supposed to attend.  You need to fix this! 

    >> I don't think I can do this as a UNION or UNION ALL because the column names are different and their data types might be different as well. <<

    That is a common kludge for a split table. Needless overhead, bitch to maintain, lack of data integrity, etc. but kludges are like that. Now, back to basics 

    1) The tables in set operations (UNION [ALL], EXCEPT [ALL] and INTERSECT [ALL]) have to be union compatible. That means same number of columns, with corresponding columns of the same data type (I prefer to do explicit CASTing, but implicit will work). 

    2) Default execution order is left to right, so use parentheses with mixed operators. 

    3) The result is a table without a name, and whose columns have no names either. So you need to use the (<set exprssion>)AS <tablename> ( <column name list>)


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, June 7, 2013 1:32 AM