How to get duplicate columns in multiple columns and single row in SQL

Question

Below is the original table

+----------+---------+
| Trade    | Key     |
+----------+---------+
|   A      |       1 |
|   A      |       2 |
|   A      |       3 |
|   B      |       1 |
|   B      |       2 |
|   B      |       3 |
+----------+---------+

Below is the results i need

+----------+---------+---------+---------+
| Trade    | Key1    | Key2    | Key3    |
+----------+---------+---------+---------+
|   A      |       1 |       2 |       3 |
|   B      |       1 |       2 |       3 |
+----------+---------+---------+---------+

Any pointers to the SQL code is appreciated.

Thanks in advance

Sarge

Your question is unclear. The values are 1/2/3 and these appear twice in the results -- as column names and values. Which are which? — Gordon Linoff, Jul 16 '21 at 15:47
Yes, that is the requirement, i need all keys for a single trade in one row. Each trade has multiple keys, but i need all the keys for a trade in one row. — Sarge1984, Jul 16 '21 at 15:52
Looks like you want a dynamic sql. This is vendor specific. Tag the question with your DBMS. — Serg, Jul 16 '21 at 15:58
Nope, I dont need a dynamic sql. I need if anything could be done on Joins and Ranks. — Sarge1984, Jul 16 '21 at 16:01
Naming columns like that would definitely require dynamic sql, as suggested by Serg. Do share any other solution if you find one. Looking forward to this. — Pushpendu, Jul 16 '21 at 16:45

score 0 · Answer 1 · answered Jul 16 '21 at 16:48

Look for PIVOT. It is sql function that allows you to do exactly what you need.

The downside with PIVOT is that not all DBs support it. Make sure your does.

Look at the following answer for further explanation: https://stackoverflow.com/a/15931734/16462128

score 0 · Answer 2 · answered Jul 16 '21 at 18:26

I think you want conditional aggregation like this:

select trade,
       max(case when seqnum = 1 then key end) as key_1,
       max(case when seqnum = 2 then key end) as key_2,
       max(case when seqnum = 3 then key end) as key_3
from (select t.*,
             row_number() over (partition by trade order by key) as seqnum
      from t
     ) t
group by trade;

You will need to explicit list the number of columns to be sure you get all keys for all trades (your example data has three).

How to get duplicate columns in multiple columns and single row in SQL

2 Answers2