Convert from flat file to relational and back to flat...I think... 1

[dbasch]

Hello all,

I have a flat file such as:

Model Color
A1 Black
A1 White
A1 Blue
B2 White
B2 Black
B2 Yellow

I need the data in a different horizontal flat file structure for usage with OSCommerce:

Model Color1 Color2 Color3
A1 Black White Blue
B2 White Black Yellow

I am assuming that converting this to two tables (Model and Color) with a one to many relationship between Model.Model and Color.Model is my best first step. Then I can generate whatever flat file format I need from that.

Any suggestions on where to start? Thanks for the help!

Derek Basch

 

[PruSQLer]

This would make it horizontal. Since you don't have too many colors (I assume), it should be doable.

select model,
coalesce(max(case when color = 'Black' then color end),''),
coalesce(max(case when color = 'White' then color end),''),
coalesce(max(case when color = 'Yellow' then color end),'')
ETC, ETC (for as many colors as you have)

from Table 1
Group by model

If you want NULLS, just take out the coalesce function

 

[dbasch]

Thanks PruSQLer.

I found a similar solution here:

http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13_cid543871_tax285649,00.html

However, I have about a hundred different colors and am using Interbase/Firebird which does not have CASE or COALESCE.

It appears I will have to use python to loop over the original result set as also suggested in the article. Of course, he uses Coldfusion in his example but the idiom is the same.

<cfoutput query="originalquery" group="Model">
#originalquery.Model#
<cfset comma="">
<cfoutput>

#comma##originalquery.Color#
<cfset comma=", ">
</cfoutput>
</cfoutput>

This produces something like:

Model Color
A1 Black,White,Blue
B2 White,Black,Yellow

So I will have to alter the script to put each color into a new column.

Do you know of any other pure SQL ways of doing this?

Thanks Again,
Derek Basch

 

[r937]

hey derek, thanks for posting that link to my article



as for "pure" sql, i dunno

however, there's a new aggregate function in mysql 4.1 called GROUP_CONCAT that does *exactly* this

rudy

http://r937.com/

SQL Consulting