Click here to Skip to main content
15,936,119 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:

I have 2 tables like this.




ECodeEName
1Ram
2Shayam
3Hari
4Joseph
5Abdul



ECodeManagerName
1Peter
2Mac
3Mac
4Peter
5Peter


Now i need to write a query which gives the output like this:-

ManagerNameEName
Peter Peter Total
PeterRam
PeterJoseph
PeterAbdul
MacMac Total
MacShyam
MacHari



Here you can see that 3 Users are reporting to Peter(Ram, Joseph & Abdul) but in the Result one extra row is there with Ename as "Peter Total" and same for Mac also.SO here i am facing problem that how to add a extra row during every insertion where the managername and Managername+ 'Total' should be added in the result set.
Posted
Updated 18-Jan-12 23:38pm
v2
Comments
Amir Mahfoozi 19-Jan-12 5:34am    
However you can solve this by SQL but it's an output problem(Grid or Report) and it is not a SQL problem.
Dharmenrda Kumar Singh 19-Jan-12 5:36am    
I Need the output like this using SQl.

1 solution

Try this

SQL
DECLARE @ENameTable AS Table (ECode BIGINT, EName NVARCHAR(50))
DECLARE @ManagerTable AS Table (ECode BIGINT, ManagerName NVARCHAR(50))

INSERT INTO @ENameTable
SELECT 1,'Ram' UNION
SELECT 2,'Shayam' UNION
SELECT 3,'Hari' UNION
SELECT 4,'Joseph' UNION
SELECT 5,'Abdul'

INSERT INTO @ManagerTable
SELECT 1,'Peter' UNION
SELECT 2,'Mac'  UNION
SELECT 3,'Mac'  UNION
SELECT 4,'Peter'  UNION
SELECT 5,'Peter'

SELECT First_Col,Sec_col  FROM
(
SELECT 1 rwid,ManagerName  First_Col, ManagerName + CAST(COUNT(ManagerName) AS NVARCHAR(10))  Sec_col  FROM @ManagerTable GROUP BY  ManagerName
UNION ALL
SELECT 2 rwid ,M.ManagerName First_Col,E.EName  Sec_col  FROM @ENameTable E Inner Join @ManagerTable M ON E.ECode=M.ECode
)  AS TBLE
ORDER BY First_Col,rwid
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900