| Author |
 Topic  |
|
|
mohan123
Posting Yak Master
India
194 Posts |
 Posted - 02/22/2013 : 01:44:46
|
hello all,
i had a query
like this :
Select B.Cust_ID,B.ProfileOwnerEmpID,LAc.ProfileStatusID,count(B.IsSerious)as Serious ,count(B.IsConfidential) As confidential
from Emp_Details E join Cust_BasicInfo B on E.EmpID=B.ProfileOwnerEmpID join Cust_Login LAc on LAc.Cust_ID=B.Cust_ID
where E.EmpID=2
group by B.Cust_ID,B.ProfileOwnerEmpID,LAc.ProfileStatusID,B.IsConfidential,B.IsSerious
and it is giving result set as :
Cust_ID ProfileOwnerEmpID ProfileStatusID Serious confidential
16 2 54 1 1
22 2 54 1 1
31 2 54 1 1
32 2 55 1 1
40 2 56 1 1
62 2 56 1 1
64 2 57 1 1
i have a query like this :
SELECT
*
FROM
(
Select B.Cust_ID,B.ProfileOwnerEmpID,LAc.ProfileStatusID,B.IsSerious,B.IsConfidential
from Emp_Details E join Cust_BasicInfo B on E.EmpID=B.ProfileOwnerEmpID join Cust_Login LAc on LAc.Cust_ID=B.Cust_ID
where E.EmpID=2
) s
PIVOT (COUNT(Cust_ID) FOR ProfileStatusID IN ([56],[57],[54]))P
but now i am using pivot i am gettingresult like :
ProfileOwnerEmpID IsSerious IsConfidential 56 57 54
2 1 1 2 1 3
now ProfileStatusID of 56 ,54,57 count coming
how can i get count for IsSerious and IsConfidential ???
P.V.P.MOhan |
Edited by - mohan123 on 02/22/2013 01:45:53
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 02/22/2013 : 02:21:52
|
Means do you want IsSerious, IsConfidential count for ProfileStatusIDs( 56, 57, 54 )?
--
Chandu |
 |
|
|
mohan123
Posting Yak Master
India
194 Posts |
Posted - 02/22/2013 : 02:26:31
|
yes chandu like 54 have IsSerious count = 3 and 56 have isconfidential count = 2 like that i need to get count basing on ( 56, 57, 54 )?
P.V.P.MOhan |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 02/22/2013 : 02:39:18
|
DECLARE @tab TABLE (Cust_ID INT, ProfileOwnerEmpID INT,ProfileStatusID INT,Serious INT,confidential INT)
INSERT INTO @tab
SELECT 16, 2, 54, 1, 1 UNION ALL
SELECT 22, 2, 54, 1, 1 UNION ALL
SELECT 31, 2, 54, 1, 1 UNION ALL
SELECT 32, 2, 55, 1, 1 UNION ALL
SELECT 40, 2, 56, 1, 1 UNION ALL
SELECT 62, 2, 56, 1, 1 UNION ALL
SELECT 64, 2, 57, 1, 1
SELECT ProfileOwnerEmpID,
COUNT( CASE WHEN ProfileStatusID = 56 THEN Cust_ID END)[56StatusCnt],
COUNT( CASE WHEN ProfileStatusID = 57 THEN Cust_ID END) [57StatusCnt],
COUNT( CASE WHEN ProfileStatusID = 54 THEN Cust_ID END) [54StatusCnt],
COUNT( CASE WHEN ProfileStatusID = 56 THEN Serious END) [56SerCnt],
COUNT( CASE WHEN ProfileStatusID = 57 THEN Serious END) [57SerCnt],
COUNT( CASE WHEN ProfileStatusID = 54 THEN Serious END) [54SerCnt],
COUNT( CASE WHEN ProfileStatusID = 56 THEN confidential END) [56ConfCnt],
COUNT( CASE WHEN ProfileStatusID = 57 THEN confidential END) [57ConfCnt],
COUNT( CASE WHEN ProfileStatusID = 54 THEN confidential END) [54ConfCnt]
FROM @tab
GROUP BY ProfileOwnerEmpID
--
Chandu |
|
|
|
mohan123
Posting Yak Master
India
194 Posts |
Posted - 02/22/2013 : 02:54:34
|
hey chandu with this query i got result set like
ProfileOwnerEmpID 56StatusCnt 57StatusCnt 54StatusCnt 56SerCnt 57SerCnt 54SerCnt 56ConfCnt 57ConfCnt 54ConfCnt
2 2 1 3 2 1 3 2 1 3
but i need to get like
ProfileOwnerEmpID sruscount confidentialcount 56 57 54
2 1 1 2 1 3
like for 56 column have IsSerious = 3 times then need to show count '3'
57 column have Isconfidential = 2 times then need to show count '2'.
P.V.P.MOhan |
|
|
|
mohan123
Posting Yak Master
India
194 Posts |
Posted - 02/22/2013 : 03:18:37
|
after executing your query i am getting out put like this
[CODE]
56StatusCnt 57StatusCnt 54StatusCnt 56SerCnt 57SerCnt 54SerCnt 56ConfCnt 57ConfCnt 54ConfCnt
0 0 1 0 0 1 0 0 1
0 0 1 0 0 1 0 0 1
0 0 1 0 0 1 0 0 1
0 0 0 0 0 0 0 0 0
1 0 0 1 0 0 1 0 0
1 0 0 1 0 0 1 0 0
0 1 0 0 1 0 0 1 0
but i want out put like this
ProfileOwnerEmpID sruscount confidentialcount 56 57 54
2 1 1 2 1 3
P.V.P.MOhan |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 02/22/2013 : 03:34:03
|
am not getting your point... can you post complete expected output?
And also result of this query....
Select B.Cust_ID,B.ProfileOwnerEmpID,LAc.ProfileStatusID, B.IsSerious as Serious ,B.IsConfidential As confidential
from Emp_Details E join Cust_BasicInfo B on E.EmpID=B.ProfileOwnerEmpID join Cust_Login LAc on LAc.Cust_ID=B.Cust_ID
where E.EmpID=2
--
Chandu |
|
|
|
mohan123
Posting Yak Master
India
194 Posts |
Posted - 02/22/2013 : 05:27:57
|
SELECT
*
FROM
(
Select B.Cust_ID,B.ProfileOwnerEmpID,LAc.ProfileStatusID,
SUM(case when B.IsSerious = 1 then 1 else 0 END) as sruscount,
SUm(case when B.IsConfidential = 1 then 1 ELSE 0 END) as confidentialcount
from Emp_Details E join Cust_BasicInfo B on E.EmpID=B.ProfileOwnerEmpID join Cust_Login LAc on LAc.Cust_ID=B.Cust_ID
where E.EmpID=2
group by B.IsConfidential,B.IsSerious ,B.Cust_ID,B.ProfileOwnerEmpID,LAc.ProfileStatusID
) s
PIVOT (COUNT(Cust_ID) FOR ProfileStatusID IN ([56],[57],[54]))P
okay while i am executing this query i am getting output like this one :
ProfileOwnerEmpID sruscount confidentialcount 56 57 54
2 1 1 2 1 3 need to get output like this
but while with out using pivot i am getting out put like this one :
[CODE]
Cust_ID ProfileOwnerEmpID ProfileStatusID IsSerious IsConfidential
62 2 56 1 1
64 2 57 1 1
31 2 54 1 1
22 2 54 1 1
32 2 55 1 1
16 2 54 1 1
40 2 56 1 1
|
Edited by - mohan123 on 02/22/2013 05:30:27 |
|
|
| |
Topic |
|
|
|
Is that possible to show pivot for 2 tables ???
