Display Values Horizontally

• netguykb

  Ten Centuries

  Points: 1310

  More actions

  February 21, 2019 at 8:51 pm

  #381551

  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
  

• DesNorton

  SSC-Insane

  Points: 24274

  More actions

  February 21, 2019 at 9:29 pm

  #2022622

  netguykb - Thursday, February 21, 2019 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
  

  This code will create a comma-separated list of emails
  SELECT STUFF((
     SELECT N',' + employee_email
     FROM dbo.employee
     FOR XML PATH('')
     ), 1,1,'');


  ---

  How to post data/code on a forum to get the best help.

• netguykb

  Ten Centuries

  Points: 1310

  More actions

  February 21, 2019 at 9:37 pm

  #2022623

  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 !!!!

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  February 22, 2019 at 1:55 am

  #2022633

  netguykb - Thursday, February 21, 2019 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
  

  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