1

Topic: Data output from several tables definitely.

Hour kind. I ask not to kick, I do not know as to formulate a question what to set to its Google and to explain to the searcher on a forum that is necessary for me. Thanks for understanding!
In general, there are 2 tables:
table 1
id, option
1, option_1
2, option_2
3, option_3
table 2
id, field, value
1, field_1, value_1
1, field_2, value_2
2, field_1, value_3
2, field_2, value_4
3, field_1, value_5
3, field_2, value_6
It is necessary for me to deduce the initial table:
result table
id, option, field_1, field_2
1, option_1, value_1, value_2
2, option_2, value_3, value_4
3, option_3, value_5, value_6
I can not pick up a word for thought presentation, in terminology a broom. And as a whole with SQL only on you. Thanks huge!

2

Re: Data output from several tables definitely.

To search by word PIVOT. It is possible simply in this section of a forum.

3

Re: Data output from several tables definitely.

See to Generate summary view (phpmyadmin)
Help to turn the table
Etc.

4

Re: Data output from several tables definitely.

Forgive but there at all that. And to turn that it is not necessary.

5

Re: Data output from several tables definitely.

Akina wrote:

to Search by word PIVOT. It is possible simply in this section of a forum.

In the table number 2 can be and field_3, field_4, field_n and in  values are explicitly specified.
I see it so: we Select all   and we transform them into table titles, we add necessary titles and we place values according to titles.

6

Re: Data output from several tables definitely.

And what for to you, actually, columns? Ready value for certain is necessary in the line to you to interpose somewhere
group_concat ()

7

Re: Data output from several tables definitely.

-- GROUP_CONCAT - The best method to collect phones in a small group: http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat
GROUP_CONCAT (DISTINCT ' +7 (', t5. ` code `, ') ', SUBSTR (t5. ` num `, 1,3), ' - ', SUBSTR (t5. ` num `, 4,7) ORDER BY t5. ` cnt ` SEPARATOR ', <br> ') AS ` phone `;

8

Re: Data output from several tables definitely.

tip78 wrote:

-- GROUP_CONCAT - The best method to collect phones in a small group: http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat
GROUP_CONCAT (DISTINCT ' +7 (', t5. ` code `, ') ', SUBSTR (t5. ` num `, 1,3), ' - ', SUBSTR (t5. ` num `, 4,7) ORDER BY t5. ` cnt ` SEPARATOR ', <br> ') AS ` phone `;

No, unfortunately it is necessary how  in topic start.

9

Re: Data output from several tables definitely.

tip78 wrote:

and what for to you, actually, columns? Ready value for certain is necessary in the line to you to interpose somewhere
group_concat ()

I here now viewed a pack, a bale of examples about PIVOT but any example with a banal turn of the table without any operations. If you could tear to me the second table (without manipulations with values), the decision itself would float on a surface.
I see it approximately so.
SELECT * FROM (SELECT * FROM ' table_2 ' GROUP BY ' id ') PIVOT (??);

10

Re: Data output from several tables definitely.

stweet;
Because is not able mysql pivot.
It is possible to get out only knowing the list of values or to tear on application already.

11

Re: Data output from several tables definitely.

stweet wrote:

it is passed...
I here now viewed a pack, a bale of examples about PIVOT but any example with a banal turn of the table without any operations. If you could tear to me the second table (without manipulations with values), the decision itself would float on a surface.
I see it approximately so.
SELECT * FROM (SELECT * FROM ' table_2 ' GROUP BY ' id ') PIVOT (??);

Well is not present at  arrays, record
Do a subquery on each column then

12

Re: Data output from several tables definitely.

And, well, their unpredictable kol-in...
Not, to you here it is certain group_concat ()
You generally in course that is not mandatory in   results, are more correct than them just in a DB at once in the necessary format to result

13

Re: Data output from several tables definitely.

stweet wrote:

And to turn that it is not necessary.

it is necessary, , it is necessary. After binding of tables you will receive sampling which and it is necessary to you .

stweet wrote:

In the table number 2 can be and field_3, field_4, field_n and in  values are explicitly specified.

And here you in vain clamped this convergence in the initial message - they are important. And because of them the references given to your are inapplicable.

Melkij wrote:

it is possible to Get out only knowing the list of values

For this reason I wrote procedure. See PIVOT means MySQL .
stweet , directly my procedure here is inapplicable, for works with the single table. But it can and be corrected, whether not so? We tell, transferring not a table name, and prepared section FROM.

14

Re: Data output from several tables definitely.

Melkij wrote:

stweet;
Because is not able mysql pivot.
It is possible to get out only knowing the list of values or to tear on application already.

Thanks! We admit we know the list of values. How in that case to implement the task in topic start?

15

Re: Data output from several tables definitely.

Akina wrote:

it is passed...
It is necessary, , it is necessary. After binding of tables you will receive sampling which and it is necessary to you .
it is passed...
And here you in vain clamped this convergence in the initial message - they are important. And because of them the references given to your are inapplicable.
it is passed...
For this reason I wrote procedure. See PIVOT means MySQL .
stweet , directly my procedure here is inapplicable, for works with the single table. But it can and be corrected, whether not so? We tell, transferring not a table name, and prepared section FROM.

I am afraid my task it is restricted by individual request = (

16

Re: Data output from several tables definitely.

stweet wrote:

my task is restricted by individual request

Means, it is possible to generate from my code procedure under this request. Or individual is which once launched, and more never it is required?

17

Re: Data output from several tables definitely.

Akina wrote:

it is passed...
Means, it is possible to generate from my code procedure under this request. Or individual is which once launched, and more never it is required?

Yes I already such  made. And  . (It is not beautiful, but other conditions were not, we will be  and to defy).
select
table_1.id,
table_1.option,
table_2.value as field_1;
table_3.value as field_2
from
table_1
left join (select id, value from table_2 where field ='field_1 ') as table_2 on table_2.id=table_1.id
left join (select id, value from table_2 where field ='field_2 ') as table_3 on table_3.id=table_1.id;

18

Re: Data output from several tables definitely.

stweet wrote:

I already such  made.

If the request you suits THIS - that those links that I gave above, approach as well as possible.

19

Re: Data output from several tables definitely.

Akina wrote:

it is passed...
If the request you suits THIS - that those links that I gave above, approach as well as possible.

Thanks are finite. I so am far in sql did not look. I there really do not understand that to what?!)))

20

Re: Data output from several tables definitely.

That there to understand? Grouping yes a maximum choice... Easier unless the multiplication table.

21

Re: Data output from several tables definitely.

Akina wrote:

That there to understand? Grouping yes a maximum choice... Easier unless the multiplication table.

Well, it agree. Here and to understand that there is nothing.

DELIMITER @@;
DROP PROCEDURE IF EXISTS pivot @@;
CREATE PROCEDURE pivot (IN schema_name VARCHAR (64)/* database name */
, IN table_name VARCHAR (64)/* table name */
, IN id_name VARCHAR (64)/* row values field name */
, IN key_name VARCHAR (64)/* col values field name,
must be char or varchar type
and <= 64 chars long */
, IN value_name VARCHAR (64)/* val values field name */
)
pivot:BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND SET @error: = 1;
SET @error: = 0;
SELECT character_maximum_length
INTO @maxlen
FROM information_schema.columns
WHERE table_schema = schema_name
AND table_name = table_name
AND column_name = key_name
AND data_type IN (' char ', ' varchar ');
IF @error OR !@maxlen OR @maxlen IS NULL THEN
SELECT ' @error OR @maxlen=0 OR @maxlen IS NULL ', @error, @maxlen;
LEAVE pivot;
END IF;
DROP TEMPORARY TABLE IF EXISTS temp_pivot;
SET @sql: = CONCAT (' CREATE TEMPORARY TABLE temp_pivot (key_name VARCHAR (';
@maxlen;
')) ENGINE=Memory SELECT DISTINCT ` ';
key_name;
' ` key_name FROM ` ';
schema_name;
' `. ` ';
table_name;
' `; ');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
SELECT GROUP_CONCAT (CONCAT (', MAX (CASE ` ';
key_name;
' ` WHEN ' ";
temp_pivot.key_name;
' "THEN ` ';
value_name;
' ` END) ` ';
temp_pivot.key_name;
' ` ') SEPARATOR ")
INTO @sql
FROM temp_pivot;
DROP TEMPORARY TABLE temp_pivot;
SET @sql: = CONCAT (' SELECT ` ';
id_name;
' ` ';
@sql;
' FROM ` ';
schema_name;
' `. ` ';
table_name;
' ` GROUP BY ` ';
id_name;
' `; ');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DROP PREPARE stmt;
SET @error: = NULL;
SET @maxlen: = NULL;
SET @sql: = NULL;
END pivot @@;

22

Re: Data output from several tables definitely.

stweet, you at all do not hesitate to show that read answers through a word.
and I gave slightly earlier. And as absolutely other type of the decision, and in general of other task. But you and them, probably, too looked through a word - after all there well simply one in one same task.