Get Name instead of ID in query

Transact-SQL
1

Dear Expert,

In one of the table Field data is capturing as, this is Master Table ID separated with comma.
20,41
89,73,22,23
10
45,20

Now all these are ID instead of Name, i want to do join with Master table and get the actual names instead of that ID.

So the output would be as below
PRIN, TEMP
ABC, YHK,TRT,HGT
TRT
HHY,YUI

2

hi

please show how data will be !!!
in the tables

depending on how data is !!! we can decide approach !!

3
ActID ActName ProductName
1 Activity1 1,5,10
2 Activity2 30,10,2
3 Activity3 9
4 Activity4 10,11
5 Activity5 22
ProductID ProductName
1 ABC
2 DEF
5 GHI
10 YUA
11 HHH
30 AAA
22 CCCCC

So the ourput in first row should be as ABC,GHI,YUA

Thanks

4

What data type is productmame in activity and data type is proructid?

Why did you choose this comma delimited approach?

5

What data type is productmame in activity and data type is proructid?

Answer:- productmame nvarchar proructid int

Why did you choose this comma delimited approach?
Answer:- Data is coming from any other software in this manner only and its saved in table like that only from our implementation partner.

6 
--these are just sample table to emulate your data
if OBJECT_ID('tempdb..#products') is not null
	drop table #products


create table #products(productid int , productname varchar(50))
insert into #products
select 1, 'ABC' union
select 5, 'Chicken' union
select 10, 'Beef' union
select 4, 'Cheese' union
select 2, 'Ham'

if OBJECT_ID('tempdb..#activity') is not null
	drop table #activity;

--these are just sample table to emulate your data 
create table #activity(actid int , activityname varchar(50), productname varchar(50))
insert into #activity
select 1, 'Activity1', '1,5,10' union
select 2, 'Activity1', '2,3,4' 

--couple of ways you could do it.
;with d
as
(
	select *
	from #activity
	cross apply string_split(productname,',')  s
)
select STRING_AGG(p.productname,',') 
WITHIN GROUP (ORDER BY p.productname ASC) 
  from d
  left join #products p on d.value = p.productid
  group by d.productname
1 Like
7

@abhit_kumarb ,
Did the solution from @yosiasz solve your problem or what???

1 Like