| Author |
 Topic  |
|
|
mohan123
Posting Yak Master
India
208 Posts |
 Posted - 02/21/2013 : 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
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
|
|
|
James K
Flowing Fount of Yak Knowledge
1730 Posts |
Posted - 02/21/2013 : 12:40:20
|
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 |
 |
|
|
mohan123
Posting Yak Master
India
208 Posts |
Posted - 02/22/2013 : 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
Flowing Fount of Yak Knowledge
India
1711 Posts |
Posted - 02/22/2013 : 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
Posting Yak Master
India
208 Posts |
Posted - 02/22/2013 : 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
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1711 Posts |
Posted - 02/22/2013 : 02:42:10
|
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
EDIT: Red Marked
--
Chandu |
Edited by - bandi on 02/22/2013 03:23:45 |
|
|
|
mohan123
Posting Yak Master
India
208 Posts |
Posted - 02/22/2013 : 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
Flowing Fount of Yak Knowledge
India
1711 Posts |
Posted - 02/22/2013 : 03:35:03
|
Check my previous post.... I have edited
--
Chandu |
|
|
|
mohan123
Posting Yak Master
India
208 Posts |
Posted - 02/22/2013 : 04:00:16
|
thanks chandu....why i missed this one????.....
P.V.P.MOhan |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1711 Posts |
Posted - 02/22/2013 : 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 |
|
|
| |
Topic |
|
How to et count using Pivot table ????
