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.
| Author |
Topic |
|
rama108
Posting Yak Master
115 Posts |
 Posted - 2012-04-20 : 15:25:47
|
| Hello All, I have the query as follows:SELECT d.Id, d.Name, ISNULL(AVG(ev.marks),0) marks, es.timePeriod, ISNULL(SUM(es.amount),0) AmountFROM depts d INNER JOIN Employees e ON d.Id = e.deptId INNER JOIN EmployeeScales ee ON e.Id = ee.EmployeeId INNER JOIN scales ev ON ee.scaleId = ev.Id inner join scaleMatrix es on ev.MatrixId = es.Idgroup by d.Id, d.Name,es.timePeriodorder by d.Name,es.timePeriodThe results are as follows:ID Name Marks timePeriod Amount44 Business 3.173559 1 42.3100044 Business 3.173559 2 53.7600044 Business 3.173559 3 68.0600044 Business 3.173559 4 96.1800044 Business 3.173559 5 107.8800044 Business 3.173559 6 112.0800044 Business 3.173559 7 127.6300044 Business 3.173559 8 140.4600044 Business 3.173559 9 151.1800044 Business 3.173559 10 138.8900044 Business 3.173559 11 109.3700044 Business 3.173559 12 78.64000What I need to do is to have id, name, marks, timeperiods and amounts in one record as follows:44 Business 3.172559 1 2 3 4 5 6 7 8 9 10 11 12 followed by the values of each amountThanks. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
rama108
Posting Yak Master
115 Posts |
Posted - 2012-04-20 : 16:07:03
|
| I created a variable table and inserted all record in it. Then I used pivot to spread it out across. Problem resolved. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-21 : 13:14:57
|
| cool------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
need help with pivot query