September 24, 2013 at 7:27 am
Hi,
I have a table say emp which has two columns empid and empskills.I need a query to retrieve the empid which has both C and CPP.
Input:
Empid Empskills
1 C
1 CPP
1 VB
2 C
2 CPP
3 C
4 CPP
From the above input ,I need output like this(otherwise only empid)
Empid Empskills
1 C,CPP,VB
2 C,CPP
If I use the below query,i ll get all the emp id.But I need only the empid which has both the given skills .
Select distinct empid from emp
where empskills in ('C','CPP')
Thanks in advance
Thanks,
Gopinath.
September 24, 2013 at 7:49 am
Quick answer:
selecta.Empid, a.Empskills+', '+b.Empskills
from#Empa
inner join #Empb on a.Empid = b.Empid
where a.Empskills = 'C' and b.Empskills = 'CPP'
September 24, 2013 at 7:50 am
DECLARE @t TABLE(Empid INT,Empskills VARCHAR(3))
INSERT INTO @t(Empid ,Empskills)
VALUES
(1, 'C'),
(1, 'CPP'),
(1, 'VB'),
(2, 'C'),
(2, 'CPP'),
(3, 'C'),
(4, 'CPP');
SELECT t.Empid,
STUFF((SELECT ',' + t2.Empskills AS "text()"
FROM @t t2
WHERE t2.Empid = t.Empid
ORDER BY t2.Empskills
FOR XML PATH(''),TYPE).value('./text()[1]','VARCHAR(100)'),1,1,'') AS Empskills
FROM @t t
WHERE t.Empskills IN ('C','CPP')
GROUP BY t.Empid
HAVING COUNT(DISTINCT t.Empskills)=2;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537September 24, 2013 at 7:53 am
Another alternative:
SELECT ID
FROM emp
WHERE empskills = 'C'
INTERSECT
SELECT ID
FROM emp
WHERE empskills = 'CPP'
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 25, 2013 at 2:08 am
I would do this way:
DECLARE @t TABLE(Empid INT,Empskills VARCHAR(3))
INSERT INTO @t(Empid ,Empskills)
VALUES (1, 'C'),(1, 'CPP'),(1, 'VB'),(2, 'C'),(2, 'CPP'),(3, 'C'),(4, 'CPP');
SELECT empid FROM @t
WHERE empskills in ('C','CPP')
GROUP BY empid
HAVING COUNT(DISTINCT empskills)=2
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy