Pivot tabel aggregation - Convert rows to column by date

Question

I'm having difficulty building the syntax. I tried to act as in the examples in a similar thread but I get aggregation errors that I can't deal with.

I will be grateful for any hint in solving my problem.

https://stackoverflow.com/questions/58213369/convert-rows-to-column-by-date-with-pivot

I would like have as result of query a table with items in every warehouse and a range of data assigned to the days of the current month.

• ( nice to have dynamic column descriptions for the current month range)

Below example of my issue. enter image description here

Code to create my example table:

I. INPUT TABLE:

CREATE TABLE [dbo].[Warehouse_test](
    [Date_gen] [date] NOT NULL,
    [Warehouse] [char](10) NOT NULL,
    [Prod_Id] [int] NOT NULL,
    [quantity] [int] NULL,
 CONSTRAINT [PK_Warehouse_test] PRIMARY KEY CLUSTERED 
(
    [Date_gen] ASC,
    [Warehouse] ASC,
    [Prod_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

II. INSERT DATA

INSERT INTO [dbo].[Warehouse_test]
           ([Date_gen]
           ,[Warehouse]
           ,[Prod_Id]
           ,[quantity])
     VALUES
        ('2023-07-01','MAG01',2145,586),
    ('2023-07-02','MAG01',2145,581),
    ('2023-07-03','MAG01',2145,344),
    ('2023-07-02','MAG01',2186,9),
    ('2023-07-01','MAG02',753,295),
    ('2023-07-02','MAG02',753,31),
    ('2023-07-03','MAG02',753,295),
    ('2023-07-02','MAG04',2186,2),
    ('2023-07-01','MAG14',2145,7),
    ('2023-07-02','MAG14',2145,111),
    ('2023-07-03','MAG14',2145,11)
GO

I am pasting my problematic queries below

III. Query

-- 1 Option 
 select   [Warehouse], [Prod_Id],  
      (case when [Date_gen] = '2023-07-01' then [quantity] end) '1',
      (case when [Date_gen] = '2023-07-02' then [quantity] end) '2',
      (case when [Date_gen] = '2023-07-03' then [quantity] end) '3'
   from
(
  select [Warehouse], [Prod_Id], [quantity], [Date_gen]
   -- , row_number() over(partition by  [Warehouse], [Prod_Id] order by [Warehouse], [Prod_Id]) rn
  from [dbo].[Warehouse_test]
 
  group by  [Warehouse], [Prod_Id], [quantity] , [Date_gen]
 )
src



 --max(case when [Date_gen] = '2023-07-01' then [quantity] end) '1',
  --max(case when [Date_gen] = '2023-07-02' then [quantity] end) '2',
  --max(case when [Date_gen] = '2023-07-03' then [quantity] end) '3'
--- Column 'src.Warehouse' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


-- 2 option pivot table
--Msg 402, Level 16, State 1, Line 147
--The data types nvarchar(max) and varchar are incompatible in the subtract operator.

DECLARE
@cols   nvarchar(max)='' ,
@query  nvarchar(max)=''

SET @cols = STUFF((SELECT
 ', ' + replace([Date_gen], '-', '')  as [Date_gen] 

   from [dbo].[Warehouse_test]
   GROUP BY  replace([Date_gen], '-', '')
   FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'');
        --            FOR XML PATH('')), 1, 1, '');


  set @query = 'SELECT [Warehouse], [Prod_Id],   ' + @cols + ' 
            from 
            (
              select [Warehouse], [Prod_Id],  
        
                 replace([Date_gen], '-', '')  as [Date_gen]   , [quantity]
              from [dbo].[Warehouse_test]            
              ORDER BY [Warehouse] DESC , [Prod_Id] DESC

             ) x
            pivot 
            (
                sum([quantity]) 
                for   replace([Date_gen], '-', '') in (' + @cols + ') ) p '

execute(@query);

Tables with aggregation to the date in the columns with the quantity of goods.

Answer 1

There's multiple problems here:

1. You don't escape your single quotes that need to appear inside your dynamic statement
2. You don't quote your column names (which require delimited identifying)
3. You use an expression not a literal for your FOR clause
4. The bad habit of using EXEC(@SQL) syntax, rather than using sp_executesql.

Address those, and your query works:

DECLARE @cols nvarchar(MAX),
        @query nvarchar(MAX);

SET @cols = STUFF((SELECT DISTINCT N', ' + QUOTENAME(replace([Date_gen], N'-', N'')) --Might as well use a DISTINCT
                  FROM [dbo].[Warehouse_test]
                  FOR XML PATH(''), TYPE).value('(./text())[1]', 'NVARCHAR(MAX)'),1,1,'');

SET @query = N'SELECT [Warehouse], [Prod_Id],   ' + @cols + N' 
            from 
            (
              select [Warehouse], [Prod_Id],  
        
                 replace([Date_gen], ''-'', '''')  as [Date_gen]   , [quantity]
              from [dbo].[Warehouse_test]            
              --ORDER BY [Warehouse] DESC , [Prod_Id] DESC

             ) x
            pivot 
            (
                sum([quantity]) 
                for date_gen in (' + @cols + N') ) p ;';
PRINT @query;
EXECUTE sys.sp_executesql @query;