jamesheslin123 (8/13/2013)
First of all, thank you! You solved the problem I had and now the view is very close to what I am looking for. Despite the error message: The OVER SQL construct or statement is not supported.... it seems to work regardless, and, if it works, well that is what counts.
Are you by chance doing this in SSRS? If so, you should turn your sql into a stored procedure instead of putting the sql in your report.
Yes, I take full blame for that. Just know that it was ignorance and not intentional laziness on my part.
No problem. Just trying to emphasize the importance of testing the scripts you post. 🙂
Is there a way to join the first and last names for each employee into a single cell? In other words, instead of four cells for two employees, it would only be two cells. Each employee name cell would countain Firstname Lastname. For example, instead of: John, Smith, Mary, Johnson. It would be: John Smith, Mary Johnson.
Sure this is quite simple. Just add the two columns together. A very minor change to the previous query can accomplish this.
with SortedData as
(
select e.*, s.SpaceType, s.Space_Number as Space_SpaceNumber, ROW_NUMBER() over (partition by s.Space_Number order by (select null)) as RowNum
from employee e
join spaces s on e.space_Number = s.space_id
)
select CC, Space_Number, SpaceType, Space_SpaceNumber,
MAX(case when RowNum = 1 then FirstName + ' ' + LastName end) as Name1,
MAX(case when RowNum = 2 then FirstName + ' ' + LastName end) as Name2
from SortedData s
group by
CC, Space_Number, SpaceType, Space_SpaceNumber
_______________________________________________________________
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/