Solved

Insert Blank Line in Output

Posted on 2007-11-29
9
808 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 

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

Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL page split per second is high 19 123
Help with simplifying SQL 6 54
Query to return total 6 25
What is this datetime? 1 29
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…

733 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