Exporting multiple rows to 1 CSV column using BCP

  • 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

  • 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

  • Thank you kindly, this does the trick!

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply