SQL Pivot with Datetime as a Column with quantity fields

user_16354134 0
Viewed 95

I have a SQL view on my database to print a report on C# and Itext7 using Lot and PartNumberId to find the results

DECLARE @LotId int = '1'
DECLARE @PartNumerId int = '100'

SELECT * FROM vw_report_diarydefects WHERE LotId = @LotId AND PartNumerId = @PartNumerId
Lot PartNumberId PartNumber DefectId Defect Qty SortingDate
1 100 WD40 1 Broken 9 2019-10-01 10:31:02.000
1 100 WD40 2 Paint Scratch 10 2019-10-02 10:31:02.000
1 100 WD40 3 Swollen 8 2019-10-02 10:31:02.000
1 100 WD40 2 Paint Scratch 10 2019-10-03 10:31:02.000
1 100 WD40 4 Bent 5 2019-10-04 10:31:02.000

What i want to do is put the SortingDate as columns and add the quantity on them like this:

Defect 2019-10-01 10:31:02.000 2019-10-02 10:31:02.000 2019-10-03 10:31:02.000 2019-10-04 10:31:02.000
Broken 9 0 0 0
Paint Scratch 0 10 10 0
Swollen 0 8 0 0
Bent 0 0 0 5

I used pivot (my 1st time using it) and i have use this:

DECLARE @StuffColumn varchar(max)
DECLARE @sql varchar(max)

DECLARE @LotId int = '1'
DECLARE @PartNumerId int = '100'

SELECT @StuffColumn = STUFF((SELECT distinct ','+QUOTENAME(SortingDate)
                        FROM vw_report_diarydefects
                                    WHERE LotId = @LotId AND PartNumerId = @PartNumerId
          FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

SET @SQL = ' select Defect, '+ @StuffColumn +'
         from
         (
            select   SortingDate,Defect,Qty
            from vw_report_diarydefects
            where LotId = ''' + CONVERT(NVARCHAR(50), @LotId, 121)+ ''' and PartNumerId = ''' + CONVERT(NVARCHAR(50), @PartNumerId, 121)+ '''
         )x
         pivot
         (
             Sum(Qty)
             for SortingDate in( '+@StuffColumn+' )
         )p'

EXEC(@SQL)

Getting just this:

Defect Oct 1 2019 10:31AM Oct 2 2019 10:31AM Oct 3 2019 10:31AM Oct 4 2019 10:31AM
Broken NULL NULL NULL NULL
Paint Scratch NULL NULL NULL NULL
Swollen NULL NULL NULL NULL
Bent NULL NULL NULL NULL

Is there a way to fill the Date Columns with the quantity data? Is my first time using Pivot, i am reading about that instruction but i still have some doubts.

pivot web-services sql-server sql c#
2 Answers

Try following :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
namespace ConsoleApplication193
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("Lot",typeof(int));
            dt.Columns.Add("PartNumberId",typeof(int));
            dt.Columns.Add("PartNumber",typeof(string));
            dt.Columns.Add("DefectId",typeof(int));
            dt.Columns.Add("Defect",typeof(string));
            dt.Columns.Add("Qty", typeof(int));
            dt.Columns.Add("SortingDate", typeof(DateTime));

            dt.Rows.Add(new object[] {1, 100, "WD40", 1, "Broken", 9, DateTime.Parse("2019-10-01 10:31:02.000")});
            dt.Rows.Add(new object[] {1, 100, "WD40", 2, "Paint Scratch", 10, DateTime.Parse("2019-10-02 10:31:02.000")});
            dt.Rows.Add(new object[] {1, 100, "WD40", 3, "Swollen", 8, DateTime.Parse("2019-10-02 10:31:02.000")});
            dt.Rows.Add(new object[] {1, 100, "WD40", 2, "Paint Scratch", 10, DateTime.Parse("2019-10-03 10:31:02.000")});
            dt.Rows.Add(new object[] {1, 100, "WD40", 4, "Bent", 5, DateTime.Parse("2019-10-04 10:31:02.000")});


            DateTime[] dates = dt.AsEnumerable().Select(x => x.Field<DateTime>("SortingDate")).OrderBy(x => x).Distinct().ToArray();

            DataTable pivot = new DataTable();
            pivot.Columns.Add("Defect");
            foreach (DateTime date in dates)
            {
                pivot.Columns.Add(date.ToString(), typeof(int));
            }

            foreach(var defect in dt.AsEnumerable().GroupBy(x => x.Field<string>("Defect")))
            {
                DataRow newRow = pivot.Rows.Add();
                newRow["Defect"] = defect.Key;
                foreach (DataRow row in defect)
                {
                    int qty = row.Field<int>("Qty");
                    DateTime date = row.Field<DateTime>("SortingDate");
                    newRow[date.ToString()] = qty;
                }
            }

        }
    }
 
}
user_50152380

Here's what I found. The way the Quotename is changing the date format is the issue. Change your @StuffColumn data to convert to varchar first and then Quote it.

SELECT @StuffColumn = STUFF((SELECT distinct ','+QUOTENAME(CONVERT(NVARCHAR(50),SortingDate, 121))
                        FROM vw_report_diarydefects
                                    WHERE Lot = @LotId AND PartNumberId = @PartNumerId
          FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

Here's the SQL Fiddle.

user_8817980
Related