Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

create a semicolon delimeted list

Posted on 2016-09-01
3
Medium Priority
?
69 Views
Last Modified: 2016-09-07
I want to insert into a table a semicolon delimeted values.

table 1 : userid
row 1      prs
row 2      ppp

so I do a select on table

select userid from table1

the resultset that i should get should be : prs;ppp

which I will go ahead and insert in table2 useridlist column

table2
                   useridlist
row1        prs;ppp
0
Comment
Question by:PratikShah111
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 41780988
try like this.
declare @table table (userid varchar(100))
insert @table values ('prs'),('ppp')
select  STUFF(( SELECT ','+CONVERT(VARCHAR,userid)
                FROM  @table 
                 FOR XML PATH('')), 1, 1, '') useridlist
-- prs,ppp

Open in new window

0
 
LVL 12

Expert Comment

by:funwithdotnet
ID: 41781005
Mine looked almost identical ...
SELECT TOP (5) p.Name AS ProductName
, STUFF((SELECT TOP (5) ', ' + CAST(t.TransactionID AS VARCHAR(20)) 
FROM [AdventureWorks2014].[Production].[TransactionHistory] t 
WHERE T.ProductID = p.ProductID FOR XML PATH('')), 1, 1, '') as TransactionIds 
FROM [AdventureWorks2014].[Production].Product p 

Open in new window

FOR XML PATH
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 41781058
Such data is a bad idea, if you want to store userlists into a database, you'd have this design:

a) Users
ID, NAME

b) UserLists
ID, NAME

c) UsersInUserlists
ID, UserListID, UserID

To get a list of users:

SELECT Users.* FROM UserLists 
INNER JOIN UsersInUserLists On UsersInUserLists.UserListID = UserLists.ID
INNER JOIN Users ON Users.ID = UsersInUserLists.UserID
WHERE UserList.Name = 'list name' -- or if you know an ID value: WHERE UserList.ID = 3

Open in new window


This type of design is not just a theoretical idealized form of storing data. It is best practice. The UsersInUserlists can also be called an n:m relation, it's there to assign users to userlists or vice versa assign userlists to users.

Bye, Olaf.
1

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question