Using Pivot in MySQL

Last Reply 11 months ago By kalpesh

Posted 11 months ago

Hi sir/mam, 

I'm pulling some data from the database and displaying in GridView But i need to display some data in common way like horizontally and some values as vertically. 

Example

Suppose i pulling the id, date, name from the users table.The id and date should come horizontally but the name should come vertically. 

How can show that please help me.

i'm using mysql database

Posted 11 months ago Modified on 11 months ago

REFER below test query for your reference and try to impliment it by using your code logic. you either need to use case stament ot dynamic columns for reference check the Link MySQL pivot table query with dynamic columns

MYSQL

CREATE TEMPORARY TABLE Orders(orderId INT ,CustomerName Varchar(20));
CREATE TEMPORARY TABLE OrderDetails(orderId INT,ProductName VARCHAR(20),qty INT);

INSERT INTO Orders
SELECT 1,'Customer 1'
UNION ALL
SELECT 2,'Customer 2'
UNION ALL
SELECT 3,'Customer 3'
;
INSERT INTO OrderDetails
SELECT 1,'Pen',2
UNION ALL
SELECT 1,'Pencil',3
UNION ALL
SELECT 1,'Note',1
UNION ALL
SELECT 2,'Pen',1
UNION ALL
SELECT 2,'Pencil',1
;
SELECT o.orderId 
	   ,o.CustomerName	
	   ,CASE WHEN od.ProductName = 'Pen' THEN od.qty ELSE 0 END Pen
	   ,CASE WHEN od.ProductName = 'Pencil' THEN od.qty ELSE 0 END Pencil
	   ,CASE WHEN od.ProductName = 'Note' THEN od.qty ELSE 0 END Note
	   FROM Orders o LEFT OUTER JOIN OrderDetails od
on o.orderId = od.orderId
GROUP BY o.orderId;

-- or by dynamic query

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'CASE WHEN od.ProductName = ''',
      ProductName,
      ''' THEN od.qty ELSE 0 END ',
      ProductName
    )
  ) INTO @sql
FROM OrderDetails;

SET @sql = CONCAT('SELECT o.orderId
                    ,o.CustomerName	', @sql, ' 
                   FROM product p
                   FROM Orders o LEFT OUTER JOIN OrderDetails od
                   on o.orderId = od.orderId
                   GROUP BY o.orderId');

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

DROP TABLE Orders; 
DROP TABLE OrderDetails;

Output

orderId CustomerName Pen Pencil Note
1 Customer 1 2 3 1
2 Customer 2 1 1 0
3 Customer 3 0 0 0

Posted 11 months ago Modified on 11 months ago

First try this

SELECT od.OId
      ,od.SalesCode
	  ,od.InvoiceNo
	  ,CASE WHEN UPPER(ob.ProductName) = 'BOOKS' THEN ob.qty ELSE 0 END Books
from orderdata od INNER JOIN orderbooking ob
on  od.OId = ob.BId
where  od.BillDate >=fdate
AND od.BillDate <=tdate
AND ob.BillDate = od.BillDate
AND ob.Status =1
group by (ob.InvoiceNo)
;

Then try to impliment for dynamic query for MYSQL as explained in previous reply.