hi

i have following sql query but i am wonder to convert into PIVOT query how it is possilbe

SELECT a.AreaID, a.AreaName, c.CustomerID, c.CustomerName, c.ProductID, c.ProductName, Sum(c.Qty) AS Qty, Sum(c.NetTotal) AS SalesValue FROM Area AS a, Parties AS b, SalesDayBook AS c WHERE a.AreaID=[b].[AreaID] AND c.CustomerID=[b].[PartyID] AND c.TransDate>='" & DateEdit1.EditValue & "' And c.TransDate<='" & DateEdit2.EditValue & "' GROUP BY a.AreaID, a.AreaName, c.CustomerID, c.CustomerName, c.ProductID, c.ProductName;

i want to show productname as a rows and areaname as a columns and in this areaname columns i want to show the qty of each product for each area

expected result is like below

ProductName      area a         area b       area c

abc                          10                0                20

def                           1                  4               2

ghi                           5                  3               10

jkl                             7                 15              3

please rectify my query

• Edited by haqayyum 21 hours 29 minutes ago

CREATE TABLE Foo (
  foo_type CHAR(1) PRIMARY KEY,
  foo_value INT);

INSERT INTO Foo VALUES('A', 1);
INSERT INTO Foo VALUES('B', 2);
INSERT INTO Foo VALUES('C', 3);
INSERT INTO Foo VALUES('D', 4);

-- pivot using CASE
SELECT MAX(CASE WHEN foo_type = 'A' THEN foo_value END) AS A,
        MAX(CASE WHEN foo_type = 'B' THEN foo_value END) AS B,
        MAX(CASE WHEN foo_type = 'C' THEN foo_value END) AS C,
        MAX(CASE WHEN foo_type = 'D' THEN foo_value END) AS D
FROM Foo;

-- pivot using PIVOT operator (SQL Server 2005/2008)
SELECT A, B, C, D
FROM Foo
PIVOT
(MAX(foo_value) FOR foo_type IN (A, B, C, D)) AS P;

-- dynamic pivot (SQL Server 2005/2008)
DECLARE @pivot_cols NVARCHAR(1000);
SELECT @pivot_cols =
        STUFF((SELECT DISTINCT '],[' + foo_type
               FROM Foo
               ORDER BY '],[' + foo_type
               FOR XML PATH('')
               ), 1, 2, '') + ']';

DECLARE @pivot_query NVARCHAR(2000);
SET @pivot_query =
N'SELECT ' + @pivot_cols +
  'FROM Foo ' +
  'PIVOT ' +
  '(MAX(foo_value) FOR foo_type IN (' + @pivot_cols + ')) AS P;';

EXEC(@pivot_query);

DROP TABLE Foo;

i have following sql query but i am wonder to convert into PIVOT query how it is possilbe
i want to show productname as a rows and areaname as a columns

 
• Dynamic PIVOT in Sql Server
• Script to create dynamic PIVOT queries in SQL Server
• Pivoting the Dynamic Way

To make a pivot query, you can follow Uri's and Jos's posts. If you could post your table structure, we can help to compose the completed query for you.