CODEWITHSUNDEEP
sqlsql-server
Rahul Aggarwal
Rahul Aggarwal

Reputation: 291

Show different row and column data in one column using SQL query

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)

Upvotes: 0

Views: 70

Answers (3)

Zohar Peled
Zohar Peled

Reputation: 82474

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)

See live demo on rextester

Upvotes: 1

Dan Guzman
Dan Guzman

Reputation: 46203

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;

Upvotes: 0

Arie
Arie

Reputation: 96

SELECT SUM(VAL) FROM @tab

check out the following: https://www.w3schools.com/sql/sql_func_sum.asp

Upvotes: 0

Related Questions