Merging multiple results rows after STRING_SPLIT on left join-CodePudding

Assume an application where a user can select an items from a table fruits to add to their favorite_fruits column stored in the table users. When adding an item to favorite_fruits, we want to add the fruit_id of the item in fruits rather than the name and keep them comma delimited. When we query a list of users in the application, we want to receive a single row for each user and display their favorite_fruits as name. To get halfway there I need a left join on the fruits table and cross apply string_split on the favorite_fruits column to receive the text/name values. At this point I will receive a row for each fruit listed in the favorite_fruits. To get the last half I need to then merge rows with common user_id. See below for the desired result.

users table

user_id	favorite_fruits
1	3,6
2	2,5

fruits table

fruit_id	name
1	banana
2	strawberry
3	blueberry
4	papaya
5	orange
6	apple

Desired results

user_id	favorite_fruits
1	blueberry,apple
2	strawberry,orange

I understand that using comma delimited values will break normalization and go against 1NF. I would like to see if this example is feasible and why.

CodePudding user response：

If the sequence is important, you can use JSON to split the string where [key] will be the proper order.

Example or dbFiddle

Select A.user_id
      ,B.*
 From users A
 Cross Apply (
                Select favorite_fruits=string_agg(B2.[Name],',') within group (order by [key])
                 From  OpenJSON( '["' replace(string_escape([favorite_fruits],'json'),',','","') '"]' ) B1
                 Join fruits B2 on B2.[fruit_id] = B1.Value
             ) B

Results

user_id favorite_fruits
1       blueberry,apple
2       strawberry,orange

Note:

If [favorite_fruits] is truly a string of numbers, the OpenJSON portion can look like this

From  OpenJSON( '[' [favorite_fruits] ']' )

CodePudding user response：

Do not store denormalized data. Normalize your database, and store each value as a separate row in a many:many join table.

So you need a new user_favoriteFruit table (note how the primary key is a composite key)

CREATE TABLE user_favoriteFruit (
    user_id int NOT NULL REFERENCES users (user_id),
    fruit_id int NOT NULL REFERENCES fruits (fruit_id),
    PRIMARY KEY (user_id, fruit_id)
);

INSERT user_favoriteFruit (user_id, fruit_id) VALUES
(1, 3),
(1, 6),
(2, 2),
(2, 5);

You can then get your expected results using aggregation, like this

SELECT
  uf.user_id,
  favorite_fruits = STRING_AGG(f.name, ',')
FROM user_favoriteFruit uf
JOIN fruits f ON f.fruit_id = uf.fruit_id;
GROUP BY
  uf.user_id;

If ordering is important then add another ordering column to the join table.