title: Mysql : Pivot table According to status
body: <p>My DB structure and details below </p>
  
  <pre><code>CREATE TABLE `tbldakmst` (
  `inwardId` int(11) NOT NULL,
  `dakType` int(3) DEFAULT NULL,
  `dakMarkTo` int(4) DEFAULT NULL,
  `letterNo` varchar(100) DEFAULT NULL,
  `markDeptt` varchar(20) DEFAULT NULL,
  `dakSenderName` varchar(256) DEFAULT NULL,
  `subject` varchar(256) DEFAULT NULL,
  `remarks` text,
  `comments` text,
  `handoverDate` datetime DEFAULT NULL,
  `handoverTo` int(11) DEFAULT NULL,
  `forwardedTo` int(11) DEFAULT NULL,
  `forwardedOn` datetime DEFAULT NULL,
  `forwardedFrom` int(11) DEFAULT NULL,
  `status` enum('0','1','2','3','4','5') DEFAULT NULL COMMENT '0=&gt;CREATED, 1=&gt;MARK TO, 2 =&gt; FORWARD TO, 3=&gt;INITIATED, 4=&gt;INITIATED AND DISPOSED, 5=&gt;DISPOSED',
  `createdBy` int(11) DEFAULT NULL,
  `createdOn` datetime DEFAULT CURRENT_TIMESTAMP,
  `modifiedBy` int(11) DEFAULT NULL,
   `modifiedOn` datetime DEFAULT NULL,
  `fileInitiateBy` int(11) DEFAULT NULL,
  `fileInitiatedOn` datetime DEFAULT NULL,
  `disposedBy` int(11) DEFAULT NULL,
  `disposedOn` datetime DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  
  
  INSERT INTO `tbldakmst` (`inwardId`, `dakType`, `dakMarkTo`, `letterNo`, `markDeptt`, `dakSenderName`, `subject`, `remarks`, `comments`, `handoverDate`, `handoverTo`, `forwardedTo`, `forwardedOn`, `forwardedFrom`, `status`, `createdBy`, `createdOn`, `modifiedBy`, `modifiedOn`, `fileInitiateBy`, `fileInitiatedOn`, `disposedBy`, `disposedOn`) VALUES
              (1, 0, 3, 'letter 5634', 'Office Dak', 'dheeraj kumar nayak', 'My subject data is here', 'Remrks Datammm VBCBCBBBBB', 'disposed again', '1970-01-26 00:00:00', 3, 3, '2017-05-02 15:14:50', 2, '5', 2, '2017-05-01 17:40:32', 3, '2017-05-07 15:42:40', 3, '2017-05-07 14:07:35', 3, '2017-05-07 14:39:51'),
              (2, 0, 4, 'letter no 787887', 'Office Dak', 'kAMLESH kUMAR', 'subj', 'Remarks  data', 'MY COMMENTS', '2017-05-02 15:14:50', 4, 2, '2017-05-02 15:14:50', 2, '2', 0, '2017-05-02 11:13:00', 2, '2017-05-05 13:00:44', NULL, NULL, 2, NULL),
              (3, 0, 2, 'letter 5634', 'Office Dak', 'dheeraj kumar nayak', 'Need operator credentiasl', 'file putted edited', 'gdfgfg', '1970-01-01 00:00:00', 2, 3, NULL, 2, '1', 2, '2017-05-03 11:03:49', 2, '2017-05-04 14:48:48', NULL, NULL, NULL, NULL),
              (4, 0, 2, 'letter 567', 'Office Dak', 'Dheraj Kumar', 'operator activation request from CSC-SPV', 'Personal Visit by EA', 'Disposed Now', '2017-05-07 00:00:00', 2, 2, NULL, 3, '5', 3, '2017-05-07 15:46:06', 2, '2017-05-07 16:10:14', 2, '2017-05-07 16:09:17', 2, '2017-05-07 16:10:14'),
              (5, 0, 3, 'letter no 5566rr', 'Office Dak', 'ranjeet vaghle', 'operator request', 'proceed', 'File issues resolved and file is diposed', '0000-00-00 00:00:00', 3, 2, NULL, 3, '4', 3, '2017-05-07 15:55:32', 2, '2017-05-07 16:06:58', 2, '2017-05-07 16:06:58', 2, '2017-05-07 16:06:58'),
              (6, 0, 2, 'letter 5634', 'Office Dak', 'dheeraj kumar nayak', 'hjjh', 'hjkhj', NULL, '0000-00-00 00:00:00', 2, NULL, NULL, NULL, '1', 2, '2017-05-07 17:27:36', NULL, NULL, NULL, NULL, NULL, NULL),
              (7, 0, 2, 'Letter Number -56788899', 'Office Dak', 'dheeraj kumar nayak', 'dsfs', 'fdsfdsf', 'initiated', '0000-00-00 00:00:00', 2, 2, NULL, 2, '3', 2, '2017-05-07 17:51:56', 2, '2017-05-07 17:52:54', 2, '2017-05-07 17:52:54', NULL, NULL);
  
  
  ALTER TABLE `tbldakmst`
                ADD PRIMARY KEY (`inwardId`);
  ALTER TABLE `tbldakmst` MODIFY `inwardId` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
  </code></pre>
  
  <p>I have tried:</p>
  
  <pre><code>        SELECT 
              sum(CASE
                  WHEN status = '1'   THEN 1
                  ELSE 0
              END) AS 'pending',
               sum(CASE
                  WHEN status = '3' THEN 1
                  ELSE 0
              END) AS 'initiated',
               sum(CASE
                  WHEN status = '4' OR status = '5' THEN 1
                  ELSE 0
              END) AS 'disposed' FROM  tbldakmst
  
          Above Query Output Was :
  
          pending     initiated   disposed        
          2              1           3         
  
  
   &lt;!-- Note:Status(0,1) ==&gt;Pending ,Status (3) ==&gt;Initiated,Status(4,5)==&gt;Disposed
   column FileInitiatedBy =&gt; User Value which one has initiated file.
   Column disposedBy=&gt; User Value which one has disposed file.
   Column dakmarkTo =&gt; User Value which one have pending file --&gt;
  </code></pre>
  
  <p>But I want Result  Like this</p>
  
  <pre><code>        User           |  Pending    | Initiated | Disposed  |
          ------------------------------------------------------
          User 2         |    1        |   1       |    2      |
          ------------------------------------------------------
          User 3         |     2       |   3       |    4      |
          ------------------------------------------------------
  </code></pre>