insert blank rows between record groups

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

insert blank rows between record groups

Author

Topic

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2005-09-08 : 12:44:18

Good day,

After a great experience on my first post, I'm back with another question. This uses the same scenario as before, but is a different question, so I am putting this in a new post.

We are working with a set of tables that include primary records and related records for an order and subject number. The original goal was, given an order number (order_no VARCHAR(10)) and subject number (subject_no INT), return each primary result sorted by date, and under each primary result return its related results sorted by date. See the data and result for clarification.

Now we have an additional requirement: between each group of primary and related records (group includes a primary record and all its related records), insert a blank line. I find this kind of hard to explain, but again please see the example at the bottom for the desired output result.

Here's the code to generate a test environment:

SET NOCOUNT ON

-- create sample tables, dropping first if they already exist
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[testPrimary]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[testPrimary]
GO

CREATE TABLE [dbo].[testPrimary] (
	[primary_id] [int] NOT NULL ,
	[order_no] [varchar] (10) COLLATE Latin1_General_BIN NOT NULL ,
	[subject_no] [smallint] NOT NULL ,
	[file_type] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
	[file_no] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
	[file_date] [datetime] NOT NULL
)
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[testRelated]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[testRelated]
GO

CREATE TABLE [dbo].[testRelated] (
	[related_id] [int] NOT NULL ,
	[order_no] [char] (10) COLLATE Latin1_General_BIN NOT NULL ,
	[subject_no] [smallint] NOT NULL ,
	[file_type] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
	[file_no] [varchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
	[file_date] [datetime] NOT NULL ,
	[primary_id] [int] NULL
)
GO


-- plug in some sample data
INSERT INTO [testPrimary] VALUES(1,1234,2,'typeA','654.15','2003-01-16')
INSERT INTO [testPrimary] VALUES(6,1234,2,'typeB','21-F46','2004-08-11')
INSERT INTO [testPrimary] VALUES(47,1234,2,'type6','97865','2003-05-22')
INSERT INTO [testPrimary] VALUES(102,1234,2,'type4','762','2004-06-30')

INSERT INTO [testRelated] VALUES(8,1234,2,'typeC','834.23','2003-10-02',1)
INSERT INTO [testRelated] VALUES(9,1234,2,'type1','1023','2003-07-14',1)
INSERT INTO [testRelated] VALUES(14,1234,2,'type2','94.z0','2005-07-12',6)
INSERT INTO [testRelated] VALUES(17,1234,2,'QQQ','999','2005-08-25',1)
INSERT INTO [testRelated] VALUES(39,1234,2,'type1','156.441','2003-02-12',6)
INSERT INTO [testRelated] VALUES(426,1234,2,'typeC','4654s-a2','2005-09-04',6)


-- create sample view, dropping first if it already exists
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[testReportView]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[testReportView]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.testReportView
AS
SELECT		TOP 100 PERCENT
			t.order_no
		,	t.subject_no
		,	t.source
		,	t.sort_date
		,	t.primary_id
		,	t.file_type
		,	t.file_no
		,	t.file_date
FROM
(	SELECT
				order_no
			,	subject_no
			,	'primary'	AS	source
			,	primary_id
			,	file_type
			,	file_no
			,	file_date
			,	file_date	AS	sort_date
	FROM
			testPrimary
	UNION ALL
	SELECT
				r.order_no
			,	r.subject_no
			,	'related'	AS	source
			,	r.primary_id
			,	r.file_type
			,	r.file_no
			,	r.file_date
			,	p.file_date	AS	sort_date
	FROM
			testRelated r
	JOIN
			testPrimary p
			ON
			r.primary_id = p.primary_id
) t
ORDER BY
			t.sort_date
		,	t.primary_id
		,	t.source
		,	t.file_date

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


-- create sample stored procedure, dropping first if it already exists
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[testReportSP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[testReportSP]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE	testReportSP
(
		@OrderNo	VARCHAR(10)		-- order number to be used as filter criteria
	,	@SubjectNo	INT				-- subject number to be used as filter criteria
)
AS
SELECT
			file_type
		,	file_no
		,	CONVERT(varchar,file_date,101)	AS	file_date
		,	primary_id
FROM
			testReportView
WHERE
			order_no = @OrderNo
AND
			subject_no = @SubjectNo

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Now if you run EXECUTE testReportSP '1234', 2 you will get the following output:

file_type  file_no   file_date   primary_id  
---------  --------  ----------  ----------- 
typeA      654.15    01/16/2003  1
type1      1023      07/14/2003  1
typeC      834.23    10/02/2003  1
QQQ        999       08/25/2005  1
type6      97865     05/22/2003  47
type4      762       06/30/2004  102
typeC      4654s-a2  09/04/2005  102
typeB      21-F46    08/11/2004  6
type1      156.441   02/12/2003  6
type2      94.z0     07/12/2005  6

(primary ID is included only for reference; this column is excluded in actual report)

With the additional requirement to insert a blank row between primary_id groupings, the result set should look like this:

file_type  file_no   file_date   primary_id  
---------  --------  ----------  ----------- 
typeA      654.15    01/16/2003  1
type1      1023      07/14/2003  1
typeC      834.23    10/02/2003  1
QQQ        999       08/25/2005  1

type6      97865     05/22/2003  47

type4      762       06/30/2004  102
typeC      4654s-a2  09/04/2005  102

typeB      21-F46    08/11/2004  6
type1      156.441   02/12/2003  6
type2      94.z0     07/12/2005  6

Can anyone offer a suggestion for how to get this result?

Thank you,

Daniel

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2005-09-08 : 12:47:52

If you create the above test environment, here's a simple script to remove those objects.

-- clean up after ourselves by removing sample objects
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[testPrimary]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[testPrimary]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[testRelated]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[testRelated]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[testReportView]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[testReportView]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[testReportSP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[testReportSP]
GO

Regards,

Daniel

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-09-08 : 12:48:40

This is a presentation issue and should be handle in the application code. not the database layer.

====================================================
Regards,
Sean Roussy

"pimpin ain't easy, but someone has to do it" -- Pimpin Whitefolks(?)

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2005-09-08 : 14:36:20

Maybe so -- but I'm not an experienced programmer in any language, and I'm just dumping the results out via PHP into an HTML table to create a simple report. Don't have any other developer resources available at this time to help out with this, so I was hoping for a SQL-based solution.

Thanks,

Daniel

X002548
Not Just a Number

15586 Posts

Posted - 2005-09-08 : 15:31:06

I know this sounds hokey, but add another UNION ALL to you view, that has a SELECT DISTINCT on the primaryID, and CHAR(13)+CHAR(10) to the segement and have it sort as the last row of the group..the could force a new line I think...

Let me see if I can come up with a simple example...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2005-09-08 : 15:35:09

OK, I have found something that works! Please have a look and let me know if there is a better way. Below are changes I have made to the view and the stored procedure.

For the view, I looked at the solution that worked earlier, and just added another UNION ALL to include a "blank" line for each primary record. I gave it a value for 'source' that begins with 'z_' so that it will sort last in each primary_id record group. Also set the date value to '1900-01-01' for file_date; tried null but that actually gave back 'NULL' in the results.

For the stored procedure I added a CASE to return blank if the file_date = '1900-01-01', otherwise return the value. This is the first time I have tried using CASE so please let me know if I am using it incorrectly or if there is a better method to accomplish this.

Here's the code:

-- create sample view, dropping first if it already exists
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[testReportView]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[testReportView]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.testReportView
AS
SELECT		TOP 100 PERCENT
			t.order_no
		,	t.subject_no
		,	t.source
		,	t.sort_date
		,	t.primary_id
		,	t.file_type
		,	t.file_no
		,	t.file_date
FROM
(	SELECT
				order_no
			,	subject_no
			,	source		=	'z_blank'
			,	primary_id
			,	file_type	=	''
			,	file_no		=	''
			,	file_date	=	'1900-01-01'
			,	sort_date	=	file_date
	FROM
			testPrimary
	UNION ALL
	SELECT
				order_no
			,	subject_no
			,	source		=	'primary'
			,	primary_id
			,	file_type
			,	file_no
			,	file_date
			,	sort_date	=	file_date
	FROM
			testPrimary
	UNION ALL
	SELECT
				r.order_no
			,	r.subject_no
			,	source		=	'related'
			,	r.primary_id
			,	r.file_type
			,	r.file_no
			,	r.file_date
			,	sort_date	=	p.file_date
	FROM
			testRelated r
	JOIN
			testPrimary p
			ON
			r.primary_id = p.primary_id
) t
ORDER BY
			t.sort_date
		,	t.primary_id
		,	t.source
		,	t.file_date

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


-- create sample stored procedure, dropping first if it already exists
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[testReportSP]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[testReportSP]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE	testReportSP
(
		@OrderNo	VARCHAR(10)		-- order number to be used as filter criteria
	,	@SubjectNo	INT				-- subject number to be used as filter criteria
)
AS
SELECT
			'File Type'		=	file_type
		,	'File Number'	=	file_no
		,	'File Date'		=	(
				CASE	file_date
					WHEN	'1900-01-01'	THEN	''
					ELSE	CONVERT(varchar,file_date,101)
				END	)
		,	'Primary ID'	=	primary_id
FROM
			testReportView
WHERE
			order_no = @OrderNo
AND
			subject_no = @SubjectNo

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Now when you run EXECUTE testReportSP '1234', 2 you will get the desired results:

File Type  File Number  File Date   Primary ID  
---------  -----------  ----------  ----------- 
typeA      654.15       01/16/2003  1
type1      1023         07/14/2003  1
typeC      834.23       10/02/2003  1
QQQ        999          08/25/2005  1
                                    1
type6      97865        05/22/2003  47
                                    47
type4      762          06/30/2004  102
typeC      4654s-a2     09/04/2005  102
                                    102
typeB      21-F46       08/11/2004  6
type1      156.441      02/12/2003  6
type2      94.z0        07/12/2005  6
                                    6

Again, Primary ID displayed only for demonstration purposes and will not be included in the actual report.

Now the last bit to figure out is how to suppress the last record returned!

Any problems or optimizations, etc. for what I have written? Can anyone offer a better way?

Thank you,

Daniel

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2005-09-08 : 15:38:19

Thanks Brett, I was thinking in the same "hokey" direction with the additional UNION ALL. I'm interested in seeing what results you get with using CHAR(13)+CHAR(10).

Regards,

Daniel

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2005-09-08 : 15:54:25

Just reading a bit on the forums and found a way to use CASE differently. This lets me use NULL values for file_date on "blank" rows and still return blank for that value in the recordset.

'File Date'		=	(
	CASE
		WHEN	file_date	IS NULL	THEN	''
		ELSE	CONVERT(varchar,file_date,101)
	END	)

So now in that first SELECT statement (the extra UNION ALL) I can use file_date = NULL.

Getting cleaner anyway, still not sure if CASE is the best thing to use here...

Daniel

X002548
Not Just a Number

15586 Posts

Posted - 2005-09-08 : 15:55:41

This is what I came up with


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int, Col2 char(1))
GO

INSERT INTO myTable99(Col1, Col2)
SELECT 1, 'a' UNION ALL
SELECT 1, 'b' UNION ALL
SELECT 1, 'c' UNION ALL
SELECT 2, 'd' UNION ALL
SELECT 2, 'e' UNION ALL
SELECT 3, 'f' UNION ALL
SELECT 3, 'g' UNION ALL
SELECT 3, 'h' UNION ALL
SELECT 3, 'i' UNION ALL
SELECT 3, 'j'
GO

SELECT ReportLine 
  FROM (
	SELECT CONVERT(varchar(15),Col1) + ' '+ Col2 AS ReportLine, Col1, 1 AS Sort 
	  FROM myTable99
     UNION ALL
	SELECT DISTINCT ' ' AS ReportLine, Col1, 2 AS Sort 
	  FROM myTable99
	) AS XXX
ORDER BY Col1, Sort
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2005-09-08 : 16:17:58

Wow, my head hurts now! OK, I'm really not sure what you did there.

It looks like you're able to use SELECT DISTINCT because you're using fewer columns and concatenating columns to output the report records. Could this possibly work with the sample data I provided? Values are different lengths, the output must be in rows/columns when given to the web app, etc. I don't see how this works...

Can you apply this approach to the sample data and get the desired results?

Thanks,

Daniel

X002548
Not Just a Number

15586 Posts

Posted - 2005-09-08 : 16:21:35

The SELECT DISTINCT is used to only get 1 row for each ID (Col1)...

I also ORDER By the ID and the artificial Sort column to force that row to come out on the end of the group

I then leave the report line blank/empty string whatever to get you your "spacing"

Did you cut and paste the code in to query Analyzer, it should run for you with no problem and you can see the code "in action"

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

X002548
Not Just a Number

15586 Posts

Posted - 2005-09-08 : 16:40:32

I was thinking along the lines like this


SELECT	   file_type
	,  file_no
	,  CASE WHEN sort = 2 then ' ' ELSE CONVERT(varchar(25),file_date,103) END AS file_date
	,  CASE WHEN sort = 2 then ' ' ELSE CONVERT(varchar(15),primary_id) END AS primary_id2  
FROM
(	SELECT
				order_no
			,	subject_no
			,	'primary'	AS	source
			,	primary_id
			,	file_type
			,	file_no
			,	file_date
			,	file_date	AS	sort_date
			,	1		AS 	sort
	FROM
			testPrimary
	UNION ALL
	SELECT
				r.order_no
			,	r.subject_no
			,	'related'	AS	source
			,	r.primary_id
			,	r.file_type
			,	r.file_no
			,	r.file_date
			,	p.file_date	AS	sort_date
			,	1		AS 	sort
	FROM
			testRelated r
	JOIN
			testPrimary p
			ON
			r.primary_id = p.primary_id
     UNION ALL
	SELECT DISTINCT
				' ' AS order_no
			,	' ' AS subject_no
			,	' ' AS	source
			,	primary_id
			,	' ' AS file_type
			,	' ' AS file_no
			,	' ' AS file_date
			,	' ' AS sort_date
			,	2   AS sort
	FROM
			testPrimary
) t
ORDER BY
			t.primary_id ASC
	,	t.sort

And if you want bizarre.....take the 2 of then end of the id...it looks like the ORDER BY references the Value of the CASE...which I didn't think it would do......

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources