How to execute this statement dynamically without using static group id ?

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

I work on sql server 2014 I need to run this statement below dynamically without

using group id static

because I don't know make group id using may be 10,15,20

so How to run query below dynamically without write static group id on join?

sql fiddle i will work on it
SQL Server 2014 | db<>fiddle[^]

What I have tried:

SQL

select r1.familyid, r1.companyid, r1.GlobalPnId,
     concat(
         r1.PortionKey,
         r2.PortionKey,
         r3.PortionKey,
         r4.PortionKey,
         r5.PortionKey,
         r6.PortionKey,
         r7.PortionKey,
         r8.PortionKey,
         r9.PortionKey
         ) as PartNumber
 from GetFinalResult r1
 left join GetFinalResult r2 on r2.familyid = r1.familyid and r2.GroupID = 2
 left join GetFinalResult r3 on r3.familyid = r1.familyid and r3.GroupID = 3
 left join GetFinalResult r4 on r4.familyid = r1.familyid and r4.GroupID = 4
 left join GetFinalResult r5 on r5.familyid = r1.familyid and r5.GroupID = 5
 left join GetFinalResult r6 on r6.familyid = r1.familyid and r6.GroupID = 6
 left join GetFinalResult r7 on r7.familyid = r1.familyid and r7.GroupID = 7
 left join GetFinalResult r8 on r8.familyid = r1.familyid and r8.GroupID = 8
 left join GetFinalResult r9 on r9.familyid = r1.familyid and r9.GroupID = 9
 where r1.GroupID = 1

Posted 1-Jun-21 2:48am

ahmed_sa

Updated 1-Jun-21 3:03am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2021-06-01T03:03:00

You can use a variable in the query. The value for the variable can for example come from a parameter if this is a stored procedure. See DECLARE @local_variable (Transact-SQL) - SQL Server | Microsoft Docs[^]

As an example, consider the following excerpt

SQL

declare @groupid int;
set @groupid = 1;

select r1.familyid, r1.companyid, 
...
 where r1.GroupID = @groupid