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