I work on SQL server 2012 i need to use group by instead of distinct
so how to do that please
query working without any problem and give me result i need but I need to use group by instead
of distinct on last statement executed in exec @SQL
and if there are advice about indexes can help me to enhance performance is preferable
IF OBJECT_ID('[dbo].[Codes]') IS NOT NULL DROP TABLE [dbo].[Codes] IF OBJECT_ID('[dbo].[gen]') IS NOT NULL DROP TABLE [dbo].[gen] IF OBJECT_ID('[dbo].[PartAttributes]') IS NOT NULL DROP TABLE [dbo].[PartAttributes] IF OBJECT_ID('[dbo].[Allfeatures]') IS NOT NULL DROP TABLE [dbo].[Allfeatures] IF OBJECT_ID('dbo.AllData') IS NOT NULL DROP TABLE dbo.AllData IF OBJECT_ID('dbo.Condition') IS NOT NULL DROP TABLE [dbo].Condition IF OBJECT_ID('dbo.core_datadefinition_Detailes') IS NOT NULL DROP TABLE core_datadefinition_Detailes CREATE TABLE [dbo].[Codes]( [ZPLID] [int] NULL, [Code] [varchar](20) NULL, [Proceed] [int] NOT NULL ) ON [PRIMARY] GO INSERT [dbo].[Codes] ([ZPLID], [Code], [Proceed]) VALUES (4239, N'32111502', 1) INSERT [dbo].[Codes] ([ZPLID], [Code], [Proceed]) VALUES (4239, N'8541100000', 1) INSERT [dbo].[Codes] ([ZPLID], [Code], [Proceed]) VALUES (4239, N'8541100050', 1) CREATE TABLE core_datadefinition_Detailes( [ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [ColumnName] [nvarchar](500) NOT NULL, [ColumnNumber] [int] NOT NULL, CONSTRAINT [PK_Core_DataDefinition_Details] PRIMARY KEY CLUSTERED ( [ID] ASC ) ) insert into core_datadefinition_Detailes([ColumnNumber],[ColumnName]) values (202503,'Product Shape Type'), (1501170111,'Type'), (1501170046,'Maximum Peak Pulse Current'), (202504,'Package Family') CREATE TABLE [dbo].[gen]( [TradeCodeControlID] [int] IDENTITY(1,1) NOT NULL, [CodeTypeID] [int] NULL, [RevisionID] [bigint] NULL, [Code] [varchar](20) NULL, [ZPLID] [int] NULL, [ZfeatureKey] [bigint] NULL, [ZfeatureType] [nvarchar](200) NULL, [EStrat] [nvarchar](2500) NULL, [EEnd] [nvarchar](2500) NULL ) ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].[gen] ON INSERT [dbo].[gen] ([TradeCodeControlID], [CodeTypeID],[RevisionID], [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) VALUES (7565, 849774, 307683692, N'8541100050', 4239, 202503, N'Package', N'<>''IC''', N'') INSERT [dbo].[gen] ([TradeCodeControlID], [CodeTypeID],[RevisionID], [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) VALUES (7566, 849774, 307683692, N'8541100050', 4239, 202504, N'Package', N'Not In(''Die'',''Wafer'',''N/A'')', N'') INSERT [dbo].[gen] ([TradeCodeControlID], [CodeTypeID],[RevisionID], [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) VALUES (7567, 849774, 307683692, N'8541100050', 4239, 1501170111, NULL, N'=''Zener''', N'') INSERT [dbo].[gen] ([TradeCodeControlID], [CodeTypeID],[RevisionID], [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) VALUES (7577, 1019997, 313023938, N'8541100000', 4239, 202503, N'Package', N'<>''IC''', N'') INSERT [dbo].[gen] ([TradeCodeControlID], [CodeTypeID],[RevisionID], [Code], [ZPLID], [ZfeatureKey], [ZfeatureType], [EStrat], [EEnd]) VALUES (9497, 1809942, 329888149, N'32111502', 4239, 1501170111, NULL, N'=''Zener''', N'') SET IDENTITY_INSERT [dbo].[gen] OFF CREATE TABLE [dbo].[PartAttributes]( [PartID] [int] NOT NULL, [ZfeatureKey] [bigint] NULL, --[FeatureName] nvarchar NOT NULL, [AcceptedValuesOption_Value] [float] NULL, [FeatureValue] [nvarchar](500) NOT NULL ) ON [PRIMARY] GO --INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value]) VALUES (413989, 202503, NULL) --INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value]) VALUES (413989, 1501170111, NULL) --INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value]) VALUES (413989, 1501170046, 3) --INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value]) VALUES (413989, 202504, NULL) INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [FeatureValue]) VALUES (413989, 202503, NULL, N'Discrete') INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [FeatureValue]) VALUES (413989, 1501170111, NULL, N'Zener') INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [FeatureValue]) VALUES (413989, 1501170046, 3, N'3A') INSERT [dbo].[PartAttributes] ([PartID], [ZfeatureKey], [AcceptedValuesOption_Value], [FeatureValue]) VALUES (413989, 202504, NULL, N'SOT') CREATE TABLE [dbo].[Allfeatures]( [ZPLID] [int] NULL, [ZfeatureKey] [bigint] NULL, [FeatType] [int] NULL, [AcceptedValueID] [int] NULL, [IsNumericValues] [int] NULL ) ON [PRIMARY] GO INSERT [dbo].[Allfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (4239, NULL, 0, 0, 0) INSERT [dbo].[Allfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (4239, 202503, NULL, 33, 0) INSERT [dbo].[Allfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (4239, 202504, NULL, 34, 0) INSERT [dbo].[Allfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (4239, 1501170046, 2044, 814, 1) INSERT [dbo].[Allfeatures] ([ZPLID], [ZfeatureKey], [FeatType], [AcceptedValueID], [IsNumericValues]) VALUES (4239, 1501170111, 2043, 155, 0) CREATE TABLE dbo.AllData ( PartID INT, Code VARCHAR(20), CodeTypeID INT, RevisionID BIGINT, ZPLID INT, ConCount INT, FeatureName nvarchar(500), FeatureValue nvarchar(500) ) UPDATE Codes SET Proceed=0 DECLARE @Code VARCHAR(20) DECLARE @ZPID INT DECLARE @Sql nvarchar(max) DECLARE @Con nvarchar(max) DECLARE @ConStr nvarchar(max) WHILE (Select Count(*) From Codes with(nolock) where Proceed =0 ) > 0 BEGIN ---select * from gen SELECT Top 1 @ZPID=ZPLID, @Code=Code From Codes with(nolock) where Proceed=0 SELECT * INTO Condition FROM Gen G with(nolock) WHERE g.Code=@Code AND G.ZPLID=@ZPID AND G.ZfeatureKey IS NOT NULL SET @Con= STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ', CC.ZfeatureKey , ' And AcceptedValuesOption_Value ' , CAST(EStrat AS NVARCHAR(2500)) , IIF(EEnd='','',CONCAT(' And AcceptedValuesOption_Value ',EEnd)),')') FROM Condition CC INNER JOIN Allfeatures AL with(nolock) ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValues >0 FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'') SET @ConStr= STUFF((SELECT CONCAT(' Or (PM.ZfeatureKey= ', CC.ZfeatureKey , IIF(CC.ZfeatureType='Qualifications',' And AcceptedValuesOption_Value ' , ' And FeatureValue ' ) , CAST(EStrat AS NVARCHAR(2500)),')') --ValueName FROM Condition CC INNER JOIN Allfeatures AL with(nolock) ON AL.ZfeatureKey = CC.ZfeatureKey AND AL.IsNumericValues =0 FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'),1,3,'') SET @ConStr = IIF(LEN(@Con)>3 AND LEN(@ConStr)>3 , CONCAT('Or ',@ConStr),@ConStr ) DECLARE @separator CHAR(1) = '$'; SET @Sql= CONCAT('INSERT INTO dbo.AllData(PartID,Code,CodeTypeID,RevisionID,ZPLID ,ConCount,FeatureName,FeatureValue)',' SELECT PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID,Count(1) as ConCount, stuff(( SELECT ''$'' + CAST( CP.ColumnName AS VARCHAR(300)) AS [text()] FROM(SELECT distinct d.ColumnName,C.codeTypeId,C.Code,C.ZfeatureKey from Condition C inner join dbo.core_datadefinition_Detailes d with(nolock) on C.ZfeatureKey=d.columnnumber INNER JOIN PartAttributes P on P.partid=PM.partid)CP where CP.codeTypeId=Co.codeTypeId and CP.Code=Co.Code ORDER BY CP.ZfeatureKey FOR XML PATH(''''), TYPE ).value(''.'', ''NVARCHAR(MAX)'') , 1, 1, '''') as FeatureName, stuff(( SELECT ''$'' + CAST( CP2.FeatureValue AS VARCHAR(300)) AS [text()] FROM(SELECT distinct P.FeatureValue,C2.codeTypeId,C2.Code,C2.ZfeatureKey FROM Condition C2 INNER JOIN PartAttributes P on C2.ZfeatureKey=P.ZfeatureKey)CP2 where CP2.codeTypeId=Co.codeTypeId and CP2.Code=Co.Code ORDER BY CP2.ZfeatureKey FOR XML PATH(''''), TYPE ).value(''.'', ''NVARCHAR(MAX)'') , 1, 1, '''') as FeatureValue FROM PartAttributes PM INNER JOINCondition Co ON Co.ZfeatureKey = PM.ZfeatureKey ', 'Where (1=1 and ',@Con , @ConStr,' ) Group By PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID ' , ' Having Count(1)>= ',(SELECT COUNT(1) FROM Condition)) EXEC (@SQL) DROP TABLE Condition UPDATE Codes Set Proceed = 1 Where @ZPID=ZPLID AND Code=@Code END --select * from alldata