How to insert and retreive multiple values in single column in Sql server with comma separation ?

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

I have a SQL table MENU with columns

SQL

Menu_id,Menu_Name,Rate

.
In the front end user can select n menus at their choice in checkboxes(CheckBoxList). I have written code to store all the menuId's for a user in a single column MenuId separated by commas (Tbl_Order: Order_Id,Customer_Id,MenuId).
Is it a good way to store these values like this in Sql server column?
Later I also need to find what all menus a customer has ordered. So how to Take individual ID's from comma separated MEnuID column?

Give me a solution soon.

Posted 19-Sep-14 0:03am

KALYANI BIBIN

Updated 19-Sep-14 0:11am

George Jonsson

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

George Jonsson · Answer 1 · 2014-09-19T00:15:00

Solution 1

My best advice is to learn how to normalize your database.
Your current design will give you lots of headaches in the future.

Database normalization[^]

3 Normal Forms Database Tutorial[^]

Database Normalization Basics[^]

Posted 19-Sep-14 0:15am

George Jonsson

Comments

CHill60 4-Oct-14 12:44pm

Balanced that 1 with a +5. Very good advice.

George Jonsson 4-Oct-14 22:40pm

Thanks. Don't think the OP liked the answer much, though.

Salman622 · Answer 2 · 2014-09-23T02:18:00

Solution 4

try this

Select Menu_id+','+Menu_Name+','+Rate from Menu

Posted 23-Sep-14 2:18am

Salman622

Comments

George Jonsson 23-Sep-14 8:48am

It is a bad idea to store comma separated data in a column.

KALYANI BIBIN 24-Sep-14 1:09am

if i have a table named HOBBY having hobbyId,Hobby_name.
Along with userRegistration i want to store their Hobbies also in USERDetails Table. so what is the best way,to store multiple hobbies selected for single person?

Salman622 24-Sep-14 4:10am

in hobby table Create one column Namely UserRegistrationID assign foreign key to it and in User Details Use ID as Primary key

when fetching data for particular user Use join
structure will be fine and Later there will be no permutations and Combinations

Prasanth Radhakrishanan · Answer 3 · 2014-09-23T02:11:00

we want to concatenate one column row values into single row we need to write query like this

SQL

DECLARE @name NVARCHAR(MAX)
SELECT @name = COALESCE(@name + ',', '') + UserName FROM UserDetails
SELECT UserNames = @name

If you observe above query I used function COALESCE it returns the first non-null expression among its arguments.

SQL

Another way

If we want to concatenate rows of column we can write query like as shown below

DECLARE @name NVARCHAR(MAX)
SET @name=''
SELECT @name = @name +','+UserName  FROM UserDetails
SET @name = Substring(@name, 2, (len(@name)))
SELECT @name as UserNames

check this