Click here to Skip to main content
14,699,811 members
Please Sign up or sign in to vote.
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:

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


Techid  Techname
   1       Abc
   2       def
   3       ghi
Posted
Updated 24-Mar-17 3:01am

1 solution

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.
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.
   
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!

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900