question

ChenJian-2706 avatar image
0 Votes"
ChenJian-2706 asked EchoLiu-msft commented

SQL Translation for value

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?

sql-server-transact-sql
image.png (1.7 KiB)
image.png (2.9 KiB)
image.png (4.2 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

0 Votes 0 ·
TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ChenJian-2706 avatar image
0 Votes"
ChenJian-2706 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered
 ;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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ChenJian-2706 avatar image
0 Votes"
ChenJian-2706 answered

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"

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered

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;
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered

You are correct. The built-in split function String_Split() does not preserve order. To do that, you must write a user function to do the split. One such function that maintains the order would be

 CREATE FUNCTION dbo.Split (@DelimitedString nvarchar(max), @Delimiter nvarchar(max))
 RETURNS table
 /* Use Option(MaxRecursion 0) in queries that call this function if 
    there can be more than 99 delimited values in @DelimitedString */
 AS
 RETURN (
     WITH Pieces (ID, start, stop) AS (
       SELECT CAST(1 AS bigint), CAST(1 AS bigint), CAST(CHARINDEX(@Delimiter, @DelimitedString) AS bigint)
       UNION ALL
       SELECT ID + 1, CAST(stop + DATALENGTH(@Delimiter)/2 As bigint), CAST(CHARINDEX(@Delimiter, @DelimitedString, stop + DATALENGTH(@Delimiter)/2) AS bigint)
       FROM Pieces
       WHERE stop > 0
     )
     SELECT ID,
       SUBSTRING(@DelimitedString, start, CASE WHEN stop > 0 THEN stop-start ELSE LEN(@DelimitedString) END) AS Element
     FROM Pieces
   )
 GO

But given what you have described as your desired result, there is another question. You say if APPLE occurs multiple times you only want it once. But now since you only want it to occur once, what do you do if you have 'A B D'? Do you want Apple first or last? I'm going to assume you want it first. In that case, after creating the function, you would do

 ;With cte As
 (Select m.id, ma.Item, Min(s.ID) As ElementID
 From #Main m
 Cross Apply dbo.Split(m.Value, ' ') s
 Inner Join #Mapping ma On s.Element = ma.MapID
 Group By m.id, ma.Item)
 Select Id, STRING_AGG(Item, ',') Within Group(Order By ElementID)
 From cte
 Group By Id;

If you want the last one, just change Min(s.ID) in the above code to Max(s.ID).
Tom

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @ChenJian-2706,

Welcome to the microsoft TSQL Q&A forum!

Please also check:

 Create Table #Main(Id int, Value varchar(20));
 Insert #Main(Id, Value) Values(1, 'A'), (2, 'A B'),(3,'A B C'),(4,'A C')
 Create Table #Mapping(MapID char(1), Item varchar(20));
 Insert #Mapping(MapID, Item) Values('A', 'Apple'), ('B', 'Banana')
 , ('C', 'Cherry'), ('D','Apple');
    
 CREATE FUNCTION SplitStr(@Sourcestr VARCHAR(8000), @Seprate VARCHAR(100))     
 RETURNS @result TABLE(F1 VARCHAR(100))     
   AS       
    BEGIN     
    DECLARE @sql AS VARCHAR(100)     
   SET @Sourcestr=@Sourcestr+@Seprate       
   WHILE(@Sourcestr<>'')     
   BEGIN     
     SET @sql=left(@Sourcestr,CHARINDEX(' ',@Sourcestr,1)-1)     
     INSERT @result VALUES(@sql)     
      SET @Sourcestr=STUFF(@Sourcestr,1,CHARINDEX(' ',@Sourcestr,1),'')     
    END     
    RETURN  
    END
 GO
    
 ;WITH cte
 as(SELECT * 
 FROM #main s 
 CROSS APPLY SplitStr(S.Value,' ') V)
    
 SELECT ID,STRING_AGG(Item ,',') [Value]
 FROM cte c
 JOIN #mapping m
 ON c.F1=m.MapID
 GROUP BY ID
    
 DROP FUNCTION SplitStr

Output:
124858-image.png

If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




image.png (2.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.