0

I'd like to join Table1 and Table2 on the Product ID to get the "Desired Table". The trick is that though the PRODUCT_ID_LIST looks like a list, it is VARCHAR. I've also tried string_split, but it is not built-in. Btw, I'm using SQL Server 2014.

Table1:

NAME|DATE|PLACE_ID|PRODUCT_ID_LIST
ABY|3/1/21|A|[1]
JON|3/1/21|A|[1,3,4]
JON|3/1/21|B|[2,4]
JON|3/1/21|C|[5]
TYLER|3/1/21|B|[1,2,3]
TYLER|3/2/21|B|[1,2,3]

Table2:

PRODUCT_ID|PRODUCT_NAME
1|Goggle
2|Ski
3|Snowboard
4|Boots
5|Helmet

Desired Table:

NAME|DATE|PLACE_ID|PRODUCT_ID_LIST|PRODUCT_NAME_LIST
ABY|3/1/21|A|[1]|[Goggle]
JON|3/1/21|A|[1,3,4]|[Goggle,Snowboard,Boots]
JON|3/1/21|B|[2,4]|[Ski,Boots]
JON|3/1/21|C|[5]|[Helmet]
TYLER|3/1/21|B|[1,2,3]|[Goggle,Ski,Snowboard]
TYLER|3/2/21|B|[1,2,3]|[Goggle,Ski,Snowboard]
Kuku233
  • 1
  • 2
  • 1
    Images of data don't help us help you. Post the data in a consumable format. Preferable DDL and DML statements, however, otherwise at least as well formatted tabular `text`. – Thom A Mar 23 '21 at 15:18
  • 1
    The real solution here, is fix your design. Don't store delimited data in your database; then the problem you have doesn't exist. – Thom A Mar 23 '21 at 15:19
  • These will answer your question, but, again, fix your design: [T-SQL split string](https://stackoverflow.com/q/10914576/2029983) and [string_agg for sql server pre 2017](https://stackoverflow.com/q/49361088/2029983) – Thom A Mar 23 '21 at 15:20
  • @Larnu Thanks for the suggesions and I'll try them out. Indeed, it's also a pain for me as I'm not the designer of the database... – Kuku233 Mar 23 '21 at 15:40

0 Answers0