SQL query help

SiliconStew

Due to a bug in our accounting software, I need a way to create a flat text file from a sql view. But the file must have header and footer rows around each set of detail rows. I normally just use a BCP query to export the view rows as is to a text file, but due to the bug I need to export them to this other format. I've tried sql reporting services and can easily add the headers and footers there, but there is no export renderer for plain text as it's displayed in the report. At best, you'd have to do a bunch of text manipulation after the fact through a custom app/script to make that work.

Here's the normal view that's used for export:

TranNumber, TranTotal, Item, Qty, Price, Location
1111, 5.00, ItemA, 1.00, 5.00, A
2222, 7.00, ItemB, 2.00, 2.00, A
2222, 7.00, ItemC, 1.00, 1.00, A
2222, 7.00, ItemD, 4.00, 0.50, A
3333, 11.00, ItemC, 0.25, 4.00, A
3333, 11.00, ItemE, 1.00, 10.00, A

The file needs to list the transactions as simple fixed width strings with header and footer rows like so:

"00" & TranNumber & TranTotal
"01" & Item & Qty & Price <-this detail line repeats for as many items as are in the transaction
"02" & Location

So the final output file for the view above would be (all numbers padded 2 leading digits, 2 trailing digits for example):

00111105.00
01ItemA01.0005.00
02A
00222207.00
01ItemB02.0002.00
01ItemC01.0001.00
01ItemD04.0000.50
02A
00333311.00
01ItemC00.2504.00
01ItemE01.0010.00
02A

What query, stored procedure, SQL Integration Services, or otherwise could I use to do this? If it can be done with only a single (however complex) query, which I doubt, that would be best. Temporary tables can be used if needed, stored procedures created, the SaleHeader and SaleDetail tables that the view was built from can be used. Ideas, solutions? Assume I know little about SQL here.

Bigity

Couldn't you use the command shell and bcp to do this in a store procedure?

http://msdn.microsoft.com/en-us/library/ms162802.aspx


Something like this? Course, this is for XP, not sure what/if anything changes for 7 and up.

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE

EXEC xp_cmdshell 'bcp "SELECT * FROM SYS.DATABASES" queryout "C:\bcp.txt" -T -c -t,'

SiliconStew

My problem isn't with how to run a stored procedure or export results, but rather what does that stored procedure need to do to format the data the way I need it.

DaxterMax

I'm going to make some general assumptions based on the data given in the example.
The view is denormalized and TranNumber, TranTotal and Location are coming from some header table or a combination of a SUM and header? As the same data is repeated for each line item.

I could be way off the mark here but maybe something like this?

http://pastebin.com/U3DTZ919

This is based solely on the example of the view table data shown, and I am calling that view "SalesView" in the example code.
If this is along the right track, it could be made more efficient by probably using the underlying tables; SalesHeader and SalesDetail
A CTE could probably be used to get the Header and Footer information in one query, rather than the two I am using.

This is all written from memory so it could be completely bust and have typos, as I don't have SQL Server installed.


Edit: That won't actually format your numbers correctly either as I'm just doing a straight conversion to string

SiliconStew

@DaxterMax That certainly looks like it might work, and impressive doing it from memory. The only thing I had to change was this:

ROW_NUMBER() OVER(ORDER BY TranNumber) --> ROW_NUMBER() OVER(PARTITION BY TranNumber ORDER BY TranNumber)

otherwise it would only put a header and footer row around the first transaction in the query. Not going to claim victory yet, lots more work and testing to do and I've been cockblocked on every seemingly minor issue on this project, but this gives some hope that I won't be doing everything manually. Thanks.

DaxterMax

Oops yeah, overlooked that otherwise the row number would just keep incrementing.
You might just need to sort out of the numeric formatting after that.

a STUFF() or RIGHT() with some CASTS might get you there. SQL isn't great with built-in functions for some of those kind of things.