• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 75
  • Last Modified:

create a semicolon delimeted list

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
PratikShah111
Asked:
PratikShah111
1 Solution
 
SharathData EngineerCommented:
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
 
funwithdotnetCommented:
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
 
Olaf DoschkeSoftware DeveloperCommented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now