Solved

Insert Blank Line in Output

Posted on 2007-11-29
9
801 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

856 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