- Need to use CAST or CONVERT to make the numeric into a varchar, then concatenate it with '%'
Option 2:
- Use the Format function in SQL Server 2012
When I tested it, option 2 is much slower than option 1. So if you just want to use it for the presentation purpose, option 1 is recommended.
SELECT
custid, orderid, val,
CAST(CAST(100.0
* val / SUM(val) OVER(PARTITION BY custid) AS NUMERIC(5, 2)) as VARCHAR) +'%' AS [pctcust_in_%],
FORMAT(CAST(100.0
* val / SUM(val) OVER() AS NUMERIC(5, 2)), 'p', 'en-US') AS [pcttotal_in_%]
FROM
Sales.OrderValues;