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