My tabular data looks like
DECLARE @Tab TABLE(Name VARCHAR(10), VAL INT)
INSERT INTO @Tab
VALUES ('A', 25), ('B', 30), ('C', 236), ('D', 217), ('E', 19)
SELECT *
FROM @Tab
I want to show my result as:
Total:527 (A:25,B:30,C:236,D:217,E:19)
-
What you're trying to do is called pivoting. There are many examples on this site: stackoverflow.com/questions/tagged/sql+pivot (Of course I'm assuming something a little more general than the specific scenario you described which returns only a single row. If that's all you want, surely it's just a display issue and SQL is not the best tool for the job?)Disillusioned– Disillusioned2017-03-26 10:35:09 +00:00Commented Mar 26, 2017 at 10:35
Add a comment
|
3 Answers
Here is a simple method using a sub query with FOR XML:
SELECT 'Total:' + CAST(SUM(VAL) as varchar(10)) +' ('+
STUFF(
(
SELECT ',' + Name +':'+ CAST(VAL as varchar(10))
FROM @Tab
FOR XML PATH('')
), 1, 1, '') +')'
as result
FROM @Tab
Results:
result
Total:527 (A:25,B:30,C:236,D:217,E:19)
Comments
SELECT SUM(VAL) FROM @tab
check out the following: https://www.w3schools.com/sql/sql_func_sum.asp
1 Comment
Rahul Aggarwal
Thanks, for your reply, along with sum I also want the breakup
One method is with a FOR XML subquery:
DECLARE @Tab TABLE(Name VARCHAR(10), VAL INT);
INSERT INTO @Tab VALUES('A',25),('B',30),('C',236),('D',217),('E',19);
SELECT 'Total:'
+ CAST(SUM(VAL) AS varchar(10))
+ ' ('
+ STUFF((SELECT ',' + Name + ':' + CAST(VAL AS varchar(10))
FROM @Tab
ORDER BY Name
FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)'), 1, 1, '')
+ ' )'
FROM @Tab;
2 Comments
Zohar Peled
Congratulations on 10k reputation
Dan Guzman
Thanks, @ZoharPeled.