Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Add rows in same row

Author  Topic 

Pirre0001
Starting Member

19 Posts

Posted - 2014-02-27 : 11:09:07
The rows look like this in the table:

Rownr---Batch---Quant
1-------E34-----3
2-------E36-----2
3-------E44-----6
4-------E34-----4
5-------E37-----12
6-------E36-----1
7-------E36-----5

I want it show in same row like this:

All_Batch
E34: 7
E36: 8
E44: 6
E37: 12

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-27 : 12:15:09
[code]
SELECT Batch,SUM(Quant) AS Total
FROM table
GROUP BY Batch
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Pirre0001
Starting Member

19 Posts

Posted - 2014-02-27 : 15:16:51
This will not show it on same row. This will be on 4 rows.
My problem is that I want it on one row.

quote:
Originally posted by visakh16


SELECT Batch,SUM(Quant) AS Total
FROM table
GROUP BY Batch


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-02-27 : 15:23:59
[code]DECLARE @Sample TABLE
(
RowNr INT,
Batch CHAR(3),
Quant INT
);

INSERT @Sample
(
RowNr,
Batch,
Quant
)
VALUES (1, 'E34', 3),
(2, 'E36', 2),
(3, 'E44', 6),
(4, 'E34', 4),
(5, 'E37', 12),
(6, 'E36', 1),
(7, 'E36', 5);

DECLARE @Result VARCHAR(MAX);

SELECT @Result = CAST(Data AS VARCHAR(MAX))
FROM (
SELECT Batch + ': ' + CAST(SUM(Quant) AS VARCHAR(12)) + CHAR(10)
FROM @Sample
GROUP BY Batch
FOR XML PATH('')
) AS d(Data);

PRINT '---------------'
PRINT @Result;[/code]


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
   

- Advertisement -