SQL Group By And Display With Different Values

Question

I want to create group query where Table values are like this below:

EMP_ID  ProjectID
815     1
985     1
815     3
985     4
815     4

And i want output like this

EMP_ID ProjectID1 ProjectID2 ProjectID3
815    1          3          4
985    1          4          0

can anyone know how can i achieve this thing in SQL query.

Thank in advance.

it's a PIVOT....http://stackoverflow.com/questions/1241178/mysql-rows-to-columns — Mitch Wheat, Mar 18 '12 at 08:12
@Mitch Wheat please note in second column in the example .. I know you can easily get that this question is not under pivot queries ... in pivot column will have same values, if exist, or null only.. — pratik garg, Mar 18 '12 at 10:42

score 2 · Accepted Answer · answered Mar 18 '12 at 12:03

The short way:

Using http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

SELECT
  tbl.emp_id,
  GROUP_CONCAT( DISTINCT project_id ) project_id_list
FROM tbl
GROUP BY tbl.emp_id

In this case, you have to split/process the concatenated project_id_list string (or NULL) in your application

The long way:

We will use a little trick:

http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html

For MyISAM tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

CREATE TEMPORARY TABLE temp (
  emp_id      INT NOT NULL,
  -- project_num will count from 1 to N PER emp_id!
  project_num INT NOT NULL AUTO_INCREMENT,
  project_id  INT NOT NULL,
  PRIMARY KEY ( emp_id, project_num )
) ENGINE=MyISAM; -- works only with myisam!

Generate the per-group auto increments:

INSERT INTO temp ( emp_id, project_id )
SELECT emp_id, project_id FROM tbl

Calculate how many project_id columns are needed:

$MAX_PROJECTS_PER_EMP =
  SELECT MAX( max_projects_per_emp ) FROM
    ( SELECT COUNT(*) AS max_projects_per_emp project_id FROM tbl GROUP BY emp_id )

Programmatically create the select expression:

SELECT
  temp.emp_id,
  t1.project_id  AS project_id_1,
  t2.project_id  AS project_id_2,
  t98.project_id AS project_id_98,
  t99.project_id AS project_id_99,
FROM      temp
LEFT JOIN temp AS t1 ON temp.emp_id = t1.id AND t1.project_num = 1
LEFT JOIN temp AS t2 ON temp.emp_id = t2.id AND t1.project_num = 2
// create $MAX_PROJECTS_PER_EMP lines of LEFT JOINs
LEFT JOIN temp AS t98 ON temp.emp_id = t98.id AND t98.project_num = 98
LEFT JOIN temp AS t99 ON temp.emp_id = t99.id AND t99.project_num = 99

Thanks a lote,for your replied. it's working fine.I wont some another help from you, please help me. i have add one new field also project_cost, now what i can do to, get both project_id and project_cost in one row with GROUP_CONCAT. — Riddhish.Chaudhari, Mar 18 '12 at 15:48
i got my query , now my problem is solved, once's again thanks. — Riddhish.Chaudhari, Mar 18 '12 at 15:56

SQL Group By And Display With Different Values

1 Answers1

The short way:

The long way: