January 27, 2017 at 9:59 am
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
January 27, 2017 at 10:04 am
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
January 27, 2017 at 10:49 am
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