SQL split columns to rows after using union

Question

My table

+-----------+---------+
|    Date   |  Letter |
+-----------+---------+
| 13.02.2013|    B    |
| 01.03.2016|    A    |
| 28.12.2003|    C    |
| 12.01.2017|    B    |
| 25.04.2011|    A    |
+-----------+---------+

I created a query which returns right data, but not as expected:

SELECT * from
(
SELECT TOP 1 Date as Date1, Letter as Letter1 from TAB where
Letter = 'A'
order by Date DESC
) TAB
UNION
SELECT * from
(
SELECT TOP 1 Date as Date2, Letter as Letter2 from TAB where 
Letter = 'B'
order by Datum DESC
) TAB

Expected output:

+-----------+---------+-----------+---------+
|    Date1  | Letter1 |    Date2  | Letter2 |
+-----------+---------+-----------+---------+
| 01.03.2016|    A    | 12.01.2017|    B    |
+-----------+---------+-----------+---------+

Output:

+-----------+---------+
|    Date1  | Letter1 |
+-----------+---------+
| 01.03.2016|    A    | 
| 12.01.2017|    B    |
+-----------+---------+

Is it possible get all 4 rows like expected using UNION?

Thank you.

I think this can help. http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — Tajinder, Mar 07 '17 at 11:06

score 1 · Accepted Answer · answered Mar 07 '17 at 11:49

If I understand correctly, you seem to want the "A"s and the "B"s in lists in columns. This is not really a relational output, because the columns in a row have no relationship to each other. But, you can do it using conditional aggregation: TOP 1 Date as Date1, Letter as Letter1

select max(case when letter = 'A' then date end) as date1,
       'A' as letter1,
       max(case when letter = 'B' then date end) as date2,
       'B' as letter2
from (select t.*,
             row_number() over (partition by letter order by date desc) as seqnum
      from t
      where letter in ('A', 'B')
     ) t
group by seqnum
order by seqnum;

Hi Gordon. Thank you very much. Your solutions works perfect. I also added ID to it and im getting what i expected. — user2463808, Mar 07 '17 at 15:02

score 0 · Answer 2 · answered Mar 07 '17 at 11:07

0

try to change the second query to (as date 1 and as letter1 ) the columns have to be the same

answered Mar 07 '17 at 11:07

Bchir Med Amine

78
13

score 0 · Answer 3 · answered Mar 07 '17 at 11:07

0

You can use an join instead of union all for the two sub queries and call the columns from both the sub queries using alias

answered Mar 07 '17 at 11:07

Tom J Muthirenthi

3,028
7
40
60

score 0 · Answer 4 · answered Mar 07 '17 at 11:08

If you need the result on the same row you need a join (not an union) eg:

SELECT T1.Date, T1.Letter, T2.date, T2.Letter 
from  (
    SELECT TOP 1 '1' as id,  Date , Letter 
    from TAB 
    where Letter = 'A'
    order by Date DESC
) T1
INNER JOIN ( 
  SELECT TOP 1  '1' as id, Date , Letter  
  from TAB 
  where   Letter = 'B'
  order by Datum DESC
) T2 on T1.id = T2.id

score 0 · Answer 5 · answered Mar 07 '17 at 11:09

Hoping i understood your problem correctly.

Please check below query.

select Date1 , Letter1 , Date2 , Letter2
from
(SELECT TOP 1 Date as Date1, Letter as Letter1 from TAB where
Letter = 'A'
order by Date DESC) a join 
(
SELECT TOP 1 Date as Date2, Letter as Letter2 from TAB where 
Letter = 'B'
order by Date DESC
) b;

score 0 · Answer 6 · edited May 23 '17 at 12:25

0

This is not what UNION does. It simply combines the given two data sets into one, as you can see in your example. You could try to use a CASE like in this answer: SQL - Query same column twice with different dates in where clause

edited May 23 '17 at 12:25

Community

1
1

answered Mar 07 '17 at 11:09

Gamer1120

236
1
8

SQL split columns to rows after using union

6 Answers6