Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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 ???
 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
2241 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
2241 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
2241 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  
 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.06 seconds. Powered By: Snitz Forums 2000