CREATE BLANK ROWS BETWEEN DATA
in [Programming SQL]
|
From: KAILASH SHARMA on 11 Aug 2010 05:43 I wanted to reate blank rows in my data sheet which having more than 1,00,000 nos of rows > On Tuesday, February 09, 2010 12:34 PM Rich wrote: > I need to insert a blank row between each group of rows as follows. What is > the tsql to accomplish this ? (2000 or 2005 tsql ok). > > create table #tmpA(rowID int Identity(1,1), fld1 varchar(10), fld2 > varchar(10)) > > insert into #tmpA > select 'abc', 'def' union all > select 'abc', 'def' union all > select 'bcd', 'efg' union all > select 'bcd', 'efg' union all > select 'bcd', 'efg' union all > select 'cde', 'fgh' union all > select 'cde', 'fgh' union all > select 'cde', 'fgh' union all > select 'cde', 'fgh' > > > rowID fld1 fld2 > > 1 abc def > 2 abc def > 3 > 4 bcd efg > 5 bcd efg > 6 bcd efg > 7 > 8 cde fgh > 9 cde fgh > 10 cde fgh > 11 cde fgh > > > Thanks, > Rich >> On Tuesday, February 09, 2010 12:43 PM Bob Barrows wrote: >> Rich wrote: >> >> You're going to be kicking yourself ... >> >> select '','' union all >> or >> select null,null union all >> >> depending on what you really want ... >> >> -- >> HTH, >> Bob Barrows >>> On Tuesday, February 09, 2010 12:52 PM Bob Barrows wrote: >>> Bob Barrows wrote: >>> >>> Oh! You probably need to specify the fields: >>> >>> insert into #tmpA(fld1,fld2) >>> select ... >>> >>> >>> -- >>> HTH, >>> Bob Barrows >>>> On Tuesday, February 09, 2010 1:36 PM Rich wrote: >>>> sorry, I mis-stated my question. I just added some sample data to my >>>> initial post. >>>> >>>> Here is my situation. I have a table with 300,000 records. I have grouped >>>> these records on various columns. I need to insert a blank row between each >>>> group of rows. How to do this with tsql? >>>> >>>> >>>> >>>> "Bob Barrows" wrote: >>>>> On Tuesday, February 09, 2010 2:11 PM Mark McGinty wrote: >>>>> You want to physically insert a row into this table between groups? To do >>>>> that you would have to iterate the rows and change their identity column values >>>>> to make room for your blanks. You'd also have to manage the positions of >>>>> blanks every time the data changes. That seems more than impractical to >>>>> accomplish what seems to be merely a reporting output goal. >>>>> >>>>> Have you looked at the WITH ROLLUP extension to the GROUP BY clause? It >>>>> would not quite be capable of output exactly like your example, but might be >>>>> acceptable when using your actual data. >>>>> >>>>> >>>>> -MM >>>>>> On Tuesday, February 09, 2010 2:22 PM Bob Barrows wrote: >>>>>> A table is a set of unordered rows. Order is imposed by using an ORDER >>>>>> BY clause when retrieving the data from the table via a SELECT >>>>>> statement. So, your goal of inserting "blank" rows "between" existing >>>>>> rows is certainly not achievable. The best you can do is add a column to >>>>>> contain data that can be used to provide your desired sort order when >>>>>> you retrieve your data from the table. >>>>>> Also, I am at a loss to come up with a reason for inserting "blank" rows >>>>>> into a table. Tables are intended for storing data, not non-data. >>>>>> This seems to be more of a presentation thing, it seems to me. >>>>>> >>>>>> However, using your sample data, Let's add a couple ranking columns >>>>>> (this solution will only work for sql 2005): >>>>>> >>>>>> create table #tmpA(rowID int Identity(1,1), fld1 varchar(10) >>>>>> , fld2 varchar(10), Rank1 int, Rank2 int) >>>>>> >>>>>> insert into #tmpA(fld1,fld2) >>>>>> select 'abc', 'def' union all >>>>>> select 'abc', 'def' union all >>>>>> select 'bcd', 'efg' union all >>>>>> select 'bcd', 'efg' union all >>>>>> select 'bcd', 'efg' union all >>>>>> select 'cde', 'fgh' union all >>>>>> select 'cde', 'fgh' union all >>>>>> select 'cde', 'fgh' union all >>>>>> select 'cde', 'fgh' >>>>>> >>>>>> update a >>>>>> set Rank1=rnum,Rank2=0 >>>>>> from #tmpA a join >>>>>> (select row_number() over (order by min(rowID)) rnum,fld1,fld2 >>>>>> from #tmpA group by fld1,fld2 >>>>>> ) q on a.fld1=q.fld1 and a.fld2=q.fld2 >>>>>> >>>>>> insert #tmpA(rank1,rank2) >>>>>> select Rank1,1 >>>>>> from #tmpA >>>>>> group by Rank1 >>>>>> >>>>>> select * from #tmpA >>>>>> order by rank1,rank2 >>>>>> >>>>>> >>>>>> >>>>>> drop table #tmpA >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> Rich wrote: >>>>>> >>>>>> -- >>>>>> HTH, >>>>>> Bob Barrows >>>>>>> On Tuesday, February 09, 2010 4:53 PM Rich wrote: >>>>>>> Thanks for you reply. This is kind of an addhoc operation. I just wanted to >>>>>>> insert blank rows into this set because I have to copy sections of it to >>>>>>> Excel - directly (an assortment of Excel files), and they wanted me to add >>>>>>> blank rows between the groups. Before I go nutty on cursors I just like to >>>>>>> see if there is an efficient tsql way to do it. It kinda sounds like there >>>>>>> is not. >>>>>>> >>>>>>> I will look at with Rollup. I used that some time ago, but too long so I >>>>>>> 'll have to dig in BOL for usage. >>>>>>> >>>>>>> "Mark McGinty" wrote: >>>>>>>> On Tuesday, February 09, 2010 9:45 PM Jeffrey Williams wrote: >>>>>>>> Forget about T-SQL, open up BIDS and create a report. Design the report >>>>>>>> however your end users need it, run the report and export the report to >>>>>>>> Excel from there. >>>>>>>>> On Tuesday, February 09, 2010 10:26 PM bill wrote: >>>>>>>>> This is really a front end issue, but I have an idea for a quick fix >>>>>>>>> if you are talking about a one time dump of data. I do not think >>>>>>>>> ROLLUP will work, but WITH CUBE (SQL 2008) could do the job if you do >>>>>>>>> some other ugly things in the query (see below). >>>>>>>>> >>>>>>>>> This is just a presentation/reporting issue, so do not try to actually >>>>>>>>> insert the empty rows in the table. Conceptually, a true table cannot >>>>>>>>> even hold an empty row, and none of the rows can repeat. If either of >>>>>>>>> those cases exist, the thing that looks like a table is not actually a >>>>>>>>> table, becasue it is not a set. >>>>>>>>> >>>>>>>>> The query below is pretty ugly, and kind of abuses the GROUP BY >>>>>>>>> concept, but it will insert blanks into the result set for you. I >>>>>>>>> cannot figure out how to change the sort order without destroying where >>>>>>>>> the blanks go, but I think it may do the job. Remember, when you >>>>>>>>> export data, you can export from a query, not only from a table. >>>>>>>>> Using a query lets you avoid destroying your table. >>>>>>>>> >>>>>>>>> Thanks, >>>>>>>>> >>>>>>>>> Bill >>>>>>>>> >>>>>>>>> The sample below is contrived (exampe, student_nm would be *terrible* >>>>>>>>> primary key) but I wanted to make it simple to follow: >>>>>>>>> >>>>>>>>> CREATE TABLE Student_Grade >>>>>>>>> ( >>>>>>>>> student_nm nvarchar(15) NOT NULL PRIMARY KEY >>>>>>>>> ,letter_grade_tx nvarchar(1) NOT NULL >>>>>>>>> ) >>>>>>>>> >>>>>>>>> INSERT INTO Student_Grade VALUES ('Joey', 'F') >>>>>>>>> INSERT INTO Student_Grade VALUES ('Sam', 'F') >>>>>>>>> INSERT INTO Student_Grade VALUES ('Margo', 'C') >>>>>>>>> INSERT INTO Student_Grade VALUES ('Geoff', 'D') >>>>>>>>> INSERT INTO Student_Grade VALUES ('Rex', 'A') >>>>>>>>> INSERT INTO Student_Grade VALUES ('Sara', 'A') >>>>>>>>> INSERT INTO Student_Grade VALUES ('Melissa','A' ) >>>>>>>>> INSERT INTO Student_Grade VALUES ('Jake', 'C') >>>>>>>>> INSERT INTO Student_Grade VALUES ('Carol', 'B') >>>>>>>>> INSERT INTO Student_Grade VALUES ('Franklin','C') >>>>>>>>> INSERT INTO Student_Grade VALUES ('Linus', 'A') >>>>>>>>> INSERT INTO Student_Grade VALUES ('Lucy', 'B') >>>>>>>>> GO >>>>>>>>> >>>>>>>>> WITH non_set AS >>>>>>>>> ( >>>>>>>>> SELECT >>>>>>>>> student_nm >>>>>>>>> ,letter_grade_tx >>>>>>>>> ,COUNT(*) AS Number_Of_Students >>>>>>>>> FROM >>>>>>>>> Student_Grade >>>>>>>>> GROUP BY >>>>>>>>> student_nm >>>>>>>>> ,letter_grade_tx >>>>>>>>> WITH CUBE >>>>>>>>> ) >>>>>>>>> SELECT >>>>>>>>> CASE >>>>>>>>> WHEN student_nm IS NULL >>>>>>>>> THEN '' /* << Two single quotes in a row = empty string */ >>>>>>>>> ELSE student_nm >>>>>>>>> END AS student_nm >>>>>>>>> , >>>>>>>>> CASE >>>>>>>>> WHEN student_nm IS NULL >>>>>>>>> THEN '' >>>>>>>>> ELSE letter_grade_tx >>>>>>>>> END AS letter_grade_tx >>>>>>>>> FROM >>>>>>>>> non_set >>>>>>>>> WHERE letter_grade_tx IS NOT NULL >>>>>>>>>> On Wednesday, February 10, 2010 2:28 AM Uri Dimant wrote: >>>>>>>>>> Rich >>>>>>>>>> Thanks for posting DDL >>>>>>>>>> select fld1,fld2, row_number() over (order by pos1,pos2 )rowid from ( >>>>>>>>>> >>>>>>>>>> select fld1 as pos1, >>>>>>>>>> >>>>>>>>>> 1 as pos2, >>>>>>>>>> >>>>>>>>>> fld1, >>>>>>>>>> >>>>>>>>>> fld2, >>>>>>>>>> >>>>>>>>>> cast(rowid as varchar(15)) as rowid >>>>>>>>>> >>>>>>>>>> from #tmpA >>>>>>>>>> >>>>>>>>>> union all >>>>>>>>>> >>>>>>>>>> select distinct >>>>>>>>>> >>>>>>>>>> fld1, >>>>>>>>>> >>>>>>>>>> 2, >>>>>>>>>> >>>>>>>>>> '', >>>>>>>>>> >>>>>>>>>> '', >>>>>>>>>> >>>>>>>>>> N'' >>>>>>>>>> >>>>>>>>>> from #tmpA >>>>>>>>>> >>>>>>>>>> ) Report >>>>>>>>>> >>>>>>>>>> order by pos1,pos2 >>>>>>>>>> Submitted via EggHeadCafe - Software Developer Portal of Choice >>>>>>>>>> Composite UI Pattern and RAD Development for Data Entry Applications, Part 1 >>>>>>>>>> http://www.eggheadcafe.com/tutorials/aspnet/a119aebe-7478-4aaa-b415-12786ec5cf90/composite-ui-pattern-and-rad-development-for-data-entry-applications-part-1.aspx
From: Erland Sommarskog on 11 Aug 2010 06:40 KAILASH SHARMA (kcs1311(a)hotmail.com) writes: > I wanted to reate blank rows in my data sheet which having more than > 1,00,000 nos of rows What is the context? That is, where do you consume this data. The purpose of an SQL query is to return data, formatting and presentation is normally done in the presentation layer. Where should these blank rows appear? After each 100 rows? Or when some key value changes? -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|
Pages: 1 Prev: Question on anatomy of a query Next: Adding extra rows in a select result set |