SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Is that possible to show pivot for 2 tables ???
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 02/22/2013 :  01:44:46  Show Profile  Reply with Quote
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
2206 Posts

Posted - 02/22/2013 :  02:21:52  Show Profile  Reply with Quote
Means do you want IsSerious, IsConfidential count for ProfileStatusIDs( 56, 57, 54 )?

--
Chandu
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 02/22/2013 :  02:26:31  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 02/22/2013 :  02:39:18  Show Profile  Reply with Quote

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
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 02/22/2013 :  02:54:34  Show Profile  Reply with Quote
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
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 02/22/2013 :  03:18:37  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 02/22/2013 :  03:34:03  Show Profile  Reply with Quote
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
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 02/22/2013 :  05:27:57  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000