select with Converting Column to csv

Question

I have two tables

tbl_Products 
ID  Keywords
1   1,2
2   1,3
3   2,3

and

tbl_Keywords 
Key_ID   Keyword
1        Keyword1
2        Keyword2
3        Keyword3

and i need result as follow

Result
ID  Keywords    keywordCSV
1   1,2     Keyword1,Keyword2
2   1,3     Keyword1,Keyword3
3   2       Keyword2

Here Keywords in tbl_Products have datatype of varchar and Key_ID have a datatype of bigint.

I try with converting Column to CSV From reference Here

But not able to get such this result.

Thanks for help

Community · Accepted Answer · 2021-01-18 12:30:34Z

3

declare @tbl_Products table(ID int, Keywords varchar(10))

insert into @tbl_Products values
(1,   '1,2'),
(2,   '1,3'),
(3,   '2,3')

declare @tbl_Keywords table(Key_ID int, Keyword varchar(10))

insert into @tbl_Keywords values
(1,        'Keyword1'),
(2,        'Keyword2'),
(3,        'Keyword3')

select P.ID,
       P.Keywords,
       stuff((select ', '+K.keyword
              from @tbl_Keywords as K
                inner join P.XKeywords.nodes('/k') as KX(N)            
                  on KX.N.value('.', 'int') = K.Key_ID
              for xml path(''), type).value('.', 'varchar(max)'), 1, 2, '') as keywordCSV
from (
      select ID,
             Keywords,
             cast('<k>'+replace(Keywords, ',', '</k><k>')+'</k>' as xml) as XKeywords
      from @tbl_Products
     ) as P

Try it here: https://data.stackexchange.com/stackoverflow/q/116544/

edited Jan 18, 2021 at 12:30

CommunityBot

11 silver badge

answered Nov 4, 2011 at 12:35

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gate Over a year ago

Thanks Eriksson. Its a perfect solution for my question

Collectives™ on Stack Overflow

select with Converting Column to csv

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related