How to et count using Pivot table ???? - 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

How to et count using Pivot table ????

Author

Topic

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-02-21 : 10:13:37

Hello all,

i have query where for Customer id i will show counts...

Select B.Cust_ID,B.ProfileOwnerEmpID,LAc.ProfileStatusID
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
[CODE]
Cust_ID ProfileOwnerEmpID ProfileStatusID
62 2 56
64 2 57
31 2 54
22 2 54
32 2 55
16 2 54
40 2 56
[/code]

but i need to get out put like this

one :
[CODE]
Profile 56==ProfileStatusID 57==ProfileStatusID 54==ProfileStatusID
2 count 2 count 2 count 2

[/code]

the result should come like this

P.V.P.MOhan

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-21 : 12:40:20

[code]SELECT
*
FROM
(
Select B.Cust_ID,LAc.ProfileStatusID
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[/code]

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-02-22 : 01:19:10

How to give alias names for [56],[57],[54] this column headers.they are coming after using pivot...

need to come like this
56 as firstcount,57 as Second Count,54 as third count

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-22 : 02:24:44

SELECT
Cust_ID, ProfileStatusID, [56] AS firstCount, [57] AS secondCount, [54] AS thirdCount
FROM
(
Select B.Cust_ID,LAc.ProfileStatusID
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

--
Chandu

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-02-22 : 02:35:43

while executing this query i am getting error as 'The multi-part identifier'

B.Cust_ID, LAc.ProfileStatusID i gave like this entire query baSED ON joins.so it is giving error how to over come this if i remove both in select query result coming fine.


ProfileOwnerEmpID
ProfileOwnerEmpID	56	57	54
2	2	1	3

[CODE]
firstCount secondCount thirdCount
2 1 3
[/code]

how i can get first output with out error 'The multi-part identifier'

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-22 : 02:42:10

[code]
SELECT
ProfileOwnerEmpID, [56] AS firstCount, [57] AS secondCount, [54] AS thirdCount
FROM
(
Select B.Cust_ID,LAc.ProfileStatusID, B.ProfileOwnerEmpID
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
[/code]

EDIT: Red Marked
--
Chandu

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-02-22 : 02:48:25

getting the same error even if i give ProfileOwnerEmpID,Cust_ID if i remove the those columns getting query executed....[56] AS firstCount, [57] AS secondCount, [54] AS thirdCount these three are showing..... along with this three columns i need to show ProfileOwnerEmpID also....suggest me

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-22 : 03:35:03

Check my previous post.... I have edited

--
Chandu

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-02-22 : 04:00:16

thanks chandu....why i missed this one????.....

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-02-22 : 04:17:40

quote:
Originally posted by mohan123

thanks chandu....why i missed this one????.....
P.V.P.MOhan

Welcome

While pivoting you can select the columns that are in inner query....

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources