CODEWITHSUNDEEP
sql
mrjimoy_05
mrjimoy_05

Reputation: 3568

Joining two columns on a table in SQL

I have a problem that is quite simple to understand, but for me it is not that simple to implement.

I have a table named Time:

-----------------------------------
 DAY     TIME1     TIME2
-----------------------------------
 1       08.00     09.40
 1       09.40     10.00
 1       10.00     11.40
 2       08.00     08.50
 2       08.50     10.40
 ----------------------------------

What I want to get is like:

-------------------
 DAY     TIME     
-------------------
 1       08.00     
 1       09.40     
 1       10.00     
 1       11.00     
 2       08.00     
 2       08.50     
 2       10.40     
 ------------------

I have tried this code:

SELECT DISTINCT sub.val FROM (
  SELECT Time1 AS val FROM Time
  UNION ALL
  SELECT Time2 AS val FROM Time
) AS sub

And it is only return the Time column. I have no idea about joining the "Day" column. While I am trying to add the "Day" after the "DISTINCT sub.val", it got error. How to do it?

Thank you very much.

Upvotes: 5

Views: 4759

Answers (4)

Arion
Arion

Reputation: 31239

This might help:

SELECT
    DAY,
    TIME1
FROM
    YourTable AS T
UNION
SELECT
    DAY,
    TIME2
FROM
    YourTable AS T
ORDER BY DAY,TIME1

Upvotes: 2

silly
silly

Reputation: 7887

try this

SELECT DISTINCT *
FROM (
    SELECT day, time1 FROM Time
    UNION ALL SELECT day, time2 FROM Time
) AS x
ORDER BY x.day

Upvotes: 3

Randy
Randy

Reputation: 16677

select distinct day, time1 from mytab
union
select distinct day, time2 from mytab

Upvotes: 6

Sam DeHaan
Sam DeHaan

Reputation: 10325

SELECT DISTINCT sub.Day, sub.val as Time FROM (
  SELECT Day, Time1 AS val FROM Time
  UNION ALL
  SELECT Day, Time2 AS val FROM Time
) AS sub

Upvotes: 3

Related Questions