SQLTeam.com | Weblogs | Forums

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

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 when do stuff for feature name and feature value

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

Why?

distinct is very slow from group by

Distinct is not slower than group by - so there is probably something else going on and I would think using a while loop is most likely the issue with this code.

i accept any solution on sample above without using while loop
so are you have instead of while loop
you can give me
sample of data as above

I wouldn't even be able to start without sample data and expected results. No idea why you are looping over the results in the first place.

sample data as original post
expected result
will be

PartID Code CodeTypeID RevisionID ZPLID ConCount FeatureName FeatureValue
413989 32111502 1809942 329888149 4239 1 Type Zener
413989 8541100000 1019997 313023938 4239 1 Product Shape Type Discrete
413989 8541100050 849774 307683692 4239 3 Product Shape Type$Package Family$Type Discrete$SOT$Zener