Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
Course Of The Month
Become 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 4 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
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In part one, we reviewed the prerequisites required for installing SQL Server vNext.
In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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 to use the UPDATE and DELETE statements to change or remove existing data from their tables.
Make a table:
Update a specific column given a specific row using the UPDATE statement:
Remove a set of values using the DELETE s…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month6 days, 11 hours left to enroll
636 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.