how to make SQL statements in same value-CodePudding

Table:

A	B	C
QW	1	42
QW	2
QW	3	12
EW	3
EW	2
R	1	52
T	2
Y	3
UY	1	1
IO	2
PO	3
PP	4	1
IU	12
PP	5	55

expected output：

A	B	C
QW1		42
QW2
QW3		12
EW3
EW2
R	1	52
T	2
Y	3
UY	1	1
IO	2
PO	3
PP4		1
IU	12
PP5		55

In the table col[A] have repeating some same values throughout the rows. For those rows, need to make them concatenated with col[B] such as col[A] col[B] as column A, and undisplay column B

But I don't know how to make the SQL statements.Please help~

CodePudding user response：

You can use conditional logic along with COUNT() OVER () analytic function such as

SELECT A||CASE WHEN COUNT(*) OVER (PARTITION BY A)>1 THEN B END AS a,
       CASE WHEN COUNT(*) OVER (PARTITION BY A)=1 THEN B END AS b,
       c
  FROM t

Demo

CodePudding user response：

Here's one option:

Sample data:

SQL> with test (a, b) as
  2    (select 'QW', 1 from dual union all
  3     select 'QW', 2 from dual union all
  4     select 'QW', 3 from dual union all
  5     select 'EW', 3 from dual union all
  6     select 'EW', 2 from dual union all
  7     select 'R' , 1 from dual union all
  8     select 'T' , 2 from dual union all
  9     select 'Y' , 3 from dual union all
 10     select 'UY', 1 from dual union all
 11     select 'IO', 2 from dual union all
 12     select 'PO', 3 from dual union all
 13     select 'PP', 4 from dual union all
 14     select 'IU',12 from dual union all
 15     select 'PP', 5 from dual
 16    ),

Count number of appearances of each a column value

 17  counts as
 18    (select a, count(*) cnt
 19     from test
 20     group by a
 21    )

Join with CASE expression

 22  select t.a || case when c.cnt > 1 then t.b end as a,
 23         case when c.cnt > 1 then null else t.b end as b
 24  from test t join counts c on t.a = c.a;

A              B
---------- -----
QW1
QW2
QW3
EW3
EW2
R              1
T              2
Y              3
UY             1
IO             2
PO             3
PP4
IU            12
PP5

14 rows selected.

SQL>

CodePudding user response：

You can use window function row_number which assign sequential integer to each row and apply partition by clause to get smaller sets or partitions. In your case you have to partition by column A and concat column A and B if your row_number(here in this case count) greater than 1. Then you will get your expected result.

select  case when count > 1 then A || B else A end as A,
 B, C from (
        select A, B, C, ROW_NUMBER () OVER  ( PARTITION BY A) as count 
from your_table_name
 );