drop table AUTOREP_SIZE_RATIO
CREATE TABLE [dbo].AUTOREP_SIZE_RATIO(
[RATIO_ID] [bigint] NOT NULL,
[RATIO_NO] [int] NOT NULL,
[CATID] [int] NULL,
[SUB_ID] [int] NULL,
[SIZE_ID] [int] NULL,
[QTY] [int] NULL
) ON [PRIMARY]
GO
insert into AUTOREP_SIZE_RATIO(
RATIO_ID, RATIO_NO, CATID, SUB_ID, SIZE_ID, QTY)values
(1000,1,100,101,1043,1),
(1000,1,100,101,1044,1),
(1001,2,100,101,1042,2),
(1001,2,100,101,1043,2),
(1001,2,100,101,1044,2),
(1002,3,100,101,1042,3),
(1002,3,100,101,1043,3),
(1002,3,100,101,1044,3),
(1002,3,100,101,1045,3)
DECLARE @CATID nvarchar(max)=100,
DECLARE @SUB_ID nvarchar(max)=101
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX),
@PivotColumnNames AS NVARCHAR(MAX),
@PivotSelectColumnNames AS NVARCHAR(MAX)
--Get distinct values of the PIVOT Column
SELECT @PivotColumnNames= ISNULL(@PivotColumnNames + ',','')
+ QUOTENAME(size)
FROM (SELECT DISTINCT AM.[DESCRIPTION] size
FROM [dbo].[AUTOREP_SIZE_RATIO] SR LEFT JOIN ARTICLEMATRIX AM ON AM.ARTMID=SR.SIZE_ID
where SR. CATID= @CATID and SR. SUB_ID= @SUB_ID) AS cat
--select @PivotColumnNames
SELECT @PivotSelectColumnNames
= ISNULL(@PivotSelectColumnNames + ',','')
+ 'ISNULL(' + QUOTENAME(size) + ', 0) AS '
+ QUOTENAME(size)
FROM (SELECT DISTINCT AM.[DESCRIPTION] size
FROM [dbo].[AUTOREP_SIZE_RATIO] SR LEFT JOIN ARTICLEMATRIX AM ON AM.ARTMID=SR.SIZE_ID
where SR. CATID= @CATID and SR. SUB_ID= @SUB_ID) AS cat order by size
--select @PivotSelectColumnNames
SET @DynamicPivotQuery =
N'SELECT RATIO_ID,RATIO_NO,
' + @PivotSelectColumnNames +' from
(SELECT RATIO_ID,RATIO_NO,qty,DESCRIPTION size
FROM [dbo].[AUTOREP_SIZE_RATIO] SR LEFT JOIN ARTICLEMATRIX AM ON AM.ARTMID=SR.SIZE_ID
where SR. CATID= '+@CATID+' and SR. SUB_ID= '+@SUB_ID+')D
pivot(sum(QTY) for size in (' + @PivotColumnNames + ')) as pvt' ;
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery
