Click here to Skip to main content
13,626,461 members
Rate this:
 
Please Sign up or sign in to 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 2-Nov-17 8:50am
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 Bangalore 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
Karthik Bangalore 3-Nov-17 1:11am
   
Use CTE
Jaydeep Shah 3-Nov-17 1:37am
   
CTE ??
Karthik Bangalore 3-Nov-17 1:42am
   
Karthik Bangalore 3-Nov-17 1:43am
   
check the code posted by santhosh it works fine.
Santosh kumar Pithani 3-Nov-17 1:50am
   
Thank you Karthik!
Santosh kumar Pithani 3-Nov-17 1:02am
   
Jaydeep,i will show you query as per your requirement which will help you
Jaydeep Shah 3-Nov-17 1:20am
   
it give me same output as above
jaydeep shah jaydeepshah81@yahoo.com 50 css
jaydeep shah jaydeepshah81@yahoo.com 50 sql
Santosh kumar Pithani 3-Nov-17 1:41am
   
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
Karthik Bangalore 3-Nov-17 1:44am
   
update your solution with this code
Jaydeep Shah 3-Nov-17 1:52am
   
which code ?
Karthik Bangalore 3-Nov-17 1:56am
   
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
Santosh kumar Pithani 3-Nov-17 1:26am
   
Have you executed query?
Jaydeep Shah 3-Nov-17 1:27am
   
yes
but it give me same output
Santosh kumar Pithani 3-Nov-17 1:49am
   
I think your "SkillMaster.SkillID = Userskilldetail.Skillid" relation has different values
Jaydeep Shah 3-Nov-17 1:52am
   
skillmaster (skillid is primary)
and userskilldetail (skillid is foreign key)
Santosh kumar Pithani 3-Nov-17 2:05am
   
Query is modified so check it now and update me.

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

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
  Permalink  
v2
Comments
Karthik Bangalore 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 Bangalore 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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web02-2016 | 2.8.180712.1 | Last Updated 3 Nov 2017
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100