Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.
Course Of The Month
8 days, 4 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
create a semicolon delimeted list
Posted on 2016-09-01
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
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
[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
Active 3 days ago
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
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
Active 1 day ago
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:
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.
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
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.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Introduction
SQL Server Integration Services can read XML files, that’s known by every BI developer. (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.)
But how far can you go? When does the XML Source component become …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses
Course of the Month8 days, 4 hours left to enroll
729 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.