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.
| Author |
Topic |
|
avats
Starting Member
5 Posts |
 Posted - 2005-07-03 : 01:47:56
|
| hii have a challange for sql experts---i have a table of the following format====================1 GIACCI BROS PTY LTD 2 Head Office 3 Picton South Western Hwy 6229 (08) 9725 4588 4 After Hours Emergencies (08) 9725 4253 5 Fax (08) 9725 4225 5 Email info@giacci.com.au 6 Internet www.giacci.com.au 7 8 9 10 Giacci Bros Pty Ltd 11 South Western Hwy Picton 6229 (08) 9724 9588 Giacci Port Services Pty Ltd 397 Marine Tce Geraldton 6530 (08) 9964 1366 Giacci Port Services Pty Ltd Marine Tce Geraldton 6530 (08) 9964 6893 GIACO'S HAIR DESIGN Shop 1/ 8 Brockman St Manjimup 6258 (08) 9771 1774 ====================there are blank rows inbetween 2 records, number of blank rows not fixed.I want to rearrange them in the following ordermy requiremnets rrows do not have numbering i give them for simplicity1. remove blank rows ie 7,8,9 rows2. arrange all rows in group in consecutive columns ie arrane rows 1,2,3,4,5,6 in columns.then 2nd record consisting of rows 10,11final format will be something like this--------======================GIACCI BROS PTY LTD Head Office Picton South Western Hwy 6229 (08) 9725 4588 After Hours Emergencies (08) 9725 4253 Fax (08) 9725 4225 Email info@giacci.com.au Internet www.giacci.com.au Giacci Bros Pty Ltd South Western Hwy Picton 6229 (08) 9724 9588 Giacci Port Services Pty Ltd 397 Marine Tce Geraldton 6530 (08) 9964 1366 Giacci Port Services Pty Ltd Marine Tce Geraldton 6530 (08) 9964 6893 GIACO'S HAIR DESIGN Shop 1/ 8 Brockman St Manjimup 6258 (08) 9771 1774 ======================i already developed a logic for it but it slows the system .there r number of files each consisting of thousands of records. i want to speed up the operationis it possible with sqlthanx |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-07-03 : 14:36:54
|
| You "could" do this in SQL Server. It's a presentation issue though. The real issue is that you stored the data in a table with this format in the first place. Are you going to change the storage, so you store each of the attributes as seperate columns eventually? Basically, the way to solve it, according to your requirments, is going to use a big WHILE loop, cursor, or some really complex CASE statements. With the example you gave though, you're just throwing the "rows" into columns for display purposes. The first row of the first data set is a completely different attribute than the first row of the second data set. If you're going to reformat the data for display, as opposed to fixing the real issue of how it's stored, then just handle in the presentation layer.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
avats
Starting Member
5 Posts |
Posted - 2005-07-05 : 14:14:13
|
| hi derrickleg...i don't need them for display purpose but i have to do lot of processing after arranging the records in the desired fashion.i already worked out a looping solution for arranging the records programmetically. but at times it appears slow. I want to improve the performance with a query or cursor whatsoever it is if anyone can work outcheersthanx |
|
|
|
sfrigard
Starting Member
5 Posts |
Posted - 2005-07-05 : 14:19:36
|
quote:
Originally posted by avats
hii have a challange for sql experts---i have a table of the following format====================1 GIACCI BROS PTY LTD 2 Head Office 3 Picton South Western Hwy 6229 (08) 9725 4588 4 After Hours Emergencies (08) 9725 4253 5 Fax (08) 9725 4225 5 Email info@giacci.com.au 6 Internet www.giacci.com.au 7 8 9 10 Giacci Bros Pty Ltd 11 South Western Hwy Picton 6229 (08) 9724 9588 Giacci Port Services Pty Ltd 397 Marine Tce Geraldton 6530 (08) 9964 1366 Giacci Port Services Pty Ltd Marine Tce Geraldton 6530 (08) 9964 6893 GIACO'S HAIR DESIGN Shop 1/ 8 Brockman St Manjimup 6258 (08) 9771 1774 ====================there are blank rows inbetween 2 records, number of blank rows not fixed.I want to rearrange them in the following ordermy requiremnets rrows do not have numbering i give them for simplicity1. remove blank rows ie 7,8,9 rows2. arrange all rows in group in consecutive columns ie arrane rows 1,2,3,4,5,6 in columns.then 2nd record consisting of rows 10,11final format will be something like this--------======================GIACCI BROS PTY LTD Head Office Picton South Western Hwy 6229 (08) 9725 4588 After Hours Emergencies (08) 9725 4253 Fax (08) 9725 4225 Email info@giacci.com.au Internet www.giacci.com.au Giacci Bros Pty Ltd South Western Hwy Picton 6229 (08) 9724 9588 Giacci Port Services Pty Ltd 397 Marine Tce Geraldton 6530 (08) 9964 1366 Giacci Port Services Pty Ltd Marine Tce Geraldton 6530 (08) 9964 6893 GIACO'S HAIR DESIGN Shop 1/ 8 Brockman St Manjimup 6258 (08) 9771 1774 ======================i already developed a logic for it but it slows the system .there r number of files each consisting of thousands of records. i want to speed up the operationis it possible with sqlthanx
What identifies each group of records? Are they always nine records? Is there a column that defines the ordering of the records? A breif table definition would be helpful. |
|
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2005-07-05 : 14:22:32
|
| Why can't these just be separate attributes inside of a table, i.e. Fax, Email, WebSite, etc. |
|
|
|
avats
Starting Member
5 Posts |
Posted - 2005-07-06 : 14:28:19
|
| hi Derikk and othersrecords r not always 9 but they r in thousandsthere is always a single Blank row or multiple blank rows among 2 recordsactually these r Leads in Excel format which i have to process accordingly.so i have to arrange them in columns.thanxavats |
|
|
|
|
|
|
|
|
challanging problem for sql experts