Combining item and it's category into single column

Question

I'm in need of combining two columns into one in a specific way.

Here are the columns.

Column A (Items) Column B (Category)
Item 1           Category A
Item 2           Category A
Item 3           Category B
Item 4           Category B
Item 5           Category C
Item 6           Category C
...

What i want to achieve is something like this

Column AB
Category A
Item 1
Item 2
Category B
Item 3
Item 4
Category C
Item 5
Item 6

Please advise as i have a feeling that there is a simple resolution for this issue i just can't find it. Thanks !

No, its always a different number i have 4 items in category A 1 item in B 1 item in C and 5 in D — Redel, Jun 11 '19 at 09:30

Krishna · Accepted Answer · 2019-06-10T13:51:37.917

1

Try this

with wt1
as(
select distinct t2.col2 as col1,t2.col2
from tst t2
union all
select t1.col1,t1.col2
from tst t1
)
select col1
from wt1
order by col2,col1;

Output:

COL1
A
item1
item2
B
item3
item4
C
item5
item6

edited Jun 10 '19 at 13:51

answered Jun 10 '19 at 13:35

Krishna

471
2
7

Robert Kock · Answer 2 · 2019-06-10T14:11:19.920

If I understood well, you want all items plus the distinct categories.
The result should be ordered by the category first and then by the item.

You could try something like this:

SELECT ColumnAB
FROM   (
        SELECT columnB AS Category
               columnA AS ColumnAB,
               2       AS ColumnType
        FROM   my_table
        UNION ALL
        SELECT DISTINCT
               columnB AS Category
               columnB AS ColumnAB,
               1       AS ColumnType
        FROM   my_table
        ORDER BY Category,ColumnType,ColumnAB
       )

Combining item and it's category into single column

2 Answers2