Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /homepages/28/d448670958/htdocs/wp-content/plugins/latex/latex.php on line 47

Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /homepages/28/d448670958/htdocs/wp-content/plugins/latex/latex.php on line 48

Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /homepages/28/d448670958/htdocs/wp-content/plugins/latex/latex.php on line 47

Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /homepages/28/d448670958/htdocs/wp-content/plugins/latex/latex.php on line 48

Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /homepages/28/d448670958/htdocs/wp-content/plugins/latex/latex.php on line 47

Warning: preg_replace(): The /e modifier is no longer supported, use preg_replace_callback instead in /homepages/28/d448670958/htdocs/wp-content/plugins/latex/latex.php on line 48

This post has already been read 4961 times!

I have a very simple table with three data columns: Date1, Report_#, Name. I would like to pivot it around Date and the various names as the header tab, and have all the report numbers appear below.


So it would LOOK like this:
Report | Date | Name Date | Name1 | Name2 | Name3
----------------------- ------------------------------
1 | 4-5-12 | Name1 4-5-12 | 1 | 2 | 3
2 | 4-5-12 | Name2 -----> 4-6-12 | 4 | 5 | 6
3 | 4-5-12 | Name3 4-7-12 | 7 | 8 | 9
4 | 4-6-12 | Name1
5 | 4-6-12 | Name2
6 | 4-6-12 | Name3
7 | 4-7-12 | Name1
8 | 4-7-12 | Name2
9 | 4-7-12 | Name3

It's difficult to tell exactly what it is you're trying to do, but if you're attempting to create a procedure that can perform arbitrary pivots then you'll need to provide it with more arguments (such as the column on which to pivot and the column in which values can be found).

Furthermore, you will need to create a prepared statement from within a prepared statement. The outermost statement will use GROUP_CONCAT() to construct the GROUP_CONCAT() expressions that are to be executed, based on the unique values in the specified column:


CREATE FUNCTION SQL_ESC(_identifier VARCHAR(64))
RETURNS VARCHAR(130) DETERMINISTIC
RETURN CONCAT('`',REPLACE(_identifier,'`','``'),'`')//

CREATE PROCEDURE writecountpivot(
IN _db_nm VARCHAR(64),
IN _tb_nm VARCHAR(64),
IN _cl_gp VARCHAR(64),
IN _cl_pv VARCHAR(64),
IN _cl_vl VARCHAR(64)
) BEGIN
SET @sql := CONCAT(
"SELECT CONCAT('
SELECT ",SQL_ESC(_cl_gp),",',
GROUP_CONCAT(DISTINCT CONCAT(
'GROUP_CONCAT(IF(",
SQL_ESC(_cl_pv),"=',QUOTE(",SQL_ESC(_cl_pv),"),'
, ",SQL_ESC(_cl_vl),"
, NULL
)) AS ',SQL_ESC(",SQL_ESC(_cl_pv),")
)), '
FROM ",SQL_ESC(_db_nm),".",SQL_ESC(_tb_nm),"
GROUP BY ",SQL_ESC(_cl_gp),"
')
INTO @sql
FROM ",SQL_ESC(_db_nm),".",SQL_ESC(_tb_nm)
);

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

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

See that to SQL Fiddle :
http://sqlfiddle.com/#!2/7cd76/1


CREATE TABLE `IR #`(`Report` int, `Date` datetime, `Name` varchar(5))

INSERT INTO `IR #`
(`Report`, `Date`, `Name`)
VALUES
(1, '2012-04-05', 'Name1'),
(2, '2012-04-05', 'Name2'),
(3, '2012-04-05', 'Name3'),
(4, '2012-04-06', 'Name1'),
(5, '2012-04-06', 'Name2'),
(6, '2012-04-06', 'Name3'),
(7, '2012-04-07', 'Name1'),
(8, '2012-04-07', 'Name2'),
(9, '2012-04-07', 'Name3')

SELECT * FROM `ir #` i;

DELIMITER
CREATE PROCEDURE `test`.`writecountpivot` (
IN _db_nm VARCHAR(64),
IN _tb_nm VARCHAR(64),
IN _cl_gp VARCHAR(64),
IN _cl_pv VARCHAR(64),
IN _cl_vl VARCHAR(64)
)
BEGIN
SET @sql := CONCAT(
"SELECT CONCAT('
SELECT ",SQL_ESC(_cl_gp),",',
GROUP_CONCAT(DISTINCT CONCAT(
'GROUP_CONCAT(IF(",
SQL_ESC(_cl_pv),"=',QUOTE(",SQL_ESC(_cl_pv),"),'
, ",SQL_ESC(_cl_vl),"
, NULL
)) AS ',SQL_ESC(",SQL_ESC(_cl_pv),")
)), '
FROM ",SQL_ESC(_db_nm),".",SQL_ESC(_tb_nm),"
GROUP BY ",SQL_ESC(_cl_gp),"
')
INTO @sql
FROM ",SQL_ESC(_db_nm),".",SQL_ESC(_tb_nm)
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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

DROP FUNCTION IF EXISTS `test`.`SQL_ESC`

DELIMITER ;

AND CALL THE PROCEDURE


CALL writecountpivot(DATABASE(),'IR #','Date','Name','Report');

Comments are closed.

Post Navigation