Solved

Insert Blank Line in Output

Posted on 2007-11-29
9
793 Views
Last Modified: 2008-09-20
I have created a stored procedure in SQL Server 2005 that queries data and creates a .csv file.  My client needs a blank space inserted after a group of companies.  I was told to use a while loop instead of a cursor.
0
Comment
Question by:ifaber601
  • 4
  • 3
9 Comments
 
LVL 8

Expert Comment

by:digital_thoughts
ID: 20376256
Could you post some of your stored procedure, mainly the section(s) that create/write to the file.
0
 

Author Comment

by:ifaber601
ID: 20376615
Here's the basics of what the stored procedure does.  Usually there is a company with many items.  Will need to group by company and when it goes to the next company, a blank row needs to be inserted before going to the next company.

For example,

Disney  1
Disney  2
Disney 3

Starbucks 21
Starbucks 199

Target 1
Target 14
Target 17


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
ALTER Procedure [dbo].[TestReport] AS
 
set transaction isolation level read uncommitted 
set nocount on
set concat_null_yields_null off
 
------------------------------------------------------------------------------------------
Create table #TestReport (CompanyID varchar(20), CompanyName varchar(100), Items varchar(20))
insert into #TestReport (CompanyID, CompanyName, Items)
 
select  
    CompanyID, 
    CompanyName,
    Items
	from company c 
    join items i on i.companyid= c.companyid     
order by Company, ItemNumber
 
-------------------------------------------------------------------------------------------
create table #TestReport_Rpt (ID int identity(1,1),ReportOutput varchar(1000))	
   insert into #TestReport_Rpt (ReportOutput) 
   values ('CompanyID, CompanyName, Items')
   Insert into #TestReport_Rpt (ReportOutput)
		Select	--distinct
            cast(CompanyID as varchar(20)) + char(44) +
            cast(CompanyName as varchar(100)) + char(44) +
            cast(Items as varchar(20)) 
		from #TestReport 
 
select ID, ReportOutput from #TestReport_Rpt order by id
 
drop table #TestReport
drop table #TestReport_Rpt

Open in new window

0
 
LVL 8

Expert Comment

by:digital_thoughts
ID: 20377038
Ok, but what is actuallly writing out to the file?
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:ifaber601
ID: 20377258
The file writes out a comma delimited file as .csv.

0
 
LVL 8

Expert Comment

by:digital_thoughts
ID: 20377385
Right, but I mean the piece of code you have shown above that basically builds the data you output, but there's not actually anything that does the file write...
0
 

Author Comment

by:ifaber601
ID: 20377421
The process the actually writes the file is huge.  That's a total separate entity.  The select statement at the bottom is picked up and then a bcp command is used to output the data.  There is a lot of reports that runs through this big process.

I wanted to know in the select statement, if a blank row could be inserted after the company name changes.
0
 

Author Comment

by:ifaber601
ID: 20377768
I decided that I would just create the first temp table.  Then after it's created, loop through the temp table looking for the change in the company names, and when there is a change, insert a blank row before the new company.
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 22530268
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql calculate reminders 11 76
Analysis of table use 7 48
Anyway to make these 2 SQL statements into one? 13 39
Addition to SQL for dynamic fields 6 38
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question