sql - 如何连接多行的字符串？

Question

我有一个包含以下列的表格:

• 产品
• YEAR_UPDATED

示例数据:

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

如何获取每个产品更新时间的列表？像这样的东西:

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

甲骨文数据库

谢谢!

Answer 1

我假设表的名称是 Products，请将其更改为您的表名称。

甲骨文

您可以使用 LISTAGG function 来实现它.

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

如果您使用的是 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

如果您想快速测试它，可以试试这个(使用内存表)。

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