Export to destination file - multiple rows to one

Question

Post reply

Export to destination file - multiple rows to one

buddy_bell

SSC Rookie

Points: 26
More actions
December 12, 2011 at 8:46 am

#99503

I have a table of question answers I need to export to a text file.
The table looks like this:
ID
Qst_ID
Qst_Ans
if ID 001 has 2 answers and ID 002 has three answers I need the output to look like this:
"001","Q1","a","Q2","b"
"002","Q1","a","Q2","b","Q3","c"
I do know how to pivot the table and then output but to do that my output looks like this:
"001","Q1","a","Q2","b","",""
"002","Q1","a","Q2","b","Q3","c"
the ,"","" is at the end of the ID 001 row because of the pivot having a Q3 column for ID 002. I need to have the nulls not show on the output.
Any suggestions would be greatly appreciated

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

John Mitchell-245523

SSC Guru

Points: 148809

December 12, 2011 at 8:55 am

#1420128

How are you doing the export?

John

buddy_bell SSC Rookie Points: 26 More actions · Answer 2

Currently using SSIS to export to a flat file. The pivoted data is my source and using flat file destination.

Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

I haven't done all that much flat-file exporting with SSIS. But aren't there all sorts of options that you can configure in terms of the output file? If the worst comes to the worst, you could write a VB or PowerShell script to get rid of those stray empty values.

John

Sergiy SSC Guru Points: 110209 More actions · Answer 4

You nay use a "concatenate" function to put all the answers into a single varchar value, including all quotes and commas.

After that you just export that 2 columns table to a tet file.

I bet nobody could tell commas and quotes placed by SSIS from those coming from the concatenated strings.

_____________
Code for TallyGenerator

buddy_bell SSC Rookie Points: 26 More actions · Answer 5

My issue is if there 2 rows (questions and answers) for one ID and 3 rows for another ID. I feel I need to somehow loop through and write individually so I don't have the blank at the end of the ID with only 2 answers. i.e. table has:

ID LOC Qst_ID Ans_Txt

001 100 1 A

001 100 2 B

002 200 1 A

002 200 2 B

002 200 3 C

txt file result needs to look like this:

"001","100","1","A","2","B"

"002","200","1","A","2","B","3","C"

and not like this:

"001","100","1","A","2","B","",""

"002","200","1","A","2","B","3","C"

which happens if I pivot the table and export to txt

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 6

buddy_bell (12/14/2011)
My issue is if there 2 rows (questions and answers) for one ID and 3 rows for another ID. I feel I need to somehow loop through and write individually so I don't have the blank at the end of the ID with only 2 answers. i.e. table has:
ID LOC Qst_ID Ans_Txt
001 100 1 A
001 100 2 B
002 200 1 A
002 200 2 B
002 200 3 C
txt file result needs to look like this:
"001","100","1","A","2","B"
"002","200","1","A","2","B","3","C"
and not like this:
"001","100","1","A","2","B","",""
"002","200","1","A","2","B","3","C"
which happens if I pivot the table and export to txt

I'll make you a deal... Read the first link in my signature line below for how to post readily consumable data and I'll show you how to easily do this in a stored procedure that SSIS can call.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110209 More actions · Answer 7

Or read this article:

http://www.sqlservercentral.com/articles/Test+Data/61572/

It's from the same author, so should be as useful.

🙂

Check the outcome generated in The "Hog" Test.

Just export 2 columns generated by the query to comma delimited file and you're done.

If you need another delimiter change the function accordingly.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 8

Sergiy (12/20/2011)
Or read this article:
http://www.sqlservercentral.com/articles/Test+Data/61572/
It's from the same author, so should be as useful.
🙂
Check the outcome generated in The "Hog" Test.
Just export 2 columns generated by the query to comma delimited file and you're done.
If you need another delimiter change the function accordingly.

🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

buddy_bell SSC Rookie Points: 26 More actions · Answer 9

thanks Jeff. I did read the etiquette article (shortly after my post unfortunately). Def plan to create better posts moving forward. Reading other links today.

Thanks Again