How to use group by instead of distinct when execute dynamic SQL query @SQL ?

ahmed salah 3,216 Reputation points
2021-11-19T12:04:28.917+00:00

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 @alenzi

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](200) 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
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,654 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
0 comments
Accepted answer
  1. Viorel 111.8K Reputation points
    2021-11-19T17:02:08.653+00:00

    Try replacing 'SELECT distinct P.FeatureValue,C2.codeTypeId,C2.Code,C2.ZfeatureKey FROM Condition C2 INNER JOIN PartAttributes P on C2.ZfeatureKey=P.ZfeatureKey' with 'SELECT P.FeatureValue,C2.codeTypeId,C2.Code,C2.ZfeatureKey FROM Condition C2 INNER JOIN PartAttributes P on C2.ZfeatureKey=P.ZfeatureKey GROUP BY P.FeatureValue,C2.codeTypeId,C2.Code,C2.ZfeatureKey'.

    0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest