How to insert comma values in a single column in SQL server

Question

1.00/5 (1 vote)

See more:

, +

Hi
Can anyone tell me ,How to insert comma values in a single column in sql server and Join them using split by comma function.

What I have tried:

SQL

ID Name Dept   Sal      Techids
 1  Abc  Mech  20000    1,2,3


Techid  Techname
   1       Abc
   2       def
   3       ghi

Posted 24-Mar-17 1:45am

Member 12605293

Updated 24-Mar-17 2:01am

Add a Solution

1 solution

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)

OriginalGriff · Accepted Answer · 2017-03-24T02:01:00

Solution 1

Don't. It's a PITA to use, and though it's simple to insert the values to start with, it rapidly descends into nastiness.
Instead, have a "linking" table:

ID Name Dept   Sal      
 1  Abc  Mech  20000    


Techid  Techname
   1       Abc
   2       def
   3       ghi


DeptTechs
ID   DeptId   TechId
1      1        1
2      1        2
3      1        3

You then use a JOIN to combine information when you want to retrieve it.

SQL

SELECT d.*, t.TechName FROM Depts d
JOIN DeptTech dt ON d.ID = dt.DeptId
JOIN Techs t ON d.TechID = t.ID

That way, adding and removing techs is a trivial job: just delete the linking row.

Posted 24-Mar-17 2:01am

OriginalGriff

Comments

Member 12605293 24-Mar-17 8:13am

Hi Griff,
Thanks for your response.I dont understand the above table sir,

Can you give me sample query with these to run on my machine

OriginalGriff 24-Mar-17 8:26am

Then learn to understand it - storing data in CSV in SQL is a bad idea - the code you need to use it is a true PITA. Think about it: how do you remove a TechId from a comma separated list? You can do it, but it's truly a pain. Setting up an extra table to link the the existing tables together is really far, far easier than messing with CSV values at all.
Splitting it out to rows is complex enough:
https://www.codeproject.com/Tips/732596/Converting-comma-separated-data-in-a-column-to-row
anything else becomes a total nightmare!