SQL Server Pivot tabel aggregation - Convert rows to column by date

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.

There's multiple problems here:

You don't escape your single quotes that need to appear inside your dynamic statement
You don't quote your column names (which require delimited identifying)
You use an expression not a literal for your FOR clause
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;

SQL Server Pivot tabel aggregation - Convert rows to column by date

1条答案

相关问题

热门标签

最新问答