TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
ahmed elbarbary
NA
1.6k
278.2k
How to make join to teams display without join all teams ?
Mar 28 2020 3:37 PM
How to make left join to only teams displayed dynamically depend on fields exist on @selectedcolumncomma ?
i work on SQL server 2012 my problem how to generate join dynamically based on teams displayed without writing all
relations to all teams and my be result two teams only
on my situation below i make left join to 7 teams because may be one team from 7 come
so actually i need left join dynamically based on column generated dynamically
so if i one team displayed to lifecycle then one left join to lifecycle
so if i two team displayed to lifecycle and crosspart as my sample then two left join to lifecycle and crosspart
create
table
#tempVariables
(
DocumentId
int
)
insert
into
#tempVariables(DocumentId)
values
(22490)
create
table
#Teams
(
TeamId
int
,
TeamName nvarchar(50),
ColumnName nvarchar(100)
)
insert
into
#teams
values
(1,
'Package'
,
'Package'
),
(2,
'Parametric'
,
'Parametric'
),
(3,
'Scribing'
,
'Scribing'
),
(4,
'Lifecycle'
,
'Lifecycle'
),
(5,
'OBS'
,
'OBS'
),
(6,
'Cross'
,
'CrossPart'
),
(7,
'Rohs'
,
'Rohs'
)
create
table
#DocumentTeams
(
DocumentTeamId
int
identity(1,1),
DocumentId
int
,
TeamId nvarchar(50)
)
insert
into
#DocumentTeams(DocumentId,TeamId)
values
(22490,4),
(22490,6),
(22491,1),
(22491,5),
(22491,7)
Create
table
#FlowStatus
(
FlowStatusID
int
,
FlowStatus nvarchar(100)
)
insert
into
#FlowStatus
values
(1,
'Pending'
),
(2,
'InProgress'
),
(3,
'Done'
)
create
table
#DocumentPartTeams
(
DocumentPartTeamsId
int
,
PartId
int
,
DocumentId
int
,
Package
int
,
Parametric
int
,
Scribing
int
,
Lifecycle
int
,
OBS
int
,
CrossPart
int
,
Rohs
int
)
insert
into
#DocumentPartTeams
(PartId,DocumentId,Package,Parametric,Scribing,Lifecycle,OBS,CrossPart,Rohs)
values
(1000,22490,
null
,
null
,
null
,1,
null
,1,
null
),
(1002,22490,
null
,
null
,
null
,1,
null
,1,
null
),
(1005,22491,2,
null
,
null
,
null
,2,
null
,2),
(1008,22491,2,
null
,
null
,
null
,2,
null
,1)
select
dt.DocumentID,dt.TeamID, t.TeamName,t.ColumnName
into
#GetDocumentTeams
from
#DocumentTeams dt
inner
join
#Teams t
on
t.TeamID=dt.TeamID
inner
join
#tempVariables tv
on
tv.DocumentId=dt.DocumentId
SELECT
distinct
ColumnName
INTO
#COLUMNS
FROM
#GetDocumentTeams
select
t.TeamId,c.ColumnName
into
#indexedColumns
from
#COLUMNS c
inner
join
pcn.Teams t
on
t.ColumnName=c.ColumnName
declare
@SeletColumnComma
varchar
(
max
)
select
@SeletColumnComma =
coalesce
(@SeletColumnComma +
','
,
''
) +
coalesce
(
'fs'
+
cast
(teamid
as
nvarchar(20)) +
'.FlowStatus as '
+ ColumnName +
'Status'
,
''
)
from
#indexedColumns
select
@SeletColumnComma
---------------
DECLARE
@query nvarchar(
max
)
SET
@query='
select
distinct
dpt.PartId,
' + @SeletColumnComma + '
from
#DocumentPartTeams dpt
inner
join
#GetDocumentTeams gdt
on
gdt.DocumentID=dpt.DocumentID
inner
join
#tempVariables tv
on
tv.DocumentId = dpt.DocumentId
left
join
#FlowStatus fs1
on
dpt.Package=fs1.FlowStatusID
left
join
#FlowStatus fs2
on
dpt.Parametric=fs2.FlowStatusID
left
join
#FlowStatus fs3
on
dpt.Scribing=fs3.FlowStatusID
left
join
#FlowStatus fs4
on
dpt.Lifecycle=fs4.FlowStatusID
left
join
#FlowStatus fs5
on
dpt.OBS=fs5.FlowStatusID
left
join
#FlowStatus fs6
on
dpt.CrossPart=fs6.FlowStatusID
left
join
#FlowStatus fs7
on
dpt.Rohs=fs7.FlowStatusID
'
exec
(@query)
drop
table
#teams
drop
table
#DocumentTeams
drop
table
#FlowStatus
drop
table
#DocumentPartTeams
drop
table
#COLUMNS
drop
table
#indexedColumns
drop
table
#tempVariables
drop
table
#GetDocumentTeams
Result displayed
PartId LifecycleStatus CrossPartStatus
1000 Pending Pending
1002 Pending Pending
so
left
join
or
inner
join
i needed
to
be
only
teams displayed dynamically
so how
to
make that please
Reply
Answers (
1
)
getting wrong Total time
How to create dynamic sql substring based on field name func