SQL Translation for value

Question

hi,

I have a table and something like this.

I have a mapping table:

the final result should be:

is that possible SQL?

Answer 1

One way

Create Table #Main(Id int, Value varchar(20));
Insert #Main(Id, Value) Values(1, 'A'), (2, 'A B');
Create Table #Mapping(MapID char(1), Item varchar(20));
Insert #Mapping(MapID, Item) Values('A', 'Apple'), ('B', 'Banana'), ('C', 'Cherry');
Select m.Id, STRING_AGG(ma.MapID, ',') 
From #Main m
Cross Apply string_split(m.Value, ' ') s
Inner Join #Mapping ma On s.value = ma.MapID
Group By m.Id;

Tom

Answer 2

This is great. Is it possible to do Distinct Value?
if A and D are all mapped to Apple, can it just be "Apple", instead of "Apple,Apple"?

Create Table #Main(Id int, Value varchar(20));
Insert #Main(Id, Value) Values(1, 'A'), (2, 'A D');
Create Table #Mapping(MapID char(1), Item varchar(20));
Insert #Mapping(MapID, Item) Values('A', 'Apple'), ('B', 'Banana'), ('C', 'Cherry'), ('D','Apple');

Select m.Id, STRING_AGG(ma.Item, ',')
From #Main m
Cross Apply string_split(m.Value, ' ') s
Inner Join #Mapping ma On s.value = ma.MapID
Group By m.Id;
drop table #main
drop table #mapping

Answer 3

;With cte As
(Select Distinct m.id, ma.Item
From #Main m
Cross Apply string_split(m.Value, ' ') s
Inner Join #Mapping ma On s.value = ma.MapID)
Select Id, STRING_AGG(Item, ',')
From cte
Group By Id;

Tom

Answer 4

hmm.. I noticed that sometimes the order of values are not the same when I run the large data set.
I guess the function of string_split doesn't keep track of the first item or second item.

for example, if I have "A B", it translates to "Banana,Apple", sometimes it's "Apple,Banana"

Answer 5

Since the output rows from STRING_SPLIT() might be in any order, you cannot use it if you want your results to be ordered as the value in the #Main.Value column. Try this:

;WITH CTE AS (
    SELECT DISTINCT m.Id, ma.Item
    FROM #Main AS m
    CROSS APPLY (
        SELECT Split.a.value('.', 'NVARCHAR(MAX)') AS [Value]
        FROM (
            SELECT CAST('<X>' + REPLACE(m.Value, ' ', '</X><X>') + '</X>' AS XML) AS String
        ) AS a
        CROSS APPLY String.nodes('/X') AS Split(a)
    ) AS s
    INNER JOIN #Mapping ma ON s.value = ma.MapID
)

SELECT Id, STRING_AGG(Item, ',') AS Value 
FROM CTE 
GROUP BY Id;