hi,
I have a table and something like this.

I have a mapping table:

the final result should be:

is that possible SQL?
hi,
I have a table and something like this.

I have a mapping table:

the final result should be:

is that possible SQL?
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!
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
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
;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
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"
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;
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
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:
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.
13 people are following this question.