question

vishalchitrala-8227 avatar image
0 Votes"
vishalchitrala-8227 asked EchoLiu-msft edited

Dynamic SQL query to loop through records and find correct category

I'm trying to write a 2 step process to determine material types for different components.

Step 1: this includes results from a different query containing RecordID and Component Name :

Sample records :

RecordID Component Material


BR39590 00000000000000564792 000000000002073757
BR39590 00000000000000567649 000000000002073757
BR39591 00000000000000567650 000000000002073758

Above RecordId's will contain several component numbers and corresponding Materials

2) Loop through the Material derived from above query result and join to a different table called 'Material' to determine Material type, if Material type belongs to 'A' or 'B' the process should exit out and insert the records into a new table. If Material type does not belong to 'A' or B then Query should go back to step 1 and fetch the next component to look for 'A' or 'B' or blank resultset.

Material Category
000000000002073757 A
000000000002073758 B

Above 2 steps are repeated for all RecordID's

Final Result:

RecordId Material Component Category


BR39590 000000000002073757 00000000000000564792 A
BR39590 000000000002073757 00000000000000567649 A
BR39591 000000000002073758 00000000000000567650 B






sql-server-generalsql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

In addition, I want to give you a small suggestion. When you post a question, it is best to provide CREATE and INSERT statements so that we can copy them directly for testing. Thank you. good luck!

0 Votes 0 ·

Could you please validate all the answers so far and provide any update?
If all of them are not working or helpful, please provide more sample data or details about your issue.
Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered vishalchitrala-8227 commented

Check a non-dynamic query:

 select r.RecordID, r.Material, r.Component, m.Category
 from Records r
 inner join Material m on m.Material = r.Material
 where m.Category in ( 'A', 'B' )
 order by RecordID

If it works, use INSERT...SELECT instruction to insert the results to target table.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you, will check this out.

0 Votes 0 ·

Thank you,
II've described the issue more clearly here:

https://docs.microsoft.com/en-us/answers/questions/479189/dynamic-sql-to-loop-through-subid39s-until-valid-c.html

Could you please have a look.

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @vishalchitrala-8227

Please check:

 DROP TABLE IF exists #target
    
 CREATE TABLE #test(RecordID varchar(15),Component int,Material int)
 INSERT INTO #test VALUES('BR39590',00000000000000564792,000000000002073757),
                         ('BR39590',00000000000000567649,000000000002073757),
                         ('BR39591',00000000000000567650,000000000002073758)
    
 CREATE TABLE #Material(Material int,Category varchar(15))
 INSERT INTO #Material VALUES(000000000002073757,'A'),
                             (000000000002073758,'B')
    
 CREATE TABLE #target(RecordID varchar(15),Component int,Material int,Category varchar(15)) 
    
 DECLARE @num int
 SET @num=1
    
 WHILE @num<=(SELECT COUNT(*) FROM #test)
  BEGIN 
    WITH cte
    as(SELECT *,ROW_NUMBER() OVER(ORDER BY RecordID) rr
       FROM #test)
    INSERT INTO #target
    SELECT t.RecordID,t.Component,t.Material,m.Category
    FROM cte t
    JOIN #Material m ON t.Material=m.Material
    WHERE t.rr=@num
       
    SET @num=@num+1
  END
    
 SELECT * FROM #target

Output:
114480-image.png

If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.



image.png (5.3 KiB)
image.png (5.5 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for your answer.. but I've further broken down the steps and created a new thread. Could you please take a look :

https://docs.microsoft.com/en-us/answers/questions/479189/dynamic-sql-to-loop-through-subid39s-until-valid-c.html

0 Votes 0 ·

Okay, I have reviewed your new post and provided suggestions. We can continue the discussion in the new post. Please select a helpful answer and accept it as the answer to close the current post. Thank you!

Regards
Echo

0 Votes 0 ·