SQL Two column as ones

Dear Expert,

Can I select two column from difference table as two column like attachment?
sql.xlsx
arkam chouAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
It's called UNION:

SELECT  DateA AS DateIssue ,
        Name
FROM    tblA
UNION ALL
SELECT  DateB ,
        Name
FROM    tblB;

Open in new window

Jim HornMicrosoft SQL Server Data DudeCommented:
Yes, that's a basic UNION ALL
SELECT DateA as DateIssue, Name
FROM tblA
UNION ALL
SELECT DateB, Name
FROM tblB

Open in new window


UNION if you wish to eliminate duplicates, UNION ALL if duplicates are ok.
Jim HornMicrosoft SQL Server Data DudeCommented:
Nice picture.  btw Next time do us a favor and capture a screenshot of your diagram, and use the Image button to post it.  That way we can see your picture and question in one view without having to click back and forth.  Thanks in advance. ask-question-buttons.jpg
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

arkam chouAuthor Commented:
so it can by using "UNION ALL". How about custom column like attachment?
6-25-2015-11-56-59-PM.jpgsql.xlsx
Jim HornMicrosoft SQL Server Data DudeCommented:
Give this a whirl..
SELECT DateIssue, Name, number, price
FROM (
   SELECT DateA as DateIssue, Name, number, NULL as price
   FROM tblA
   UNION ALL
   SELECT DateB, Name, NULL, price
   FROM tblB) 
WHERE number=10 OR price=200

Open in new window

ste5anSenior DeveloperCommented:
When using large sets, I would use:

 SELECT DateA AS DateIssue ,
        Name ,
        number ,
        NULL AS price
 FROM   tblA
 WHERE  number = 10
 UNION ALL
 SELECT DateB ,
        Name ,
        NULL ,
        price
 FROM   tblB
 WHERE  price = 200;

Open in new window


Cause imho there is no predicate push-down in this case.
arkam chouAuthor Commented:
Hello All,

@Jim Horn: I saw in MSSQL said that "incorrect syntax. when put mouse hover on "DateIssue", it said that "invalide column name. what should to do?

@ste5an: I need to put some condition after select.
ex: order by name
ste5anSenior DeveloperCommented:
Just use a sub-query as Jim did.
arkam chouAuthor Commented:
Dear Expert,

Below are the error when I process it Microsoft SQL

6-26-2015-8-38-13-AM.jpg6-26-2015-8-38-43-AM.jpg6-26-2015-8-39-12-AM.jpg6-26-2015-8-40-53-AM.jpg
ste5anSenior DeveloperCommented:
Yup, using a sub-query requires a table alias name:

SELECT  DateIssue ,
        Name ,
        number ,
        price
FROM    ( SELECT    DateA AS DateIssue ,
                    Name ,
                    number ,
                    NULL AS price
          FROM      tblA
          UNION ALL
          SELECT    DateB ,
                    Name ,
                    NULL ,
                    price
          FROM      tblB
        ) Q
WHERE   number = 10
        OR price = 200;

Open in new window


The Q or any name which you're comfortable with.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
arkam chouAuthor Commented:
yes, it work but I have problem with "Null" display in column "price" and "number". How can I use "group by" and "sum" on this query?
SELECT  DateIssue ,
        Name ,
        sum(number) ,
        sum(price)
FROM    ( SELECT    DateA AS DateIssue ,
                    Name ,
                    number ,
                    NULL AS price
          FROM      tblA
          UNION ALL
          SELECT    DateB ,
                    Name ,
                    NULL ,
                    price
          FROM      tblB
        ) Q
group by dateissue, name
WHERE   number = 10
        OR price = 200;

Open in new window

ste5anSenior DeveloperCommented:
GROUP BY must be placed after the WHERE clause.
arkam chouAuthor Commented:
yes, it work. one more thing how to change from "null" value to "0" value?
arkam chouAuthor Commented:
ok. now it work with "ISNULL(null, 0)".
arkam chouAuthor Commented:
Thanks you all for you support.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.