Query w/bad table structure

[rmorgan]

Hi,
I am trying to query a column that has multiple values. When I export it, I
need to have it so that each of those values is placed in its own column
instead of them being grouped together. What might the mysql syntax be to
accomplish this? TIA.

[Sojovi]

Are you trying to convert a column into a row ?

Regards

[Kronin555]

There's no way to do this directly in a database query. You have 2 choices:

redesign your database to fix this
-or-
do the conversion in Coldfusion (do the query, loop through the query creating
a new query and separating out the values)

If you are storing multiple values in a single field in your database, either
comma-delimited, space-delimited, or some other way, then you aren't working
with a normalized database structure and aren't using the database how it was
designed (which is why you can't do this directly in a query).

[rmorgan]

I guess you could say that Sojovi

The origianal column, lets call it OrigCol has these possible values
101,102,103,104

To start with I need to run the query to create a tab delimited file and the
end result needs to look like:
"101" "102" "103" "104"

instead of : "101 102 103 104" like it does right now.

[Dan Bracuk]

Originally posted by: rmorgan
I guess you could say that Sojovi

The origianal column, lets call it OrigCol has these possible values
101,102,103,104

To start with I need to run the query to create a tab delimited file and the
end result needs to look like:
"101" "102" "103" "104"

instead of : "101 102 103 104" like it does right now.


See if the listqualify function allows you to use double quotes. I always use
single, so I don't actually know.

[MikerRoo]

Originally posted by: Kronin555
There's no way to do this directly in a database query....

No, you can do this in PostgreSQL and MS SQL. Might be able to do it in
MySQL 5 not sure about that one.

So the answer is switch to PostgreSQL!

[Kronin555]

MikerRoo, I'd be interested to see how you can do this in PostgreSQL. Could you
post an example? I'm assuming you're proposing using a column type of ARRAY or
a composite type... even with that I don't know how you can get what the
original poster was asking for.

[MikerRoo]

The basic technique is to use a UDF that parses the string into a record set.

I don't have an example here at work (a MS SQL shop). I'll fire up the ole
abacus at home when I get a chance, if you still need an example.

[Kronin555]

> The basic technique is to use a UDF that parses the string into a record set.

Ah, gotcha. Yes, this could be handled with a UDF or stored procedure. I never
considered that angle, but would be a good fix if changing the database schema
is out of the question. I haven't written many UDFs or stored procs, so don't
consider it as often as I probably should when looking for a solution to a
problem.

[MikerRoo]

Anyway, (as you correctly point out) if you have to do this then some DB
designer has not done an adequate job.

The correct thing to do is to fix the DB design and "calibrate" the original
designer if he's within range.