Make homepage|Add to favorites
 

MYSQL TOOLS
reviews, articles, tips & snippets

 
Buy SQL » MySQL » Dynamic pivot tables (transform rows to columns)

Dynamic pivot tables (transform rows to columns)

Author: rootsql Date: 1-11-2011, 21:50

MySQL pivot table with dynamic headers.

 

Let’s assume we have a table of properties (a properties table) – ‘properties’ (script of its creation is given below), and we need to do data transformation for the report.

+----+---------+---------------+--------+
| id | item_id | property_name | value  |
+----+---------+---------------+--------+
|  1 |       1 | color         | blue   |
|  2 |       1 | size          | large  |
|  3 |       1 | weight        | 65     |
|  4 |       2 | color         | orange |
|  5 |       2 | weight        | 57     |
|  6 |       2 | size          | large  |
|  7 |       3 | size          | small  |
|  8 |       3 | color         | red    |
|  9 |       3 | weight        | 12     |
| 10 |       4 | color         | violet |
| 11 |       4 | size          | medium |
| 12 |       4 | weight        | 34     |
| 13 |       5 | color         | green  |
| 14 |       5 | weight        | 10     |
+----+---------+---------------+--------+
=>
+---------+--------+--------+--------+
| item_id | color  | size   | weight |
+---------+--------+--------+--------+
|       1 | blue   | large  | 65     |
|       2 | orange | large  | 57     |
|       3 | red    | small  | 12     |
|       4 | violet | medium | 34     |
|       5 | green  | NULL   | 10     |
+---------+--------+--------+--------+

As is generally known (as it is known), in MySql there is no function of automatically table transformation. Surely we can use some program (tool), which is able to connect to MySql and execute data transformation. But in this case we want to do it manually, and we have one variant – to write a query which will execute the data rotation in the table.

This query can have such type as:

SELECT
  item_id,
  MAX(IF(property_name = 'color', value, NULL)) AS color,
  MAX(IF(property_name = 'size', value, NULL)) AS size,
  ...
  ...
  ...
FROM
  properties
GROUP BY
  item_id;

As you can see – for each value `property_name` we need to do definite actions. It can be easy when the types of properties do not change. But what we should do if the values of properties in the column `property_name` often change or complement with the new ones? In this case we would change the query every time. In this case the algorithm of dynamic construction of this query can help us, this algorithm has to read all possible values of the column `property_name`, and on its basement create a query. The algorithm of query construction is:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(property_name = ''',
      property_name,
      ''', value, NULL)) AS ',
      property_name
    )
  ) INTO @sql
FROM properties;
SET @sql = CONCAT('SELECT item_id, ', @sql, ' FROM properties GROUP BY item_id');

As the result will be created the query:

SELECT
  item_id,
  MAX(IF(property_name = 'color', value, NULL)) AS color,
  MAX(IF(property_name = 'size', value, NULL)) AS size,
  MAX(IF(property_name = 'weight', value, NULL)) AS weight
FROM
  properties
GROUP BY
  item_id

(the query is formatted additionally for readability)

 

Note: the GROUP_CONCAT result length is limited to the value of the group_concat_max_len system variable, which has a default value of 1024. So, if you have large number of columns, then this value can be set higher before using GROUP_CONCAT function, for example:

SET @@group_concat_max_len = 5000;
SELECT GROUP_CONCAT(column_name) FROM table;

 

The query is written into variable @sql; now we can execute it with prepared statements:

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
+---------+--------+--------+--------+
| item_id | color  | size   | weight |
+---------+--------+--------+--------+
|       1 | blue   | large  | 65     |
|       2 | orange | large  | 57     |
|       3 | red    | small  | 12     |
|       4 | violet | medium | 34     |
|       5 | green  | NULL   | 10     |
+---------+--------+--------+--------+

 

Script of creation and filling of the table:

CREATE TABLE properties (
  id INT(11) NOT NULL AUTO_INCREMENT,
  item_id INT(11) DEFAULT NULL,
  property_name VARCHAR(255) DEFAULT NULL,
  value VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (id)
);
INSERT INTO properties VALUES 
  (1, 1, 'color', 'blue'),
  (2, 1, 'size', 'large'),
  (3, 1, 'weight', 65),
  (4, 2, 'color', 'orange'),
  (5, 2, 'weight', 57),
  (6, 2, 'size', 'large'),
  (7, 3, 'size', 'small'),
  (8, 3, 'color', 'red'),
  (9, 3, 'weight', 12),
  (10, 4, 'color', 'violet'),
  (11, 4, 'size', 'medium'),
  (12, 4, 'weight', 34),
  (13, 5, 'color', 'green'),
  (14, 5, 'weight', 10);

Tags: Tips

Dear visitor, you went to the site as unregistered user.
We recommend you to Register or Sign In.

Comments:

Leave a comment
  • oskar

  • 19 January 2013 06:58
  • Group: Guests
  • ICQ:
  • Registration: --
  • Comments: 0
  • Publications: 0
^
I have found your code of great help, but I have got stuck when I trid to use it slightly differently.

basically my table looks ike this
dt        ticker         open
1            A                1.2
1            B                 6
2            A                 1.4
2            B                 6.5

I would like it to turn out to be
dt           A            B
1            1.2         1.4
2            6            6.5


but it turns out to be
dt           A            B
1            1.2         NULL
1            NULL         1.4
2            6            NULL
2            NULL            6.5


the adjusted code I have used is

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      '(IF(ticker = ''',
      ticker,
      ''', open, NULL)) AS ''',
      ticker,''''
    )
  ) INTO @sql
FROM
  prices;

SET @sql = CONCAT('SELECT dt, ', @sql, ' FROM prices');
-- SET @sql = CONCAT('SELECT dt, ', @sql, ' FROM prices GROUP BY dt');

  PREPARE stmt FROM @sql;
  EXECUTE stmt;



would very much appreciate if you could help me out with this.
  • Group: Administrators
  • ICQ:
  • Registration: 27.09.2011
  • Comments: 5
  • Publications: 36
^
Hi, Oskar.

You should use aggregate function, for example - MAX()

SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(ticker =...
  • dp

  • 10 April 2013 11:49
  • Group: Guests
  • ICQ:
  • Registration: --
  • Comments: 0
  • Publications: 0
^
I'm seeing sysntax error in this code.
  • Group: Chief editors
  • ICQ:
  • Registration: 8.11.2011
  • Comments: 1
  • Publications: 7
^
dp,
hmm strange. code is correct. The query is executed without error. If you have an error, specify what?
  • Jonas

  • 17 May 2013 08:25
  • Group: Guests
  • ICQ:
  • Registration: --
  • Comments: 0
  • Publications: 0
^
I have a table 'narvarorapportering' with fields 'amne', 'lektionstillfalle', 'narvarande', 'efternamn'.

I have replaced tablename and fields in your code with my own like this:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(lektionstillfalle = ''',
      lektionstillfalle,
      ''', narvarande, NULL)) AS ',
      lektionstillfalle
    )
  ) INTO @sql
FROM
  narvarorapportering;
SET @sql = CONCAT('SELECT efternamn, ', @sql, ' FROM narvarorapportering GROUP BY efternamn');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;



When trying this I get the following error message:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '36,MAX(IF(lektionstillfalle = 'v 37', narvarande, NULL)) AS v 37,MAX(IF(lektions' at line 1

Any help on what could be wrong would be really great!

/Jonas
  • Group: Administrators
  • ICQ:
  • Registration: 27.09.2011
  • Comments: 5
  • Publications: 36
^
Check that table is not empty, and lektionstillfalle column has no NULL values.
  • Jonas

  • 17 May 2013 09:30
  • Group: Guests
  • ICQ:
  • Registration: --
  • Comments: 0
  • Publications: 0
^
...the table is not empty and the column 'lektionstillfalle' has no NULL values...

/jonas
  • admin

  • 17 May 2013 10:36
  • Group: Administrators
  • ICQ:
  • Registration: 23.01.2011
  • Comments: 11
  • Publications: 2
^
Jonas,
If the names of the future columns contains a space, then there is a syntax error.
try:

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(lektionstillfalle = ''',
lektionstillfalle,
''', narvarande, NULL)) AS ',
CONCAT("`",lektionstillfalle,"`")
)
) INTO @sql
FROM
narvarorapportering;
SET @sql = CONCAT('SELECT efternamn, ', @sql, ' FROM narvarorapportering GROUP BY efternamn');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
  • Jonas

  • 17 May 2013 10:45
  • Group: Guests
  • ICQ:
  • Registration: --
  • Comments: 0
  • Publications: 0
^
I really appreciate you helping me with this and for your quick reply!

When trying this I get a different error message:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM narvarorapportering GROUP BY efternamn' at line 1

/Jonas
  • admin

  • 17 May 2013 10:52
  • Group: Administrators
  • ICQ:
  • Registration: 23.01.2011
  • Comments: 11
  • Publications: 2
^
Jonas,
show DDL of the narvarorapportering table.
 SHOW CREATE TABLE narvarorapportering; 
  • Jonas

  • 17 May 2013 11:06
  • Group: Guests
  • ICQ:
  • Registration: --
  • Comments: 0
  • Publications: 0
^
CREATE TABLE `narvarorapportering` (
 `IDnarvrap` int(5) NOT NULL auto_increment,
 `larare` varchar(30) character set utf8 collate utf8_swedish_ci default NULL,
 `amne` varchar(30) character set utf8 collate utf8_swedish_ci default NULL,
 `lektionstillfalle` varchar(30) character set utf8 collate utf8_swedish_ci default NULL,
 `narvarande` enum('ja','nej') character set utf8 collate utf8_swedish_ci default NULL,
 `fornamn` varchar(20) character set utf8 collate utf8_swedish_ci default NULL,
 `efternamn` varchar(20) character set utf8 collate utf8_swedish_ci default NULL,
 `langd` time NOT NULL,
 PRIMARY KEY  (`IDnarvrap`)
) ENGINE=MyISAM AUTO_INCREMENT=8134 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
  • admin

  • 17 May 2013 11:13
  • Group: Administrators
  • ICQ:
  • Registration: 23.01.2011
  • Comments: 11
  • Publications: 2
^
also show result of the query:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(lektionstillfalle = ''',
lektionstillfalle,
''', narvarande, NULL)) AS ',
CONCAT("`",lektionstillfalle,"`")
)
) INTO @sql
FROM
narvarorapportering;
SET @sql = CONCAT('SELECT efternamn, ', @sql, ' FROM narvarorapportering GROUP BY efternamn');


SELECT @sql;
  • Jonas

  • 17 May 2013 11:18
  • Group: Guests
  • ICQ:
  • Registration: --
  • Comments: 0
  • Publications: 0
^
SELECT efternamn, MAX(IF(lektionstillfalle = '36b', narvarande, NULL)) AS `36b`,MAX(IF(lektionstillfalle = '45b', narvarande, NULL)) AS `45b`,MAX(IF(lektionstillfalle = 'Vecka 36', narvarande, NULL)) AS `Vecka 36`,MAX(IF(lektionstillfalle = 'v 37', narvarande, NULL)) AS `v 37`,MAX(IF(lektionstillfalle = 'v 37:2', narvarande, NULL)) AS `v 37:2`,MAX(IF(lektionstillfalle = 'v 38', narvarande, NULL)) AS `v 38`,MAX(IF(lektionstillfalle = 'v 38:2', narvarande, NULL)) AS `v 38:2`,MAX(IF(lektionstillfalle = '35', narvarande, NULL)) AS `35`,MAX(IF(lektionstillfalle = 'v 40', narvarande, NULL)) AS `v 40`,MAX(IF(lektionstillfalle = 'v 40:2', narvarande, NULL)) AS `v 40:2`,MAX(IF(lektionstillfalle = 'v 41', narvarande, NULL)) AS `v 41`,MAX(IF(lektionstillfalle = 'v 41:2', narvarande, NULL)) AS `v 41:2`,MAX(IF(lektionstillfalle = 'v 2', narvarande, NULL)) AS `v 2`,MAX(IF(lektionstillfalle = 'v 42', narvarande, NULL)) AS `v 42`,MAX(IF(lektionstillfalle = 'v 42:2', narvarande, NULL)) AS `v 42:2`,MAX(IF(lektionstillfalle = 'v 45', narvarande, FROM narvarorapportering GROUP BY efternamn
  • admin

  • 17 May 2013 12:23
  • Group: Administrators
  • ICQ:
  • Registration: 23.01.2011
  • Comments: 11
  • Publications: 2
^
Increase the variable group_concat_max_len
for example:
SET group_concat_max_len=2048;
  • Jonas

  • 17 May 2013 12:46
  • Group: Guests
  • ICQ:
  • Registration: --
  • Comments: 0
  • Publications: 0
^
When I try as below I get no error message. The query is executed correctly.
However, it comes back empty. The query gives back 0 rows.
Again, thanks for your help!

SET group_concat_max_len=5000;
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(lektionstillfalle = ''',
lektionstillfalle,
''', narvarande, NULL)) AS ',
CONCAT("`",lektionstillfalle,"`")
)
) INTO @sql
FROM
narvarorapportering;
SET @sql = CONCAT('SELECT efternamn, ', @sql, ' FROM narvarorapportering GROUP BY efternamn');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
  • Group: Users
  • ICQ:
  • Registration: 22.05.2013
  • Comments: 2
  • Publications: 0
^
I am fairly new to mySQL (previously used ms-sql). I think I am having the same problem as Jonas - when I run the query, it says it was executed successfully, and the output ends with this feedback:

EXECUTE stmt;# Rows: 8
DEALLOCATE PREPARE stmt;# MySQL returned an empty result set (i.e. zero rows).

But the 8 rows that supposedly were affected are not displayed. Is there something I'm missing?
  • admin

  • 22 May 2013 20:05
  • Group: Administrators
  • ICQ:
  • Registration: 23.01.2011
  • Comments: 11
  • Publications: 2
^
tkbell,
to reproduce the issue show DDL of the table and a few rows of data as insert.

Jonas,
Could you show some records of the table.
  • Group: Users
  • ICQ:
  • Registration: 22.05.2013
  • Comments: 2
  • Publications: 0
^
The query I am using references 2 tables: cms_items and cms_headers. Below is the structure and some data for each and then the query I used, with the results.

-- Table structure for table `cms_items`
CREATE TABLE `cms_items` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`hid` int(11) NOT NULL,
`iid` int(11) NOT NULL DEFAULT '0',
`descript` text NOT NULL,
`order` tinyint(4) NOT NULL,
PRIMARY KEY (`id`),
KEY `hid` (`hid`),
KEY `iid` (`iid`),
KEY `order` (`order`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=29 ;

INSERT INTO `cms_items` (`id`, `hid`, `iid`, `descript`, `order`) VALUES
(2, 1, 0, 'Robust, 45 screw terminal', 2),
(3, 1, 0, 'Reversible door flips, to conveniently show points list', 3),
(4, 2, 0, 'Universal ranges', 1),
(5, 3, 0, 'Outputs 1-4 are wired to unpopulated sockets', 4);

-- Table structure for table `cms_headers`
CREATE TABLE `cms_headers` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sid` int(11) NOT NULL,
`descript` text NOT NULL,
`order` tinyint(4) NOT NULL,
PRIMARY KEY (`id`),
KEY `type` (`sid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

INSERT INTO `cms_headers` (`id`, `sid`, `descript`, `order`) VALUES
(1, 1, 'Superior Design', 1),
(2, 1, '12 Inputs', 2),
(3, 1, '8 Outputs', 0);

-- Pivot Query (with results)
SET @@group_concat_max_len = 5000;# MySQL returned an empty result set (i.e. zero rows).

SET @sql = NULL;# MySQL returned an empty result set (i.e. zero rows).

SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'MAX(IF(cms_headers.descript = ''', cms_headers.descript,''', cms_items.descript, NULL)) AS ',
CONCAT("`",cms_headers.descript,"`")
)
) INTO @sql
FROM cms_headers join cms_items on cms_items.hid = cms_headers.id;# Affected rows: 1

SET @sql = CONCAT('SELECT cms_headers.descript, ', @sql, ' FROM cms_headers join cms_items on cms_items.hid = cms_headers.id GROUP BY descript');# MySQL returned an empty result set (i.e. zero rows).

PREPARE stmt FROM @sql;# MySQL returned an empty result set (i.e. zero rows).

EXECUTE stmt;# Rows: 8

DEALLOCATE PREPARE stmt;# MySQL returned an empty result set (i.e. zero rows).
  • admin

  • 22 May 2013 22:13
  • Group: Administrators
  • ICQ:
  • Registration: 23.01.2011
  • Comments: 11
  • Publications: 2
^
If you are using the phpMyAdmin as mysql client, try execute query without last statment.
Quote: tkbell
DEALLOCATE PREPARE stmt;# MySQL returned an empty result set (i.e. zero rows).


P.s.
EXECUTE stmt;# Rows: 8

it means that the query returned 8 rows. Unfortunately phpMyAdmin displays latest result of the last statment, which is (i.e. zero rows).
  • jonas

  • 24 May 2013 13:00
  • Group: Guests
  • ICQ:
  • Registration: --
  • Comments: 0
  • Publications: 0
^
Here are some records from my table:

INSERT INTO `narvarorapportering` (`IDnarvrap`, `larare`, `amne`, `lektionstillfalle`, `narvarande`, `fornamn`, `efternamn`, `langd`) VALUES
(384, 'Kristina Ugelstad', 'Kammarensemble', '36b', '', 'Magdalena', 'karlsson', '01:20:00'),
(416, 'Kristina Ugelstad', 'Mentorstid', 'Vecka 36', 'ja', 'David', 'Storm', '00:40:00'),
(420, 'Kristina Ugelstad', 'Mentorstid', 'v 37', 'ja', 'Edith', 'Bremer', '00:40:00'),
(422, 'Kristina Ugelstad', 'Mentorstid', 'v 37', 'ja', 'Asim', 'Mayindu', '00:40:00'),
(424, 'Kristina Ugelstad', 'Mentorstid', 'v 37', '', 'David', 'Storm', '00:40:00'),
(479, 'Johan Jrnland', 'Gemensam lektion', 'v 38', 'ja', 'Ardalan', 'Azadi', '01:00:00'),
(481, 'Johan Jrnland', 'Gemensam lektion', 'v 38', 'ja', 'Edvin', 'Diskin Wetter', '01:00:00'),
(483, 'Johan Jrnland', 'Gemensam lektion', 'v 38', '', 'Gursel', 'Hasanov', '01:00:00'),
(489, 'Johan Jrnland', 'Gemensam lektion', 'v 38', '', 'Anton', 'Nessvi', '01:00:00'),
(516, 'Johan Jrnland', 'Musikaliskt hantverk', 'v 38', 'ja', 'Emelie', 'Sjstrm', '01:40:00'),
(519, 'Johan Jrnland', 'Musikaliskt hantverk', 'v 38', 'ja', 'Tomas', 'Wester', '01:40:00'),
(523, 'Elin Bergstrand', 'Afroensemble', 'v 37', 'ja', 'Robin', 'Petersson', '01:00:00'),
(524, 'Elin Bergstrand', 'Afroensemble', 'v 37', 'ja', 'Emelie', 'Sjstrm', '01:00:00'),

/Jonas
  • admin

  • 24 May 2013 14:09
  • Group: Administrators
  • ICQ:
  • Registration: 23.01.2011
  • Comments: 11
  • Publications: 2
^
jonas,
The query works correctly.
pivot tables

Maybe you having the same problem as tkbell..
Did you execute the query in phpMyAdmin? If so, try execute the query without last statement.
  • Group: Guests
  • ICQ:
  • Registration: --
  • Comments: 0
  • Publications: 0
^
I'm having a diferente problem I count get any mention about this... I have in a exported table 93 columns, but only train, equipament are important to me; I need to have a count() from grouped information from train and equipment, but equipment must be a column, because I dont have records from all equipements for all trains to show in a stacked bars graph. So, my result shoud be like above:

train, eq1, eq2,eq3,...
train1 1, 0, 0, ,...
train2 0, 10, 0, ,...

Any ideas to have this result???
  • Group: Users
  • ICQ:
  • Registration: 27.07.2013
  • Comments: 1
  • Publications: 0
^
First of all,

thank you for writing such a brilliant article.
I only have one question to ask:

Is there any way that i can send the output of this command to a table so as to extract the data to a csv file?

Thsnk you very much!
  • admin

  • 30 July 2013 15:00
  • Group: Administrators
  • ICQ:
  • Registration: 23.01.2011
  • Comments: 11
  • Publications: 2
^
Tepes4,
Quote: Tepes4
Is there any way that i can send the output of this command to a table so as to extract the data to a csv file?


Just add the "CREATE TABLE New_Table_name AS" at the beginning of @sql
SET @sql = CONCAT('CREATE TABLE New_Table_name AS SELECT item_id, ', @sql, ' FROM properties GROUP BY item_id'); 
  • Group: Guests
  • ICQ:
  • Registration: --
  • Comments: 0
  • Publications: 0
^
Is it possible to add a where clauses to it? For example if I only want the sizes small from the properties?
  • Group: Guests
  • ICQ:
  • Registration: --
  • Comments: 0
  • Publications: 0
^
This post is helped me alot but I have slightly complex Requirements
CREATE TABLE `tbl_header` (
	`HeaderId` BIGINT(20) NOT NULL AUTO_INCREMENT,
	`OrderId` BIGINT(20) NOT NULL DEFAULT '0',
	`PageNo` INT(11) NULL DEFAULT NULL,
	`RowNo` INT(11) NULL DEFAULT NULL,
	`ColNo` INT(11) NULL DEFAULT NULL,
	`Value` VARCHAR(250) NULL DEFAULT NULL,
	`Attribute` VARCHAR(250) NULL DEFAULT NULL,
	PRIMARY KEY (`HeaderId`)
	)



this is table I need the result
convert value column into row based on PageNo ,and RowNo
  • Group: Administrators
  • ICQ:
  • Registration: 27.09.2011
  • Comments: 5
  • Publications: 36
^
Patrick,
Is it possible to add a where clauses to it? For example if I only want the sizes small from the properties?
Yes, it is possible. Use a subquery -
SET @sql = CONCAT('SELECT * FROM (SELECT item_id, ', @sql, ' FROM properties GROUP BY item_id) t WHERE size = ''small''');


Basavaraj,
Please add more details to your question.
  • Group: Users
  • ICQ:
  • Registration: 5.03.2014
  • Comments: 2
  • Publications: 0
^
Hello, I found your article very useful for my problem. More I have a doubt: I currently do a left join to cross information between my customer table and my table these phones customers a customer can have more than one phone. unfortunately the person who was working on this project before I had the bright idea of accumulating data and add 1 per phone line. why a client may have in the phones table 4 rows for his phones. I need to extract this data from the database when I put it increases the number of lines. I wonder if it is possible I dynamically create a column for each phone? example: if a customer has 20 lines with phones, turn into 20 columns?


I thank anyone who can help me.
  • admin

  • 5 March 2014 14:53
  • Group: Administrators
  • ICQ:
  • Registration: 23.01.2011
  • Comments: 11
  • Publications: 2
^
jorgeslima,

In this example, show in what format you need to get data from the database?
What should be the result table?
  • Group: Users
  • ICQ:
  • Registration: 5.03.2014
  • Comments: 2
  • Publications: 0
^
admin,

A quick example of what i need is:

Table 1 (customers):

	+----+--------------+
	| ID |     NAME     |
	+----+--------------+
	| 1  | Jorge Lima   |
	| 2  | John Lennon  |
	| 3  | Barack Obama |
	+----+--------------+

Table 2 (phones):
	
	+----+---------+----------+
	| ID | CUST_ID |  PHONE   |
	+----+---------+----------+
	| 1  | 1       | 12345543 |
	| 2  | 1       | 98765432 |
	| 3  | 1       | 23456789 |
	| 4  | 2       | 12345675 |
	| 5  | 2       | 98765678 |
	+----+---------+----------+

Desired reult:

	+----+--------------+----------+----------+----------+
	| ID |     NAME     |  PHONE   |  PHONE1  |  PHONE2  |
	+----+--------------+----------+----------+----------+
	| 1  | Jorge Lima   | 12345543 | 98765432 | 23456789 |
	| 2  | John Lennon  | 12345675 | 98765678 | (null)   |
	| 3  | Barack Obama | (null)   | (null)   | (null)   |
	+----+--------------+----------+----------+----------+

not has to be exactly what is in the table result above but, something like the table result above will be very very useful.

I will be thankful for any kind of help like documentation where i can find some explanation about what i need, or maybe few examples of similar scenarios where the problem was solved

anyway, thanks for the help for now...

Previous Next
Go to top
Information
Members of Guests cannot leave comments.