Exporting multiple rows to 1 CSV column using BCP

• rjjh78

  SSCrazy

  Points: 2787

  More actions

  January 27, 2017 at 9:59 am

  #326185

  Hello, I am working on a data export where I want to export my SQL query to CSV.  I have a row of data that I am trying to send to 1 CSV column and I want the data to appear as a list instead of a row with multiple commas. 

  For example, I can export a row to look like:  Boston MA, Providence RI, New York NY

  However I would like to present it so the column has the complete list present as below:

  Boston MA
  Providence RI
  New York NY

  I was thinking I could do this by using CHAR(10) as part of my ROW, but this brings the data to the next ROW instead of creating a new row within the column. 
  Is it possible to create multiple rows within on CSV column? 

  Below is my sample code:
  

  -- 1. Data

  -- DROP TABLE ##DATA

  -- SELECT * FROM #DATA

  CREATE TABLE ##DATA (ID int IDENTITY, Region varchar(50), District int, City varchar(50))

  INSERT INTO ##DATA VALUES ('North East,', '1', 'Boston, MA')

  INSERT INTO ##DATA VALUES ('North East,', '1', 'Providence, RI')

  INSERT INTO ##DATA VALUES ('North East,', '3', 'New York, NY')

  INSERT INTO ##DATA VALUES ('South East,', '4', 'Atlanta, GA')

  INSERT INTO ##DATA VALUES ('South East,', '4', 'Miami, FL')

  INSERT INTO ##DATA VALUES ('South East,', '4', 'Memphis, TE')

  INSERT INTO ##DATA VALUES ('Mid West,', '5', 'Chicago, IL')

  INSERT INTO ##DATA VALUES ('Mid West,', '6', 'Detriot, MI')

  INSERT INTO ##DATA VALUES ('South West,', '6', 'Phoenix, AZ')

  INSERT INTO ##DATA VALUES ('South West,', '7', 'Las Vegas, ND')

  INSERT INTO ##DATA VALUES ('South West,', '7', 'Albuquerque, NM')

  INSERT INTO ##DATA VALUES ('West,', '7', 'Los Angeles, CA')

  INSERT INTO ##DATA VALUES ('West,', '7', 'Portland, OG')

  -- 2. Present Data

  -- DROP TABLE ##DATAList

  -- SELECT * FROM ##DataList

  CREATE TABLE ##DATAList (District varchar(50), City varchar(1000))

  INSERT INTO ##DATAList (District, City)

  SELECT

  District,

  City = STUFF((SELECT ',' + City + CHAR(10) FROM ##DATA s WHERE s.District = b.District FOR XML PATH ('')),1, 0, '')

  FROM ##DATA b

  GROUP BY Region, District

  ORDER BY Region, District

  -- 3. Export To Excel

  DECLARE @bcpcmd nvarchar(4000)

  SELECT @bcpcmd = 'bcp "SELECT * FROM ##DataList " queryout "c:\Temp\Test_' + replace(CONVERT(varchar,getDate(),112),'/',' ') + '.csv" -c -t "," -T'

  EXEC master..xp_cmdshell @bcpcmd

• Thom A

  SSC Guru

  Points: 99137

  More actions

  January 27, 2017 at 10:04 am

  #1925394

  Have you tried wrapping your cities in Quotes?

  City = '"' + STUFF((SELECT ',' + City + CHAR(10)  + FROM ##DATA s WHERE s.District = b.District FOR XML PATH ('')),1, 0, '') + '"'

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• rjjh78

  SSCrazy

  Points: 2787

  More actions

  January 27, 2017 at 10:49 am

  #1925407

  Thank you kindly, this does the trick!