Parsing a cell into multiple rows

Question

Post reply

Parsing a cell into multiple rows

wdillon

SSChasing Mays

Points: 642
More actions
May 2, 2008 at 12:43 pm

#185080

Hi All:
I have a intesting question. I have a table that I need to run some reports from, but the row that contains the data is delimited in an intesting way. What I was thinking about doing was to expand it out in a temp table with a serial ID and report from the temp table.
Basically the table looks like this:
ID| Customer # | Reservation # | details | ( | delimits the columns)
So the cell(s) of detail look like this:
No
$505.26
05/27/2007 12:00:00 PM
$505.26
What I'm trying to expand it to is something that might look like this:
ID| Customer # | Reservation # |
Cabin: WoodDuck (6B)
ID| Customer # | Reservation # | Check In:
ID| Customer # | Reservation # | Check Out:
ID| Customer # | Reservation # | Number of People:
ID| Customer # | Reservation # | Pet: No
ID| Customer # | Reservation # | Total Cost: $505.26
ID| Customer # | Reservation # | Dock: A
ID| Customer # | Reservation # | Check In:
ID| Customer # | Reservation # | Check Out: 05/27/2007 12:00:00 PM
ID| Customer # | Reservation # | Total Cost: $505.26
Any ideas??
Thanks a lot
Bill

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

Search this site for a split function.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Hi Bill,

I'm a little confused by the data you've posted. Take a look at the URL I carry in my signature line... providing data and table info in that manner will help us provide you with a good answer.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

wdillon SSChasing Mays Points: 642 More actions · Answer 3

Sorry, the actual detail of the cell looks like this:

No

$505.26

05/27/2007 12:00:00 PM

$505.26

Bill

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

You have a table with a single column and no external identifiers? Did you read the URL I pointed you to? You're not providing enough information for anyone to help.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

wdillon SSChasing Mays Points: 642 More actions · Answer 5

Hi Jeff:

I'm having some troble doing the cut copy and paste. I'm going to try it with quotes around it.

"

No

$505.26

05/27/2007 12:00:00 PM

$505.26

"

Ah, much better. I have not looked at the web site as of yet, but will be able to in a few hours.

Thanks

Bill

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

Ok... I'm starting to feel like I'm being trolled a bit here, Bill.

What else is in the table besides that... show more than 1 "record" please.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

The bottom line is that I don't know exactly what you're trying to do and can't help unless I understand. Parsing isn't difficult at all... if you can spend a little time making me understand what you're getting at... I don't know what you mean by a "cell" because there's no such thing as a "cell" in SQL Server. I can guess, but I'd rather you told me.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

wdillon SSChasing Mays Points: 642 More actions · Answer 8

Hi Jeff:

I know what you are saying and I appreciate it fully. I read the atricle that you suggested and I fully agree. I'm finding it difficult to paste the data into the reply, because it gets stripped of valuable information and delimiters. Basically, I'm refering to a cell as the intersction of one row and column. Maybe a data value would be a better name for it. Basically they have taken HTML data and plopped it in and are asking me to tear it appart and report on it.

The split function as suggested in the post is good and I can disasemble it that way be using a delimiter of "b>". However when I do that it results to:

1

<

6Cabin: WoodDuck (6B) Check In: </

4205/25/2007 3:00:00 PM <

68Check Out: </

8305/28/2007 10:00:00 AM <

110Number of People: </

1322 <

137 Pet: </

147No

<

156Total Cost: </

172$505.26

<

186Dock: A Check In: </

2095/25/2007 12:00:00 PM <

235Check Out: </

25005/27/2007 12:00:00 PM

<

279Total Cost: </

295$505.26

Well first thought is great, but when I think about it further, "Check Out" date is not associated to the date below it of "05/28/2007 10:00:00 AM" which BTW is associated to the WoodDuck Cabin. so between the Cabin name and the check in date, there is no delimiter.

I'm not sure what to do short of a program that can have the logic to tear apart the date and associate to each service. You will notice that there is also a Dock rental that is going on as well that has a different "Check Out" date of the Cabin.

Hope this helps

Thanks,

Bill

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

Hi Bill,

Ok... understood about the "cell" being the intersection of a row/column. Guess I need to see raw data without any split attempt on your part. Could you do a ...

SELECT column

FROM yourtable

... where "column" is the column of "cells" you're talking about. If there's a primary key, include that in the select... I need to see the unadulterated output from the select. If necessary, attach a file which will allow us to see the whole gambit.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

wdillon SSChasing Mays Points: 642 More actions · Answer 10

Thanks Jeff:

The select statement is:

Select reservationID, invoice from reservations

The attached file is eastlake.txt which is a csv

Thanks again for your help

Bill

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

Do you have the original file? SOmeone obviously tried to change the first one... even a computer couldn't screw things up that badly... takes a human beinging to mess things up that bad.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

wdillon SSChasing Mays Points: 642 More actions · Answer 12

Hi Jeff

Hum, you are right, it's pretty messed up. I'll attach a zip file of the orginal and see if that works.

Thanks

Bill

DAVNovak Hall of Fame Points: 3120 More actions · Answer 13

You may have some other issues.

Looking over your data in the attached Zip file, you have multiple line designated by the 'br' html tag

code for the same Id value.

You will need to straighten the multiple lines first and then you can parse the line for the various fields and corresponding data.

You will have to work with CHARINDEX function along with SUBSTRING to do most of the parsing.

To find the lines, search for 'br' html tag and parse everything up to the next 'br' html tag

.

Then, to find each field in the line, search for 'b' html tag up to the next 'b' html tag. In this value, you can further parse it on the '/b' html tag with everything to the left being the field designator and everything to the right being the value.

One other thing, in the first field for the line, you will need to parse the field designator one more time to seperate the 'Check In:' from the name of the particular rented object.

This can be done, but it will take some time and testing to make sure you grabbed each field correctly.

Dave Novak

wdillon SSChasing Mays Points: 642 More actions · Answer 14

I really don't know if this can be accomplished without a program. If I were to write programming specs for this, it would be as follows. I also included a spreadsheet of how the data should be extracted to usabe in a report format and a saved result of the columns involved in csv format.

Thanks and love your opinions.

Bill

Spec ---

In Reservations:

Total records = 1566

Total records where “Total Price” is used = 1422

Total records where “Total Cost” is used = 144

2 records don’t have “Total Cost” or “Total Price” - undefined

•Each record may contain 0 to n number of services

•Each record may contain one or more instances of “Total Cost/Price”

Program logic

Create transactions.temp table with the flowing schema

TransID Not null, Identity

ReservationNum vchar(25)

PayStatus vchar(15)

ServiceType vchar(15)

CheckInDate date

CheckOutDate date

Price num

Total num

Tax num

TotalPrice num

Select * from reservations table

Read in a record set

Determine record type (PayStatus) of (Pending, Paid, Refunded, Cancelled)

Get ReservationNum

Get Invoice

In Invoice field

Delimit on Service Type(s) (Cabin, RV Site, Pontoon, Dock) – note that some records do not contain any Service

Loop through each string from above ?

Getting

Service Type (Cabin, . . . .)

Check in date

Check out date

Price – if any

Tax – if any

Total – if any

Total (Price/Cost) if any

Write dataset to transaction.temp

Close datasets

Select from reservations.temp by criteria

Report