Convert Rows To Columns and Calculate Sum using dynamic Pivot query in SQL Server

Last Reply 8 months ago By pandeyism

Posted 8 months ago

i want to convert row into column and calculate sum, want result like this.

item Name 01-05-2019 01-05-2019 01-05-2019 01-05-2019
A 4 4 3 1
B 1 2 4 1
c 4 3 2 2

i have two table one table is Product(col:ItemCode,Name) and second table is Produce (ID,ItemCode,Prdqty).

please guide me ,i want above result date wise item produce quantity ..thanks

i tried this but not getting result giving error,here store procedure

USE [SilverProduction]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[Pivot_Item1]
@StartDate date,
@Enddate   date
AS
BEGIN
    SET NOCOUNT ON;
SELECT DISTINCT EntryDate INTO #Date
FROM Probale
WHERE     (EntryDate BETWEEN @StartDate AND @Enddate)
ORDER BY EntryDate

DECLARE @cols NVARCHAR(4000)
SELECT  @cols = COALESCE(@cols + ',[' + CONVERT(varchar, DATEPART(DAY, EntryDate), 112)
                + ']','[' + CONVERT(varchar,DATEPART(DAY, EntryDate), 112) + ']')
FROM    #Dates222
ORDER BY EntryDate
DECLARE  @qry NVARCHAR(4000) =
    N'SELECT * 
    FROM (SELECT ItemMasterFile.Codeitem,ItemMasterFile.Descriptionitem,Probale.prdqty, 
    DATEPART(DAY, Probale.EntryDate)as DDate
    FROM Probale  Inner Join ItemMasterFile on Probale.Codeitem=ItemMasterFile.Codeitem )prd
    PIVOT (Sum(prdqty) FOR DDate IN (' + @cols + ')) AS stat 
    '
EXEC(@qry) 
END

PLease guide thanks

This question does not have replies that have been liked.