How do I concatenate strings from multiple strings?
I have a table with the following columns:
- PRODUCT
- YEAR_UPDATED
Sample data:
PROD1,2017 PROD1,2015 PROD2,2014 PROD3,2017
How can I get a list when each product has been updated? Something like:
PRODUCT,2017,2016,2015,2014,etc PROD1,Y,N,Y,N PROD2,N,N,N,Y PROD3,Y,N,N,N
or
PROD1,2017,2015 PROD2,2014 PROD3,2017
Oracle DB
Thank!
+3
source to share
2 answers
I am assuming the name of the table Products
, change it to whatever your table name is.
Oracle
You achieve this by using LISTAGG
function .
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 you can do this.
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
If you want to quickly test it, you can try this (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
+3
source to share
Assuming you have id and year columns in your table:
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
source to share