如何从多个行连接字符串?

I have a table with the following columns:

我有一个表，列有以下列:

PRODUCT
产品
YEAR_UPDATED
YEAR_UPDATED

Example data:

示例数据:

PROD1,2017
PROD1,2015
PROD2,2014
PROD3,2017

How can I get a list of when each product was updated? Something like:

如何获得每个产品更新的时间列表?喜欢的东西:

PRODUCT,2017,2016,2015,2014,etc
PROD1,Y,N,Y,N
PROD2,N,N,N,Y
PROD3,Y,N,N,N

或

PROD1,2017,2015
PROD2,2014
PROD3,2017

Oracle DB

Oracle数据库

Thanks!

谢谢!

2 个解决方案

#1

I am assuming the table's name is Products, change it to whatever your table's name is.

假设该表的名称是product，将其更改为该表的名称。

Oracle

甲骨文

You achieve it by using the LISTAGG function.

通过使用LISTAGG函数实现它。

select p.Product || ', ' || listagg(p.YEARUPDATED,',') within group (order by p.YEARUPDATED)
from Products p
group by p.Product;

If you are using SQL Server, this is how you can do it.

如果您使用的是SQL Server，那么您可以这样做。

select p.Product + ', ' + stuff((select ', '+ cast(tp.YearUpdated as varchar(4)) from Products tp where p.Product = tp.Product
          FOR XML PATH('')) , 1, 2, '')
from Products p
group by p.Product

In case you want to quickly test it, you can try this out (using an in-memory table).

如果您想要快速测试它，可以尝试一下(使用内存表)。

declare @Products table (Product varchar(50), YearUpdated int);

insert into @Products values ('Product 1', 2000);
insert into @Products values ('Product 1', 2001);
insert into @Products values ('Product 1', 2002);
insert into @Products values ('Product 1', 2003);
insert into @Products values ('Product 2', 2010);
insert into @Products values ('Product 2', 2011);
insert into @Products values ('Product 4', 2012);
insert into @Products values ('Product 4', 2013);
insert into @Products values ('Product 4', 2015);
insert into @Products values ('Product 3', 2005);

select p.Product + ', ' + stuff((select ', '+ cast(tp.YearUpdated as varchar(4)) from @Products tp where p.Product = tp.Product
          FOR XML PATH('')) , 1, 2, '')
from @Products p
group by p.Product

#2

Assuming you have id and year columns in your table :

假设您的表中有id和year列:

   select cast ( t1.id as  varchar) + ',' + ( case when  t1.rn2 = 1   then  '2015' else '' end )
+ 
   ( case when  t1.rn2 = 2  then  '2015,2016 ' else  '' end ) + 
   ( case when  t1.rn2 =  3 then  '2015,2016,2017' else '' end ) 
 from 
  (select distinct  yourTBL.id , max(yourTBL.rn)
   over ( partition by yourTBL.id order by yourTBL.year rows BETWEEN  UNBOUNDED PRECEDING 
        AND UNBOUNDED following ) as rn2
 from (select id ,  year ,  
      row_number()over (partition by id order by year) as rn from yourTBL ) t) t1

#1