Is that possible to show pivot for 2 tables ??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

All Forums

General SQL Server Forums

New to SQL Server Programming

Is that possible to show pivot for 2 tables ???

Author

Topic

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-02-22 : 01:44:46

hello all,

i had a query

like this :
[CODE]
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
[/CODE]

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 :

[CODE]
ProfileOwnerEmpID IsSerious IsConfidential 56 57 54
2 1 1 2 1 3
[/CODE]

now ProfileStatusID of 56 ,54,57 count coming

how can i get count for IsSerious and IsConfidential ???

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-22 : 02:21:52

Means do you want IsSerious, IsConfidential count for ProfileStatusIDs( 56, 57, 54 )?

--
Chandu

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-02-22 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-22 : 02:39:18

[code]
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
[/code]

--
Chandu

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-02-22 : 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
Constraint Violating Yak Guru

252 Posts

Posted - 2013-02-22 : 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

[/code]

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-22 : 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
Constraint Violating Yak Guru

252 Posts

Posted - 2013-02-22 : 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
[/code]
[CODE]
so here observe for ProfileOwnerEmpID = 2 i am using pivot and getting count of column ProfileStatusID as [56],[57],[58]. now see IsSerious IsConfidential have 1 as value

for 56 how many IsSerious IsConfidential counts and for 57 columns IsSerious IsConfidential how many counts
[/CODE]
P.V.P.MOhan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources