March 17, 2011 at 12:21 pm
I need to export distinct values from multiple columns in a SQL table to a text file. However I need the output from each of the columns to appear as a comma separated string on a unique line of the file. So for example, I need to take the table:
ColA, ColB, ColC
1A, 1B, 1C
2A, 2B, 2C
1A, 2B, 3C
and write it as follows to the text file:
ColA:1A,2A
ColB:1B,2B
ColC:1C,2C,3C
The following method called from a stored procedure has worked well in previous cases when I've only needed to handle one column:
SELECT STUFF((SELECT ',' + column FROM table FOR XML PATH('')), 1, 1, '') AS concat
However, in actuality I need to perform this on 11 columns, and calling a stored procedure to do this using stuff / for xml path seems like a fairly bulky option.
Is there a graceful way to accomplish this using SSIS data flow transformations?
March 17, 2011 at 12:43 pm
pschwartzbauer (3/17/2011)
Is there a graceful way to accomplish this using SSIS data flow transformations?
No. You'll want to pretty much do this at the T-SQL level and feed it up.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 18, 2011 at 1:22 am
Craig Farrell (3/17/2011)
pschwartzbauer (3/17/2011)
Is there a graceful way to accomplish this using SSIS data flow transformations?No. You'll want to pretty much do this at the T-SQL level and feed it up.
I guess it can be done using the UNPIVOT transformation and by adding a DISTINCT clause on the SQL query that fetches the data.
And then you write some script component that puts the data in the desired format.
OK, this isn't gracefully 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 18, 2011 at 8:50 am
Koen, You had me until script component. Once you got there, it didn't feel any more graceful than doing it inside the stored procedure. Oh, well. Thanks for putting the thought into it.
I went on Craig's statement and ended up going the stored procedure route. My sp contains a series of unioned STUFF FOR XML PATH queries:
SELECT STUFF((SELECT DISTINCT ',' + columnA FROM table FOR XML PATH('')), 1, 1, '')
UNION ALL
SELECT STUFF((SELECT DISTINCT ',' + columnB FROM table FOR XML PATH('')), 1, 1, '')
...
Now I just need to write that to a file. Something about it feels a bit like duct tape and papier mache, but it's functional.
Thanks for the input.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply