SQL Translation for value

MAXP 1 Reputation point
2021-08-19T17:36:36.57+00:00

hi,

I have a table and something like this.

124756-image.png

I have a mapping table:

124773-image.png

the final result should be:

124699-image.png

is that possible SQL?

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,562 questions
{count} votes

7 answers

Sort by: Most helpful
  1. Tom Cooper 8,466 Reputation points
    2021-08-19T18:42:25.833+00:00

    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

    0 comments No comments

  2. MAXP 1 Reputation point
    2021-08-19T19:26:48.25+00:00

    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

    0 comments No comments

  3. Tom Cooper 8,466 Reputation points
    2021-08-19T19:57:34.897+00:00
    ;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

    0 comments No comments

  4. MAXP 1 Reputation point
    2021-08-19T20:22:51.207+00:00

    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"

    0 comments No comments

  5. Guoxiong 8,126 Reputation points
    2021-08-19T20:43:46.16+00:00

    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;
    
    0 comments No comments