Multiple Fields per Field - SQL Server Forums

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)

Multiple Fields per Field

Author

Topic

zavier
Yak Posting Veteran

50 Posts

Posted - 2002-02-20 : 14:55:44

Hello

I made a post about a topic related to this and got some very helpful advice but I have not been able to figure a solution to my problem. From the responses to my posts I realized that database design is my problem. I guess I take the blame because I get the data in text files and import them in virtually the same format as I get it. For instance, here is an example:

Main Data Table
ID | Beds | Baths | Floors | Exterior
---------------------------------
142 | 3 | 2 | 2,5, | 6,9,
178 | 4 | 3 | 1,5, | 6,7,

Features Table

ID | Description
-------------------
1 | Wood
2 | Carpet
5 | Linoleum
6 | Stucco
7 | Siding
9 | Paneling

As you can see the Floors and Exterior Fields have multiple possible selections and they are seperated by a comma. I need to return for instance the following recordset to the calling application (ASP Page).

ID | Beds | Baths | Floors | Exterior
---------------------------------
178 | 4 | 3 | Wood,Linoleum | Stucco, Siding

I have simplified this greatly as in actuality the Features table contains about 1000 or so records and the number of features fields in each record in the main data table is about 200. I would prefer this process of conversion be done during the import of the data but at this point I'm willing to try anything.

Any help would be greatly appreciated. Thanks

chadmat
The Chadinator

1974 Posts

Posted - 2002-02-20 : 15:21:00

Zavier,

I think you will be much better off if you go ahead and bite the bullet now. Redesign the database, and normalize these tables.

I will try to help given the info in the post.

You would have three tables:
1. Main property table:

ID | Beds | Baths
142 | 3 | 2
178 | 4 | 3

2. A Features Table that will be the same as it is now.

3. A PropertyFeature Table

PropertID | FeatureID | ExteriorOrFloor
142 | 2 | 0
142 | 5 | 0
142 | 6 | 1
142 | 9 | 1
178 | 1 | 0
178 | 5 | 0
178 | 6 | 1
178 | 7 | 1

ExteriorOrFloor is a bit, if there are more types of features, that should be a table as well Like:

TypeID | FeatureType
1 | Floor
2 | Exterior
3 | Roof
4 | Backyard
etc...
Then you would just replace ExteriorOrFloor with TypeID.

HTH
-Chad

zavier
Yak Posting Veteran

50 Posts

Posted - 2002-02-20 : 15:42:19

Thanks Chadmat!

This is exactly what I was looking for! I think this will work Like a charm but how do I go from my original data file in text format to the layout you describe. It looks as if there will be some pretty dificult manipulation to do with the text files.

chadmat
The Chadinator

1974 Posts

Posted - 2002-02-20 : 16:00:34

How is the data arranged in the text files now?

-Chad

zavier
Yak Posting Veteran

50 Posts

Posted - 2002-02-20 : 16:08:02

Pretty much the same way the data is in the table.

ID | Beds | Baths | Floors | Exterior
---------------------------------
142 | 3 | 2 | 2,5, | 6,9,
178 | 4 | 3 | 1,5, | 6,7,

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-02-20 : 16:14:39

You should be able to use DTS to read teh Text files, and then transform the "array fields" into individual "INSERT" statements into the correct tables.
DTS is hella fast and hella powerful. You should be able to do this all with DTS.

Michael

chadmat
The Chadinator

1974 Posts

Posted - 2002-02-20 : 16:21:59

So is comma the delimiter in the text file, or pipe ('|')?

Does it look like this: 142 | 3 | 2 | 2,5, | 6,9,
or this: 142 , 3 , 2 , 2,5, 6,9,

-Chad

zavier
Yak Posting Veteran

50 Posts

Posted - 2002-02-20 : 16:26:05

It is a pipe
142 | 3 | 2 | 2,5, | 6,9,

Thanks alot for your help btw :-)

chadmat
The Chadinator

1974 Posts

Posted - 2002-02-20 : 16:43:41

I'm not 100% sure, but it doesn't look like you can do this in a single import with DTS. Someone can correct me if I'm wrong, but It looks like you have 2 delimiters , (For features) and | (For other attributes. I don't think DTS will allow you to specify 2 delimiters, but I don't do a lot of text file importing, so I don't know that for sure.

Anyone know a way to import this data into the normalized tables?

-Chad

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-20 : 17:47:55

Import the text file into a holding table, using the pipe as your column delimiter. The holding table will have the structure you're using now.

Create the new tables according to the design Chad recommended. Then try this:

http://www.sqlteam.com/item.asp?ItemID=2652

I don't think you'll be able to do both floors and exterior in one INSERT operation, but you really don't need to; do one INSERT for floors and a separate one for exterior.

zavier
Yak Posting Veteran

50 Posts

Posted - 2002-02-20 : 20:26:36

Thanks Rob & Chad!

This is exactly what I needed! I hope I'm not pushing my luck here, but one more question. There are actually about 200 features columns, I only used 2 for my example for brevity. Is there a way to dynamically iterate through say columns 200 - 355 (which are the feature columns) without having to name them? Thanks very much for all the help so far!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-20 : 22:11:05

AAAAAAAAAAAAAAAAAAAAIIIIIII-
IIIIIIIIIIIIIIIIIIIIIIIIIGGGGG-
GGGGGGGGGGGGGGGGGGGGGGGGGGG-
GGGHHHHHHHHHHHHHHHHHHHHHHHHHHH-
HHHHHHHH!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Uh, what I meant to say was, SURE, WHY NOT, I ALWAYS WANTED TO PULL MY HAIR OUT, THIS IS PROBABLY THE BEST OPPORTUNITY I'LL EVER GET!!!

<meant in jest>
Just out of curiosity, is there some secret society out there whose credo is "Hey, let's post questions on SQL Team and provide vague descriptions, table structures bearing no resemblance to the actual tables we have, and simplified versions that leave out absolutely crucial information!" There seem to be A LOT of posts like that lately!
</meant in jest>

Anyway, I think I have a way to do it. I had to modify Chad's recommendations on structure though, for the PropertyFeature table. Instead of a numeric code to identify floor, exterior, etc. I'm actually using the column name taken from the holding table...so that the Type column will have 'Floor', 'Exterior' etc. as values. I'm making it a sysname column so that it can hold legitimate column names as values:

CREATE TABLE PropertyFeature (PropertyID int, FeatureID int, FeatureType sysname)

You won't need a separate table for codes, since the values would be self-explanatory. Don't ask me to figure out a way that allows you to use codes for FeatureType, straightjackets are itchy and too tight!

Here it is:

DECLARE @sqltemplate varchar(8000), @sql varchar(8000), @ctr int
SET NOCOUNT ON
SELECT @sqltemplate='INSERT INTO PropertyFeature (ID, FeatureID, FeatureType) 
SELECT H.ID, convert(int,SubString('','' + ^ + '','' , seq , 
CharIndex('','' , '','' + ^ + '','' , seq) - seq)) FeatureID, ''^'' FeatureType 
FROM sequence, holding AS H WHERE seq <= Len('','' + ^ + '','') 
AND SubString('','' + ^ + '','' , seq - 1, 1) = '','''
SELECT @ctr=1  --CHANGE THIS NUMBER TO THE NUMERIC POSITION OF THE 1st COLUMN TO BE PARSED
WHILE @ctr<=3  --CHANGE THIS NUMBER TO THE NUMERIC POSITION OF THE LAST COLUMN TO BE PARSED
 BEGIN
 SELECT @sql=replace(@sqltemplate, '^', COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS 
 WHERE ORDINAL_POSITION=@ctr AND TABLE_NAME='holding'
 EXEC(@sql)
 SELECT @ctr=@ctr+1
 END

This was written under the influence of rum & coke and Metallica, so I can't vouch for whether it works right off the bat

Don't ask me how it works.....ohhhhhhhhhhhh, alllllllllllllllriiiiiiiiiiiiight:

The columns you want to parse MUST be in consecutive order, you can't skip any with this code. The sections in bold highlight the beginning and ending number; change them to match the start and end column numbers. If you have to skip columns you should make up multiple blocks of code with the start and end of each consecutive sequence of columns.

It will grab the column names from the INFORMATION_SCHEMA.COLUMNS view, one at a time. Also notice that I highlighted table names in bold, I'm assuming the data is kept in a table called "holding", change this to match your table.

The @sqltemplate variable contains the INSERT statement needed, with placeholders (^) for the column name. The loop will replace this with each column name, then EXECUTE the resulting INSERT statement. It should be syntactically correct unless you have columns with spaces in the names (man, PLEEEEEEEEEAAAAAAAAAASSSSSSSSSSSSEEEEEEEE tell me you don't put spaces in your column names, I couldn't take it!)

This loops through each parseable column and parses its contents and INSERTs them into the PropertyFeature table. Well, that's what it's SUPPOSED to do, you'll have to test it. I tried it with a 3 column table because, well, I'm lazy, but it worked fine.

And you'll need to create a table called "Sequence", with a single column called "seq" as an int, and populate it with values from 1 to 8000 or higher. The example in the article I posted earlier was called "Tally" with an "ID" column, and I changed the name here cause I didn't want to confuse either of us.

I can't WAIT for the next thing you need..."Hey guys, can I have this command glow in the dark and howl like a wolf when I run it?"...but I'm off now, gotta paint the Great Wall of China, 2 coats, both sides, and I've only got a 1 inch brush and 12 hours to do it...

HTH

Edited by - robvolk on 02/20/2002 22:40:29

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2002-02-20 : 23:51:31

I think this is what happens when Rob doesn't get his medication.

Everybody back slowly toward the door while making little cooing sound and gentling hand gestures

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources