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
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 7 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 today
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
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.
Suggested Courses
Course of the Month5 days, 17 hours left to enroll
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.