April 20, 2012 at 1:17 pm
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) Amount
FROM 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.Id
group by d.Id, d.Name,es.timePeriod
order by d.Name,es.timePeriod
The results are as follows:
ID Name Marks timePeriod Amount
44Business 3.173559 142.31000
44Business 3.173559 253.76000
44Business 3.173559 368.06000
44Business 3.173559 496.18000
44Business 3.173559 5107.88000
44Business 3.173559 6112.08000
44Business 3.173559 7127.63000
44Business 3.173559 8140.46000
44Business 3.173559 9151.18000
44Business 3.173559 10138.89000
44Business 3.173559 11109.37000
44Business 3.173559 1278.64000
What 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 amount
Thanks.
April 20, 2012 at 1:29 pm
If you want some real help take a look at the first link in my signature. Honestly I am surprised that over 300 points you wouldn't know to post ddl, sample data and desired output.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 20, 2012 at 1:33 pm
And after that, check out these articles:
April 20, 2012 at 2:08 pm
I created a variable table and inserted all record in it. Then I used pivot to spread it out across. Problem resolved.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply