February 21, 2019 at 8:51 pm
I have a table with employees. I'd like to display the email for all records horizontally placing a coma between each record in one Row.
CREATE TABLE dbo.employee (
employee_id int IDENTITY(1,1) PRIMARY KEY,
employee_name nvarchar(50) not null,
employee_email nvarchar(50) not null,
);
INSERT INTO dbo.employee
(employee_name,employee_email)
VALUES ('Bob Perdue','BP@gmail.com')
INSERT INTO dbo.employee
(employee_name,employee_email)
VALUES ('Frank Perdue','FP@gmail.com')
INSERT INTO dbo.employee
(employee_name,employee_email)
VALUES ('Dave Perdue','DP@gmail.com')
The results should include commas between each record until the last record
February 21, 2019 at 9:29 pm
netguykb - Thursday, February 21, 2019 8:51 PMI have a table with employees. I'd like to display the email for all records horizontally placing a coma between each record in one Row.
CREATE TABLE dbo.employee (
employee_id int IDENTITY(1,1) PRIMARY KEY,
employee_name nvarchar(50) not null,
employee_email nvarchar(50) not null,
);INSERT INTO dbo.employee
(employee_name,employee_email)
VALUES ('Bob Perdue','BP@gmail.com')INSERT INTO dbo.employee
(employee_name,employee_email)
VALUES ('Frank Perdue','FP@gmail.com')
INSERT INTO dbo.employee
(employee_name,employee_email)
VALUES ('Dave Perdue','DP@gmail.com')
The results should include commas between each record until the last record
This code will create a comma-separated list of emailsSELECT STUFF((
SELECT N',' + employee_email
FROM dbo.employee
FOR XML PATH('')
), 1,1,'');
February 21, 2019 at 9:37 pm
That'll do it. I have seen so many examples of doing this but with multiple rows and rolling up rows into still other rows. Blah Blah. THANK YOU !!!!
February 22, 2019 at 1:55 am
netguykb - Thursday, February 21, 2019 8:51 PMI have a table with employees. I'd like to display the email for all records horizontally placing a coma between each record in one Row.
CREATE TABLE dbo.employee (
employee_id int IDENTITY(1,1) PRIMARY KEY,
employee_name nvarchar(50) not null,
employee_email nvarchar(50) not null,
);INSERT INTO dbo.employee
(employee_name,employee_email)
VALUES ('Bob Perdue','BP@gmail.com')INSERT INTO dbo.employee
(employee_name,employee_email)
VALUES ('Frank Perdue','FP@gmail.com')
INSERT INTO dbo.employee
(employee_name,employee_email)
VALUES ('Dave Perdue','DP@gmail.com')
The results should include commas between each record until the last record
In case in future if your server is upgraded to above SQL SERVER 2017 or above there is a inbullt function called as STRING_AGG .
/*SQL SERVER 2017*/
SELECT STRING_AGG(employee_email,',') AS EMAIL FROM dbo.employee
Saravanan
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy