1

I’m using the following query to generate a summary of invoices for a particular customer.

SELECT
customer_account,
store_number,
invoice_total

FROM invoices WHERE company LIKE '%Joe’s Bait Shop%';

This SQL query is used as an input in a homegrown application which executes the query against the DB, and outputs the results to an Excel spreadsheet. The output looks something like:

customer_account    store_number    invoice_total
222                 13              22.90
333                 16              56.00
444                 21              18.21

Before this spreadsheet can be sent to the customer, text or “header” information must be manually added to the file such as:

Frank’s Fish Supply
Remit To
123 First St., Wonderland, HI 12345

Is there anything I could add to the SQL query that display this text in the output, before returning the table data? Ideally, I would want the SQL query to return:

Frank’s Fish Supply
Remit To
123 First St., Wonderland, HI 12345

customer_account    store_number    invoice_total
222                 13              22.90
333                 16              56.00
444                 21              18.21

I tried using something like:

PRINT N’Frank’s Fish Supply’;
PRINT N’Remit To’;
PRINT N’123 First St., Wonderland, HI 12345’;
PRINT N’’;

SELECT
customer_account,
store_number,
invoice_total

FROM invoices WHERE company LIKE '%Joe’s Bait Shop%';

However, the results of the PRINT commands only show up in the “Messages” tab of SSMS rather than the “Results” tab, and do not appear in the Excel output when run through the homegrown application. Are there any SQL commands I could use to output this static text before the results of SELECT statement?

  • Header to be consumed by what? Did you try SELECT instead of PRINT? It's not super clear what you want the output to be. Your "such as:" looks like data, not header information, and seems to be about different columns and clauses than your query. – Aaron Bertrand Nov 24 '17 at 20:08
  • If you simply want to see this data in the "Results" tab of SSMS, then I would: Create a temp table with an identity column and a varchar(200) column (perhaps named Header); build each line out of the relevant data, and insert it into the temp table, in the order it should appear; and select the varchar column ordered by the identity column. – RDFozz Nov 24 '17 at 21:06
  • This is not trivial. As in my previous note, you probably need to generate the data in two separate queries. I'd try setting up three worksheets. Write the header data to worksheet 2; write the detail lines to worksheet 3; and set up worksheet 1 to show the data form the second two sheets. – RDFozz Nov 27 '17 at 16:26
  • Thanks for the ideas, RDFozz. I ended up finding a suitable solution in another thread and have updated my original post accordingly. – SuperSix Nov 28 '17 at 16:16
  • 1
    @SuperSix You should post the solution as an answer instead of editing your original question. You can then accept your answer and the question will be marked as "solved" without having to edit the title. – LowlyDBA Nov 28 '17 at 16:20
2

I stumbled upon this thread which contained a suitable solution. I ended using a query similar to the following to generate my desired results:

select customer_account, store_number, invoice_total
from ((select 'Frank’s Fish Supply' as customer_account, '' as store_number, '' as invoice_total, 1 as which
      ) union all
        (select 'Remit To:' as customer_account, '' as store_number, '' as invoice_total, 2 as which
      ) union all
        (select '123 First St., Wonderland, HI 12345' as customer_account, '' as store_number, '' as invoice_total, 3 as which
      ) union all
        (select '' as customer_account, '' as store_number, '' as invoice_total, 4 as which
      ) union all
        (select 'customer_account' as customer_account, 'store_number' as store_number, 'invoice_total' as invoice_total, 5 as which
      ) union all
      (select customer_account, store_number, invoice_total, 6 as which from arinvc where company LIKE '%Joe’s Bait Shop%'
      )
     ) t
order by which;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.