Help in creating a dynamic query

• Puiu

  SSC Enthusiast

  Points: 184

  More actions

  May 5, 2021 at 11:36 am

  Go to Answer

  #3879582

  I want to do a dynamic query whose result is the intersection of several results. I have this table:

  CREATE TABLE [dbo].[Seturi](
  [id] [int] IDENTITY(1,1) NOT NULL,
  [partId] [nvarchar](50) NOT NULL,
  [codeId] [nvarchar](50) NOT NULL,
  [denumire] [nvarchar](250) NOT NULL,
   CONSTRAINT [PK_Seturi] PRIMARY KEY CLUSTERED 
  (
  [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]

  where codeId represents the code of the finished product, and partId is the code of the subassemblies that compose the finished product. For example, for codeId = 10001 I have several records with different partId. I want to get a dynamic query in which to enter a list of partId and results are all codeId in which all these partId exist. A functional example of what I want is (but I want a dynamic query):

  select codeId, denumire from Seturi where partId = 300421
  intersect
  select codeId, denumire from Seturi where partId = 301002
  intersect
  select codeId, denumire from Seturi where partId = 300102
  intersect
  select codeId, denumire from Seturi where partId = 300324
  group by codeId, denumire
  order by denumire

  Thank you for any advice! Pls excuse my englis, is not my native language...

• Jez-448386

  SSCarpal Tunnel

  Points: 4594

  More actions

  May 5, 2021 at 1:43 pm

  Answer

  #3879620

  If I understand you correctly, if you enter a list of 4 parts you want to find all codeId which have those 4 parts.

  One way of  doing this is to split the input string into rows into a table and then count the number of rows for codeId for those parts:

  
  USE TEMPDB;
  DROP TABLE IF EXISTS Seturi;
  CREATE TABLE [dbo].[Seturi](
  [id] [int] IDENTITY(1,1) NOT NULL,
  [partId] [nvarchar](50) NOT NULL,
  [codeId] [nvarchar](50) NOT NULL,
  [denumire] [nvarchar](250) NOT NULL,
   CONSTRAINT [PK_Seturi] PRIMARY KEY CLUSTERED 
  (
  [id] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  
  
  
  INSERT Seturi
  VALUES 
   ( '300421','Code1','Denumire1')
  ,( '301002','Code1','Denumire1')
  ,( '300102','Code1','Denumire1')
  ,( '300324','Code1','Denumire1')
  ,( '300421','Code2','Denumire2')
  ,( '300102','Code2','Denumire2')
  ,( '300324','Code2','Denumire2')
  
  
  DECLARE @PartsCounts INT;
  
  DROP TABLE IF EXISTS #Parts;
  
  select Value as PartId
  into #Parts
  FROM string_split('300421,301002,300102,300324',',')
  
  SET @PartsCounts = @@ROWCOUNT;
  
  WITH CTE AS(
  SELECT DISTINCT CodeId
  FROM Seturi a
  JOIN #Parts b
    ON b.PartId = a.partId
  GROUP BY CodeId, denumire
  HAVING COUNT(*) = @PartsCounts
  )
  SELECT DISTINCT a.codeID, a.denumire
  FROM Seturi a
  JOIN CTE b
    on b.codeId = a.codeId
  ;