Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Insert Blank Line in Output

Posted on 2007-11-29
9
Medium Priority
?
821 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

604 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