Insert blank lines in table to group data....

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

Transact-SQL (2000)

Insert blank lines in table to group data....

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2006-12-15 : 08:49:02

Hi I wish to group data and insert a blank line in a table to make it easier to see the grouped data. i.e.:

Co_Name, Co_Address
abc ltd , 123 street
fort ltd , 123 street
<blank row>
123 ltd , 345 Road
test ltd , 345 Road
89 plc , 345 Road
<blank row>

etc....

The group by is on Co_Address. Currently all the data is in one table, and I just need a blank row to separate the grouped by addresses. Any suggessions very welcome!!

Any sample code even better ;)

Thanks in advance

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-12-15 : 09:13:22

Since you only want this for viewability, I'd use a report (Reporting Services, Crystal, etc...) and format the report to include a blank group footer. Is that a possibility?

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2006-12-15 : 09:18:00

Not really. I have to have a table that does this stored in the database.

Thanks for you suggestion though ;)

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-12-15 : 09:19:18

An alternative...

Create Table #TestData
(Co_Name varchar(100)
, Co_Address varchar(100))

Insert Into #TestData Values ('abc ltd', '123 street')
Insert Into #TestData Values ('fort ltd', '123 street')
Insert Into #TestData Values ('123 ltd', '345 road')
Insert Into #TestData Values ('89 plc', '345 road')
Insert Into #TestData Values ('test ltd', '345 road')

Create Table #FinalOutput
(Co_ID int identity (1, 1)
, Co_Name varchar(100)
, Co_Address varchar(100))

Declare @Address varchar(100)
, @RowCnt int

Select Top 1 @Address = Co_Address
From #TestData
Order By Co_Address

Set @RowCnt = @@RowCount

While @RowCnt <> 0

Begin
Insert Into #FinalOutput
Select Co_Name, Co_Address
From #TestData
Where Co_Address = @Address

Insert Into #FinalOutput (Co_Name, Co_Address) Values ('', '')

Select Top 1 @Address = Co_Address
From #TestData
Where Co_Address > @Address
Order By Co_Address

Set @RowCnt = @@RowCount
End

Select * From #FinalOutput

...Although it's not set based, it will work.

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-12-15 : 09:21:01

Why do you need a table with blank rows?

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2006-12-15 : 09:39:01

Thanks for that, I'll try adjust the code to be applied on a table.

I need blank rows to compare if the company names are definitely different but share the same address. As this could involve 1000's rows, It's easier to identify the groups with a blank row.

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2006-12-15 : 09:48:30

what do you mean, by its not set based?

Will it insert a row in between every record? I just require a row to be inserted between records where the Address is not the same, so in effect it will be grouped by records with the same address..

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-12-15 : 09:49:49

I get that it's easier to visually see the groups with a space, it sounds like this is more of a presentation issue and should probably be addressed at that layer.

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-12-15 : 09:52:28

If all you're looking for is companies that share addresses...

Create Table #TestData
(Co_Name varchar(100)
, Co_Address varchar(100))

Insert Into #TestData Values ('abc ltd', '123 street')
Insert Into #TestData Values ('fort ltd', '123 street')
Insert Into #TestData Values ('123 ltd', '345 road')
Insert Into #TestData Values ('89 plc', '345 road')
Insert Into #TestData Values ('test ltd', '345 road')
Insert Into #TestData Values ('test2 ltd', '456 lane')

Select *
From #TestData
Where Co_Address In
(Select Co_Address
From (Select Co_Address, Co_Name
From #TestData
Group By Co_Address, Co_Name) A
Group By Co_Address
Having Count(*) > 1)
Order By Co_Address

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-12-15 : 09:59:35

quote:
what do you mean, by its not set based?

Will it insert a row in between every record? I just require a row to be inserted between records where the Address is not the same, so in effect it will be grouped by records with the same address..

My solution uses a loop and "processes" records group at a time. Set based would use the whole desired result set at once.

Run the sample I gave you, it will insert a blank into the temp table...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-15 : 11:25:50

quote:
Originally posted by dnf999

Hi I wish to group data and insert a blank line in a table to make it easier to see the grouped data. i.e.:

Co_Name, Co_Address
abc ltd , 123 street
fort ltd , 123 street
<blank row>
123 ltd , 345 Road
test ltd , 345 Road
89 plc , 345 Road
<blank row>

etc....

The group by is on Co_Address. Currently all the data is in one table, and I just need a blank row to separate the grouped by addresses. Any suggessions very welcome!!

Any sample code even better ;)

Thanks in advance

1 Where do you want to show data?
2 If you use Reports, group the data by Address and in the Group footer insert a line or expand that footer
3 This is correct time to say "Do it in the front end application?"
4 SQL Server is to store proper data and not formatted data

Madhivanan

Failing to plan is Planning to fail

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2006-12-26 : 16:07:13

I want the data to be stored (with the blank rows) in a table.

It cannot be done in a front end app, as I'm doing it through SQL

Formatted has to be displayed like this for visual analysis.

Thanks!

srinath
Starting Member

16 Posts

Posted - 2006-12-27 : 09:08:18

Create Table #TestData
(Co_Name varchar(100)
, Co_Address varchar(100))

Insert Into #TestData Values ('abc ltd', '123 street')
Insert Into #TestData Values ('fort ltd', '123 street')
Insert Into #TestData Values ('123 ltd', '345 road')
Insert Into #TestData Values ('89 plc', '345 road')
Insert Into #TestData Values ('test ltd', '345 road')

Create Table #TestData1
(CNT INT IDENTITY(1,1),Co_Name varchar(100)
, Co_Address varchar(100))

DECLARE @I VARCHAR(25)
DECLARE TEST_CUR CURSOR FOR SELECT Co_Address FROM #TestData GROUP BY Co_Address
OPEN TEST_CUR
FETCH NEXT FROM TEST_CUR INTO @I
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #TestData1(Co_Address,Co_Name) SELECT Co_Address,Co_Name FROM #TestData WHERE Co_Address = @I
INSERT INTO #TestData1 (Co_Address,Co_Name) VALUES( ' ',' ')
FETCH NEXT FROM TEST_CUR INTO @I
END
CLOSE TEST_CUR
DEALLOCATE TEST_CUR

SELECT Co_Address,Co_Name FROM #TestData1 ORDER BY CNT

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-27 : 11:59:43

This is done without a loop or a cursor... uncomment the "INTO" line to send the output to a new table...

--===== "Borrowed" test data
Create Table #TestData
(Co_Name varchar(100), Co_Address varchar(100))

Insert Into #TestData Values ('abc ltd', '123 street')
Insert Into #TestData Values ('fort ltd', '123 street')
Insert Into #TestData Values ('123 ltd', '345 road')
Insert Into #TestData Values ('89 plc', '345 road')
Insert Into #TestData Values ('test ltd', '345 road')

--===== Cursorless/loopless solution
 SELECT CASE WHEN GROUPING(Co_Name) = 1 THEN ' ' ELSE Co_Name END AS Co_Name,
        CASE WHEN GROUPING(Co_Name) = 1 THEN ' ' ELSE Co_Address END AS Co_Address
--  INTO sometable
   FROM #TestData
  GROUP BY Co_Address,Co_Name WITH ROLLUP

Better yet, create a new table for the output with an IDENTITY column so you can maintain the sort order...

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources