CREATING A VIEW

[Belle19]

I am trying to creat a virtual table by using a dynamic sql. This sql needs to be reuseable on different applications. To make matters more simple to explain let's say my table is a 3 column table. The ID column is a process identifier.

ID Integer
NAME VARCHAR
VALUE VARCHAR

The data will appear like this:

1 FIRSTNAME JOE
1 LASTNAME SMITH
2 FIRSTNAME MARY
2 LASTNAME JONES

I need to create a table that would take the data above and create the table like below

ID INTEGER
FIRSTNAME VARCHAR
LASTNAME VARCHAR

The data would look like this:

1 JOE SMITH
2 MARY JONES

Any ideas on this could work???? Please help!!!

 

[feherke]

Hi

This one need to have both records for each person.

[blue]mysql>[/blue] [b]select[/b] * [b]from[/b] belle19;
+------+-----------+-------+
| id   | name      | value |
+------+-----------+-------+
|    1 | firstname | Joe   |
|    1 | lastname  | Smith |
|    2 | firstname | Marry |
|    2 | lastname  | Jones |
+------+-----------+-------+
4 rows in set (0.00 sec)

[blue]mysql>[/blue] [b]select[/b] f.id,f.value,l.value [b]from[/b] belle19 f [/b]join belle19 l [b]using[/b] (id) [b]where[/b] f.name=[i]'firstname'[/i] [b]and[/b] l.name=[i]'lastname'[/i];
+------+-------+-------+
| id   | value | value |
+------+-------+-------+
|    1 | Joe   | Smith |
|    2 | Marry | Jones |
+------+-------+-------+
2 rows in set (0.01 sec)

See thread699-1265593 about a similar question in the PostgreSQL forum.

I would like to see the MySQL way for the second solution from the above mentioned thread. I did not found the equivalent of [tt]full join[/tt]. Someone any idea ? Thanks.

Feherke.

http://rootshell.be/~feherke/

 

[Belle19]

Yes I agree with your sql feherke, however I am attempting to create something that is more dynamic in that if a new value of column two was added such as middle name or address that I wouldn't have to go in and hard code the change. I like the sql you have given and have started something similar to that but I am trying to minimize changes. I would also like to use a standard (dynamic) sql on additional applications so that is also where the idea of not hard coding the values comes into play. Any further ideas???

 

[TonyGroves]

You can't do it using SQL alone. You would need to use program code to analyse the data, picking out the field names, and assembling appropriate CREATE TABLE and INSERT statements.

 

[Belle19]

That is what I figured however I am not familar with T-sql. I am trying to pull this information from a table on a 3rd party software and create a view for another 3rd party software (Reporting software) to use. My only option that I see thus far is creating a virtual table for the Reporting software to use. Any ideas on how to set this procedure up and running or do you know any website that could begin my journey on figuring this out. A little lost!!!!

 

[TonyGroves]

Almost any general-purpose programming language (such as Perl) would be capable of converting the table for you. That's the only feasible approach I can think of.

 

[KarveR]

ok my stab:

mysql> desc belle19;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(6)      | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| value | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+


mysql> select * from belle19;
+------+-----------+-------+
| id   | name      | value |
+------+-----------+-------+
|    1 | FIRSTNAME | JOE   |
|    1 | LASTNAME  | SMITH |
|    2 | FIRSTNAME | MARY  |
|    2 | LASTNAME  | JONES |
+------+-----------+-------+

mysql> create view v as select id,group_concat(value separator ' ') from belle19  group by id;

mysql> select * from v;
+------+-----------------------------------+
| id   | group_concat(value separator ' ') |
+------+-----------------------------------+
|    1 | JOE SMITH                         |
|    2 | MARY JONES                        |
+------+-----------------------------------+

is that about right?




______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[KarveR]

OK I read further down .. brainstorming, back later...

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.