Urgent Help Guys for this Scenario!!!
-
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.
-
Quick answer:
selecta.Empid, a.Empskills+', '+b.Empskillsfrom#Empa
inner join #Empb on a.Empid = b.Empid
where a.Empskills = 'C' and b.Empskills = 'CPP'
-
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/61537
-
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 -
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