tricky join in sql | sql server interverview question and answer | comma separated values | Part 11

tricky sql server interview question.
Must watch full video and do subscribe the channel and please share to others.
Query is available in the comment box.

[ssunitech]

/*
Create Table Item_Master
(
Id Int,
Did Varchar(20)
)
Create Table Variant_Master
(
Id Int,
Name Varchar(10)
)
Insert Item_Master Values (1, '1, 2'), (2, '3, 4'), (3, '5, 4'), (4, '3, 4'), (5, '11, 2')
Insert Variant_Master Values (1, 'A'), (2, 'B'), (3, 'C'), (4, 'D'), (5, 'E'), (11, K)
*/





Select *
From Item_Master

Select *
From Variant_Master



SELECT CHARINDEX(', ', '')-1)


;WITH CTE AS
(
Select *,
LEFT(DID, CHARINDEX(', ', DID)-1) AS LEFTJOIN,
SUBSTRING(DID, CHARINDEX(', ', DID)+1, LEN(DID)-CHARINDEX(', ', DID)) AS RIGHTJOIN
From Item_Master as I
)
SELECT CTE.ID, V1.Name+', '+V2.Name [DESC]
FROM CTE AS CTE
LEFT JOIN Variant_Master V1
ON CTE.LEFTJOIN = V1.Id
LEFT JOIN Variant_Master V2
ON CTE.RIGHTJOIN = V2.Id

[GaneshNaduvathati]

;with cte as
(
select id, left(Did, charindex(', ', Did)-1) as firstaname, right(Did, len(Did)-charindex(', ', Did)) as lastname
from Item_Master
)
select c.id, v.Name+', '+vm.Name as [Desc] from cte c left join Variant_Master v on c.firstaname=v.Id
left join Variant_Master vm on c.lastname=vm.Id

[ravindrareddych]

Hi, instead of charindex and substring all we can use right( column, 1) and left (column, 1). Thank for the videos thease are very helpfull.

[vedasiva]

Great content. But please try to improve video quality as the code is almost illegible.

[varshadeore]

---How i can acheive below output
Input
E_ID E_name MGR_ID
1 A -
2 B 1
3 C 2
4 D 2

Output

E_ID E_name MGR_Name
1 A -
2 B A
3 C B
4 D B

[vinayasenakatta]

Nice one, Thanks one small remark the clarity of video is not good. please improve on this thanks again