Click here to Skip to main content
15,867,756 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
1>PersonalDetail

ClientID (pk)
name,
surname
email

2> Skillmaster

Skillid(pk)
skillname

3> EndUser
enduserid(pk)
userid(FK) // perosnaldeail clientid
FeesId(FK)

4> UserskillDetails
id(PK)
ClientId (FK)
SkillId(Fk)

What I have tried:

select PersonalDetails.name,PersonalDetails.phone,PersonalDetails.email,EndUser.Amount,SkillMaster.SkillName
from PersonalDetails join EndUser on PersonalDetails.ClientID = EndUser.UserId
join Userskilldetail on Userskilldetail.clientId = PersonalDetails.ClientID
join SkillMaster on SkillMaster.SkillID = Userskilldetail.Skillid



output
jaydeep	444444	jay@gmail.com	34	mvc
jaydeep	444444	jay@gmail.com	34	jquery



i requried jaydeep as a one record and mvc and jquery on one line with comma
jaydeep	444444	jay@gmail.com	34	mvc,jquery
Posted
Updated 2-Nov-17 19:53pm
Comments
jekin77 2-Nov-17 15:12pm    
The commands to be used are STUFF and FOR XML.
Jaydeep Shah 2-Nov-17 23:17pm    
can you please help me in creating query ..!
j snooze 2-Nov-17 17:18pm    
to expand on jekin77's replly since sql server isn't as nice as oracle or mysql in this particular instance, this link should give you an idea on syntax.

https://stackoverflow.com/questions/15477743/listagg-in-sqlserver
Karthik_Mahalingam 3-Nov-17 0:16am    
possible solution
Jaydeep Shah 3-Nov-17 1:06am    
but this is one table solution ...in my case i have 5 table join.
and skillname with comma

1 solution

Create table #temp(name varchar(50),phone int,email varchar(max),amount money, skillName varchar(10));

insert into #temp values('jaydeep',444444,'jay@gmail.com',34,'mvc'),
('jaydeep',444444,'jay@gmail.com',34,'jquery'),
('jaydeepshah',50,'jaydeepshah81@yahoo.com',	50,	'css'),
('jaydeepshah',50,'jaydeepshah81@yahoo.com',	50,	'sql');

SELECT DISTINCT name,phone,email,Amount,
STUFF((SELECT DISTINCT ', '+ SkillName 
FROM #temp as t1
WHERE T1.Name =t2.Name
FOR XML PATH(''),TYPE
).value('.','NVARCHAR(MAX)') ,1,2,'')AS SkillName 
FROM #temp t2;
------------------------------------------
name	phone	email	Amount	SkillName
------------------------------------------
jaydeep	444444	jay@gmail.com	34.00	jquery, mvc
jaydeepshah	50	jaydeepshah81@yahoo.com	50.00	css, sql

-------------------------------------------------------------------
;WITH X AS( 
SELECT 
      PersonalDetails.name, PersonalDetails.phone,
       PersonalDetails.email,EndUser.Amount,SkillMaster.SkillName 
 FROM PersonalDetails INNER JOIN EndUser 
     ON PersonalDetails.ClientID = EndUser.UserId
   INNER JOIN Userskilldetail ON Userskilldetail.clientId = PersonalDetails.ClientID
   INNER JOIN SkillMaster ON SkillMaster.SkillID = Userskilldetail.Skillid)
 
   SELECT DISTINCT name,phone,email,Amount,
        STUFF((SELECT DISTINCT ', '+SKL.SkillName 
             FROM X AS SKL WHERE SKL.Name=X.Name
                 FOR XML PATH(''),TYPE
                    ).value('.','NVARCHAR(MAX)') ,1,2,'')AS  SkillName 
   FROM X
 
Share this answer
 
v2
Comments
Karthik_Mahalingam 3-Nov-17 1:56am    
dont post multiple solution, keep only one and delete other
Santosh kumar Pithani 3-Nov-17 2:20am    
Solution 1 is deleted and modified query is updated in solution 2. Thank u!
Jaydeep Shah 5-Nov-17 9:04am    
Thank you so much for your solution.
Karthik_Mahalingam 7-Nov-17 9:13am    
Welcome
Santosh kumar Pithani 5-Nov-17 22:50pm    
Always welcome!

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900