How to Structure Data table/fields when using Multiple Select Drop Down

Question

How to Structure Data table/fields when using Multiple Select Drop Down

rjjh78

SSCrazy

Points: 2787
More actions
March 13, 2021 at 12:08 pm

#3857308
Hello,
I am interested in using a multi-select drop down on an application interface and am looking to understand how to handle this at the database level to (1) Store the data, (2) Query the data. Below is an example of the concept.
I have a drop down with multiple check boxes that asks, "Please select your favorite Fruits:"
```
-- DROP TABLE #dd
CREATE TABLE #dd (FID int, FName varchar(10))
INSERT INTO #dd (FID, Fname) VALUES (1,'Apple')
INSERT INTO #dd (FID, Fname) VALUES (2,'Banana')
INSERT INTO #dd (FID, Fname) VALUES (3,'Blueberry')
INSERT INTO #dd (FID, Fname) VALUES (4,'Cherry')
INSERT INTO #dd (FID, Fname) VALUES (5,'Grape')
INSERT INTO #dd (FID, Fname) VALUES (6,'Kiwi')
INSERT INTO #dd (FID, Fname) VALUES (7,'Orange')
INSERT INTO #dd (FID, Fname) VALUES (8,'Pineapple')
INSERT INTO #dd (FID, Fname) VALUES (9,'Strawberry')
SELECT * FROM #dd
```
When the user select their favorite Fruits, I am currently stringing together all selections.
```
-- DROP TABLE #u
CREATE TABLE #u (UserID int, FID varchar(100))
INSERT INTO  #u (UserID, FID) VALUES (100, '1,4,5,6')
INSERT INTO  #u (UserID, FID) VALUES (200, '2,4')
INSERT INTO  #u (UserID, FID) VALUES (300, '7,8,9')
SELECT * FROM #u
```
Is this the correct way to handle this? Or is there a much more optimal way to handle this?
If yes, how can a I parse the selected options so that I can return a list of selections like this:
UserID 100: Apple, Cherry, Grape, Kiwi
UserID 200: Banana, Cherry
UserID 300: Orange, Pineapple, Strawberry
Many thanks in advance!

March 14, 2021 at 6:42 pm

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 1

It would be a lot easier to do using STRING_AGG() in SQL Server 2017 and up but, since this is a 2014 thread, we'll have to do it the old way.

Please see the following article for how this works and see the link below that for the bit of optimization that I used in the code that follows that. And thanks a ton for posting readily consumable data that actually worked. 😀

https://www.sqlservercentral.com/articles/creating-a-comma-separated-list-sql-spackle

https://www.sqlservercentral.com/forums/topic/creating-a-comma-separated-list-sql-spackle#post-1272350

   WITH cteFID AS
(
 SELECT  src.UserID, FID = split.Item
   FROM #u src
  CROSS APPLY dbo.DelimitedSplit8k(FID,',') split
)
 SELECT  fid1.UserID
        ,FruitCSV = STUFF((--Aggregate the fruit names for each UserID
                         SELECT ','+FName
                           FROM cteFID fid2
                           JOIN #dd fruit ON fruit.FID = fid2.FID 
                          WHERE fid2.UserID = fid1.UserID
                          ORDER BY fruit.FName
                            FOR XML PATH(''),TYPE).value('(./text())[1]','varchar(8000)')
                    ,1,1,'')
   FROM cteFID fid1
  GROUP BY fid1.UserID
;

That code produces the following output...

If you actually do need the output in the exact form that you posted, a tweak to the code about with the CONCAT() function will complete the trick...

   WITH cteFID AS
(
 SELECT  src.UserID, FID = split.Item
   FROM #u src
  CROSS APPLY dbo.DelimitedSplit8k(FID,',') split
)
 SELECT  Result = CONCAT('UserID ',fid1.UserID,': ',
                    STUFF((--Aggregate the fruit names for each UserID
                         SELECT ','+FName
                           FROM cteFID fid2
                           JOIN #dd fruit ON fruit.FID = fid2.FID 
                          WHERE fid2.UserID = fid1.UserID
                          ORDER BY fruit.FName
                            FOR XML PATH(''),TYPE).value('(./text())[1]','varchar(8000)')
                    ,1,1,'')
                  )
   FROM cteFID fid1
  GROUP BY fid1.UserID
;

That code produces the following output

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 2

Ah... sorry... you can read the article about how the DelimitedSplit8K function works at the following article...

https://www.sqlservercentral.com/articles/tally-oh-an-improved-sql-8k-%e2%80%9ccsv-splitter%e2%80%9d-function

... and a serious performance enhancement to the code by Eirikur Eiriksson in the "Resources" section of his excellent article located at the following URL.

https://www.sqlservercentral.com/articles/reaping-the-benefits-of-the-window-functions-in-t-sql-2

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18377 More actions · Answer 3

Your database is not normalised.

The standard way to store this is to have two tables instead of your #u table

CREATE TABLE #User
(
    UserID int NOT NULL,
    FirstName nvarchar(40) NOT NULL,
    LastName nvarchar(40) NOT NULL,
    CONSTRAINT PK_#User PRIMARY KEY CLUSTERED (UserID)
);

GO

INSERT INTO #User (UserId, FirstName, LastName) VALUES
(100, 'Fred', 'Bloggs'),
(200, 'Joe', 'Soap'),
(300, 'Jane', 'Smith');

GO

CREATE TABLE #Fruit
(
    FID int NOT NULL,
    FruitName nvarchar(40) NOT NULL,
     CONSTRAINT PK_#Fruit PRIMARY KEY CLUSTERED (FID)
);

GO

INSERT INTO #Fruit (FID, FruitName) VALUES
(1,'Apple'),
(2,'Banana'),
(3,'Blueberry'),
(4,'Cherry'),
(5,'Grape'),
(6,'Kiwi'),
(7,'Orange'),
(8,'Pineapple'),
(9,'Strawberry');

GO

CREATE TABLE #UserFruit
(
    UserId int NOT NULL,
    FID int NOT NULL,
    CONSTRAINT PK_#UserFruit  PRIMARY KEY CLUSTERED (UserId, FID),
    CONSTRAINT FK_#UserFruit_UserId FOREIGN KEY (UserId) REFERENCES #User(UserId),
    CONSTRAINT FK_#UserFruit_FID FOREIGN KEY (FID) REFERENCES #Fruit(FID)
);

GO

INSERT INTO #UserFruit (UserId, FID) VALUES
(100,1),
(100,4),
(100,5),
(100,6),
(200,2),
(200,4),
(300,7),
(300,8),
(300,9);

GO

SELECT u.UserID,
       f.FID,
       f.FruitName
  FROM #User u
 INNER JOIN #UserFruit uf
         ON uf.UserId = u.UserID
 INNER JOIN #Fruit f
         ON f.FID = uf.FID
 ORDER BY u.UserID;

GO

DROP TABLE #User
DROP TABLE #Fruit
DROP TABLE #UserFruit

GO

You can then use the STUFF FOR XML method Jeff has shown above to display the data in the format you want.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

Ok... if we're going to go "there", then why doesn't the front-end where the "pull-down" lives do this and occasionally upload from the Fruit table to keep it in synch? Obviously, the front-end was able to convert the selections to numeric values and convert them to a CSV... why didn't it just take the names and do that instead?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 5

Jonathan AC Roberts wrote:

Your database is not normalised.
The standard way to store this is to have two tables instead of your #u table

It's a work table, Jonathan.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rjjh78 SSCrazy Points: 2787 More actions · Answer 6

Hi Many thanks both for your help on this!

For some reason, I get the message Invalid object name 'dbo.DelimitedSplit8k'. Could I need an update for this?

Also, if I want to do a simple WHERE clause if I want to check if a User like the same Fruit, do I need to use the Split as well?

For example, if I wanted to return records that only had a matching fruit like Cherries (FID 4), how can I do this with the data constructed in this format?

This reply was modified 3 years, 9 months ago by rjjh78.

Jonathan AC Roberts SSCoach Points: 18377 More actions · Answer 7

Jeff Moden wrote:

Jonathan AC Roberts wrote:
Your database is not normalised.
The standard way to store this is to have two tables instead of your #u table
It's a work table, Jonathan.

I know it's a temporary table, I thought the OP had put it there just so we could test it without creating permanent tables. Also, they did say: "I am interested in using a multi-select drop down on an application interface and am looking to understand how to handle this at the database level to (1) Store the data, (2) Query the data. Below is an example of the concept."

So it looks like rjjh78 wants a table design for how to store it.

rjjh78 SSCrazy Points: 2787 More actions · Answer 8

Hi, My apologies for any confusion.

What I am asking for help with now is the ability to compare values between two Users based on Fruit they like.

For example, using my original table:

CREATE TABLE #u (UserID int, FID varchar(100))
INSERT INTO  #u (UserID, FID) VALUES (100, '1,4,5,6')
INSERT INTO  #u (UserID, FID) VALUES (200, '2,4')
INSERT INTO  #u (UserID, FID) VALUES (300, '7,8,9')
SELECT * FROM #u

I would like to create a query that would turn UserID 100 and 200 because they both have "4" in the String.

I understand the temp table is not normalized, I was just trying to share the concept but do appreciate this was pointed out.

Historically, I have created a database field per each checkbox, so I am really trying to learn and understand how to handle the one column storing multiple values.

Thanks again!!

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 9

rjjh78 wrote:

Hi Many thanks both for your help on this!
For some reason, I get the message Invalid object name 'dbo.DelimitedSplit8k'. Could I need an update for this?

See my post right after the code post where I tell you where to get dbo.DelimitedSplit8k from.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rjjh78 SSCrazy Points: 2787 More actions · Answer 10

I studied the article and have my sample data, and real project working now, many thanks!!