SO this works!!!
Client.LName + ', ' + Client.FName + ' -' + cast(client.ID as nvarchar) as ClientName
Be very careful about using the default lengths for datatypes. When you don't specify the length you get the default size, and this can vary depending on context. I would add a length for your nvarchar. I don't know what datatype your ID is but 10 should be plenty of room. Make it smaller if you don't need that much length, or longer if needed.
Client.LName + ', ' + Client.FName + ' -' + cast(client.ID as nvarchar(10)) as ClientName
As a side note, it is often considered poor naming convention to have ID. What happens when you have two tables with a PK named ID? Something like ClientID is probably a better choice. One general rule for databases is that is usually preferred that a column name not change it's name when referenced as a foreign key. For example. If you have an Order table and the PK is ID it gets awfully confusing. You have a column with the same name in two tables but you join those tables on Order.ClientID = Client.ID where Order.ID = 8473. That just makes my head spin. If those same two table were aliased O and C the join becomes very clear (O.ClientID = C.ClientID where O.OrderID = 8473
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 Moden's splitter
.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2)