SQL transpose table

Question

I have in a table data like in the following picture:

And I want something like this:

I try to insert first id, nr_1 and lbl_1 and then using:

MERGE INTO ...

WHEN MATCHED THEN UPDATE SET...

WHEN NOT MATCHED THEN INSERT..

But doesn't seems to work.

Has anyone a better solution to achieve the goal without using MERGE?

Do you want "something like this" to be a query output, or an actual table structure, which is what your MERGE statement is implying. If so - an actual table structure - then I'd _strongly_ urge you to reconsider your design. That "something like this" is fine for a report, but violates just about every principle of rdbms design. Look up "data normalization". — EdStevens, Oct 28 '20 at 17:03
Yes, indeed will be used for a query in order to generate a report. I use MERGE on a temporary table to check if is possible. For the moment I don't have any idea about the proper way of doing. Thanks! — NoUserName, Oct 28 '20 at 17:20
[This is](https://stackoverflow.com/questions/15297809/sql-transpose-full-table) what transposition means, consider editing the title — darw, Oct 28 '20 at 17:23
I don't get the part of merge. Do you want a query to do so or ? What are you trying to do with merge ? — Sujitmohanty30, Oct 28 '20 at 17:34
Check out https://stackoverflow.com/questions/4841718/oracle-sql-pivot-query — Sam M, Oct 28 '20 at 17:46
Well, if "something like this" is a report layout, then don't waste your time beating your head against a wall trying to force that into a table, which is what MERGE does. Tables should be designed to standard normalization rules, not to map directly to a given report. Just concentrate on writing the SELECT query to produce the report. As others have asked, please specify what rdbms product (oracle, mysql, MS Sql Server?) you are working with, as there is no 'universal' solution. — EdStevens, Oct 28 '20 at 17:53
Oracle Database 11g. With MERGE I try to create a temp table (it will be preferable to have a temp table with 7 columns to create the report, but a query will do the trick also). Thanks! — NoUserName, Oct 28 '20 at 18:25

darw · Answer 1 · 2020-10-28T19:54:30.703

You could achieve something like that using listagg that would put the values in a single column:

select distinct
  id,
  listagg(nr || '    ' || lbl, '     ')
    within group (order by nr)
    over (partition by id) as "nr_1 lbl_1 nr_2 lbl_2 nr_3 lbl_3"
from a
order by id

        ID nr_1 lbl_1 nr_2 lbl_2 nr_3 lbl_3
---------- --------------------------------
      1001 x    a     y    b     z    c
      1002 x    a     z    c
      1003 z    c

Gaurav Kelwadkar · Answer 2 · 2020-10-28T21:05:40.617

0

There are numerous ways of doing it using Oracle's case,decode,liastagg,pivot,sys_connect_by_path,xmlagg funtions.Here I used decode below to achieve the output.

SELECT  id,
MAX(DECODE ( rnk , 1, nr )) nr1,MAX(DECODE ( rnk , 1, lbl )) lbl1,
MAX(DECODE ( rnk , 2, nr )) nr2,MAX(DECODE ( rnk , 2, lbl )) lbl2,
MAX(DECODE ( rnk , 3, nr )) nr3,MAX(DECODE ( rnk , 3, lbl )) lbl3
FROM
(select pop.*,dense_rank() over (order by nr,lbl) as rnk from (
SELECT  id , nr, lbl,
row_number() OVER ( partition by id  order by nr) rn
FROM  tabd) pop order by id,rn)
GROUP BY id;

Click for Query Output

edited Oct 28 '20 at 21:05

answered Oct 28 '20 at 20:48

Gaurav Kelwadkar

13
4

Thanks! Indeed this is working with oracle but now I see that for UT it fails since it uses an old H2 version, that doesn't support function dense_rank. – NoUserName Oct 29 '20 at 07:30

SQL transpose table

2 Answers2