Odd Result Set to CSV Request - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Odd Result Set to CSV Request

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-09-22 : 09:08:21

I need to create a result set which looks like the following:

"hardcodedHeader1", "hardcodedHeader2"
"hardcodedTitle1", "hardcodedTitle1A", "hardcodedTitle1B",
column1value, column2value, column3value, column4value, column5value
"hardcodedTitle2", "hardcodedTitle2A", "hardcodedTitle2B",
column1value, column2value, column3value, column4value, column5value
etc....

Ultimately, this result set will be saved as a CSV for an application to read. I am reading a single table. The fact that the result set needs to display with an uneven number of columns in the result set is messing me up.

Hope this makes sense...

Thanks
Q

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-22 : 09:59:27

If some of your values are numeric, you'll need to cast 'em

With results (c1, c2, c3, c4, c5, orderby)
AS (
	select 'hardcodedHeader1', 'hardcodedHeader2', null, null, null, 0
	union all
	select 'hardcodedTitle1', 'hardcodedTitle1A', 'hardcodedTitle1B', null, null, 1
	union all
	select column1value, column2value, column3value, column4value, column5value, 2
	union all
	select 'hardcodedTitle2', 'hardcodedTitle2A', 'hardcodedTitle2B', null, null, 3
	union all
	select column1value, column2value, column3value, column4value, column5value, 4
)
SELECT	c1, c2, c3, c4, c5
FROM	results
ORDER BY
	orderby

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-09-22 : 10:11:46

Thanks Russell for your suggestion.

What if I do not know how many selects should be included in the WITH statement? Would a Trigger be required to accomplish this?

Also, when saving the results to CSV, the values with NULL obviously show with a comma separator. What I need to do is show the header row with just the two text strings, followed by a row for the title showing only the three text strings, followed by a row with the actual column values.

I can imagine that you are reading this and going, why....!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-22 : 10:16:15

You're better off using a tool designed for this sort of work like C#, VB.Net -- even vbscipt -- for this.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-09-22 : 10:21:23

Yeah, I was hoping I could have done it all in a SP.

Thanks again!

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-09-22 : 10:35:36

What about the first part of my last post?

"What if I do not know how many selects should be included in the WITH statement? Would a Trigger be required to accomplish this?"

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-22 : 12:38:13

I have no idea how a trigger would help. Dynamic SQL would. But again, seems the wrong tool for the job.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-09-22 : 13:10:26

Thanks Russell, I meant to say a Cursor....

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-22 : 13:14:58

A cursor isn't going to create a result set with different # of columns per row. In fact, nothing is really.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2011-09-22 : 13:34:12

Yeah, think I will use SQL and get the results with nulls, keeping the same number of columns. Then using VB so strip out the fields I do not need, finally writing out to a file.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources