Tuesday, March 27, 2012

altering data

Hello!

I have a problem that I really could use some help to solve. I have a table
which looks like this:

id1, id2, id3, rate, ratenr

Examples of a select * from this table would be:

1047336399 21000 1 617 1
1047336399 21000 1 624B 1
1047336399 21000 1 621D 1
1047336399 21000 2 624B 1
1047336399 21000 2 612A 1
1047336399 21000 2 621D 1
1047336399 21000 3 617 1
1047336399 21000 3 624B 1
1047336399 21000 3 621D 1

I would like to transform this table into something like this:

1047336399 21000 1 617 1 624B 1 621D 1
1047336399 21000 2 624B 1 612A 1 621D 1
1047336399 21000 3 617 1 624B 1 621D 1

the three first columns should be the primary key.

Any help is appreciated

Gunnar> I would like to transform this table into something like this:
> 1047336399 21000 1 617 1 624B 1 621D 1
> 1047336399 21000 2 624B 1 612A 1 621D 1
> 1047336399 21000 3 617 1 624B 1 621D 1

This is not a normalised result set. You shouldn't create a table like this
in your database because the rate columns are a repeating group (violation
of 1st Normal Form). Your initial table appears to be a normalised design.

If this is supposed to be crosstab report then do it in your application or
report generator. Or Google search this group for "crosstab" to find lots of
examples.

--
David Portas
----
Please reply only to the newsgroup
--sql

No comments:

Post a Comment