hi

I have a query which I want to convert It PIVOT query

SELECT     Parties.AreaID, Parties.Area, CashSalesDetail.ProductID, CashSalesDetail.ProductName, SUM(CashSalesDetail.Qty) AS Qty
FROM         CashSalesDetail INNER JOIN
                      CashSales ON CashSalesDetail.CSNo = CashSales.CSNo INNER JOIN
                      Parties ON CashSales.PartyID = Parties.PartyID
WHERE     (CashSales.TransDate >= CONVERT(DATETIME, '2014-07-01 00:00:00', 102)) AND (CashSales.TransDate <= CONVERT(DATETIME, '2015-06-30 00:00:00', 102))
GROUP BY Parties.AreaID, Parties.Area, CashSalesDetail.ProductID, CashSalesDetail.ProductName

following is my requirement after summing up qty of each area

ProductName      area a         area b       area c

abc                          10                0                20

def                           1                  4               2

ghi                           5                  3               10

jkl                             7                 15              3

Note: numeric values are Quantity of each product in each area

Hi Haqayyum,

Please post  sample data in form of table variable and insert statements. Which will  help the forum members to solve your problem faster.

Hi ,

you can take help from below link;

https://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic-columns-in-sql-server-2005/

https://www.mssqltips.com/sqlservertip/1019/crosstab-queries-using-pivot-in-sql-server/

Thanks

following is my table structure

--Main Customer Table

CREATE TABLE [dbo].[Parties](
	[PartyID] [int] NOT NULL,
	[AccountCode] [nvarchar](255) NULL,
	[PartyName] [nvarchar](255) NULL,
	[ContactNo] [nvarchar](255) NULL,
	[MobileNo] [nvarchar](50) NULL,
	[AreaID] [nvarchar](255) NULL,
	[Area] [nvarchar](255) NULL,
	[Address] [nvarchar](255) NULL,
	[OpeningDate] [datetime] NULL,
	[OpeningDebit] [float] NULL,
	[OpeningCredit] [float] NULL,
	[Type] [nvarchar](255) NULL,
	[HeadName] [nvarchar](255) NULL,
	[LicenseNo] [nvarchar](255) NULL,
	[ExpDate] [datetime] NULL,
	[GroupID] [nvarchar](50) NULL,
	[DriverName] [nvarchar](10) NULL,
	[OwnerName] [nvarchar](10) NULL,
	[CreditLimit] [real] NULL,
	[Remarks] [nvarchar](10) NULL,
	[CurrentBalance] [decimal](18, 0) NULL,
	[ProgramID] [int] NULL,
	[ID] [int] NULL,
 CONSTRAINT [PK_Parties] PRIMARY KEY CLUSTERED 
(
	[PartyID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

--Main Sales Master Table

CREATE TABLE [dbo].[CashSales](
	[CSNo] [nvarchar](255) NOT NULL,
	[RefNo] [nvarchar](255) NULL,
	[AccountCode] [nchar](10) NULL,
	[PartyID] [int] NULL,
	[TransDate] [datetime] NULL,
	[SubTotal] [decimal](18, 2) NULL,
	[Total] [decimal](18, 2) NULL,
	[User] [nvarchar](255) NULL,
	[Type] [nvarchar](255) NULL,
	[GroupType1] [nvarchar](255) NULL,
	[DiscountPcnt] [decimal](18, 2) NULL,
	[DiscountRs] [decimal](18, 2) NULL,
	[Received] [decimal](18, 2) NULL,
	[Time] [datetime] NULL,
	[BNo] [float] NULL,
	[Changed] [decimal](18, 2) NULL,
	[DetailDiscRs] [decimal](18, 2) NULL,
	[SalesmanID] [int] NULL,
	[SalesmanName] [nvarchar](50) NULL,
	[Margine] [decimal](18, 2) NULL,
	[StoreID] [nvarchar](100) NULL,
	[Address] [nvarchar](100) NULL,
	[ProgramID] [int] NULL,
	[TotalCost] [decimal](18, 2) NULL,
	[InvTotal] [decimal](18, 2) NULL,
 CONSTRAINT [PK_CashSales] PRIMARY KEY CLUSTERED 
(
	[CSNo] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

--Main Sales Detail Table

CREATE TABLE [dbo].[CashSalesDetail](
	[CSNo] [nvarchar](255) NOT NULL,
	[ProductID] [int] NULL,
	[ProductName] [nvarchar](255) NULL,
	[UOM] [nvarchar](255) NULL,
	[Packing] [nchar](10) NULL,
	[Qty] [float] NULL,
	[Bonus] [float] NULL,
	[TPrice] [float] NULL,
	[Price] [float] NULL,
	[Discount] [real] NULL,
	[DiscountRs] [real] NULL,
	[Total] [float] NULL,
	[BatchNo] [nvarchar](50) NULL,
	[Barcode] [nvarchar](255) NULL,
	[BNo] [int] NULL,
	[Margine] [float] NULL,
	[QtyRet] [float] NULL,
	[CostPricePU] [float] NULL
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[CashSalesDetail]  WITH CHECK ADD  CONSTRAINT [FK_CashSalesDetail_CashSales] FOREIGN KEY([CSNo])
REFERENCES [dbo].[CashSales] ([CSNo])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[CashSalesDetail] CHECK CONSTRAINT [FK_CashSalesDetail_CashSales]

Try:

;with cte as (SELECT  Parties.Area, CashSalesDetail.ProductName, SUM(CashSalesDetail.Qty) AS Qty
FROM         CashSalesDetail INNER JOIN
                      CashSales ON CashSalesDetail.CSNo = CashSales.CSNo INNER JOIN
                      Parties ON CashSales.PartyID = Parties.PartyID
WHERE     CashSales.TransDate >= CONVERT(DATETIME, '2014-07-01 00:00:00', 102) AND CashSales.TransDate <= CONVERT(DATETIME, '2015-06-30 00:00:00', 102)
GROUP BY Parties.AreaID, Parties.Area, CashSalesDetail.ProductID, CashSalesDetail.ProductName)

select * from cte PIVOT (sum(Qty) for [Area] IN ([Area a], [Area b], [Area c])) pvt

This assumes you know the areas you want in your final result. If you don't know names of the areas, then you would need Dynamic Pivot. Search this forum for examples.

SELECT *
FROM (
    SELECT 
        year(invoiceDate) as [year],left(datename(month,invoicedate),3)as [month], 
        InvoiceAmount as Amount 
    FROM Invoice
) as s
PIVOT
(
    SUM(Amount)
    FOR [month] IN (jan, feb, mar, apr, 
    may, jun, jul, aug, sep, oct, nov, dec)
)AS pivot

hi Naomi N

thanks for your reply

but as you mentioned above post that if I know Area Name then your above query will be useful for me but It it inform you that area is not fixed it depends on the user whatever he want to select desired area's to show report of that area's. So please suggest me how to create dynamic PIVOT query according to your above illustration please modify it according to my requirement

Good day,

You can use Dynamic Pivot in this case. you can see more information in this blog (It is just the first result I got in Google :-) but I know that Naomi wrote a blog about this issue as well): http://sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server/

Basically you can use Dynamic Query in order to execute any dynamic query that you create on-the-fly. In this case you use Dynamic Query (the built in SP sp_executesql) in order to execute a pivot query that you create on-the-fly :-)

Update: clarification
First step you found all the columns and second step you create the dynamic query using the columns that you found in

Did you make an effort to research Dynamic Pivot and what problems do you have in attempting to write one? Please post what you have tried so far.

thanks for your reply Naomi I have given idea from your blog http://sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server/

and i have successfully created dynamic query but I have a trouble that when I execute my query it will provide the large number of columns which make me no sense how to fixed only 8 columns on each page of .net report and remaining columns will be automatically shifted to a new page of my report or can I view the entire columns on a single report when there's have large number of columns

also how can I set Null values to 0

please take a look my sample image and suggest me best solution for this problem.

• Edited by haqayyum a few seconds ago