Solved

# Showing multiple group values in one row

Posted on 2016-09-30
37 Views
Hi how can I show multiple vaules in the same group row
(not sure if this is the right way to say it)

example
table( PalletNo, box weight, count)
Values
19,10,1
19,20,1
19,20,1
19,20,1

How do I querie this so that it will display
Pallet,   boxes,            sum(count)
19,        20x3  10x1,    4
0
Question by:staleb
• 4
• 2

LVL 17

Expert Comment

ID: 41823315
Hi Staleb,

``````SELECT PalletNo, [box weight], COUNT(*)
FROM T
GROUP BY GROUPING SETS ((PalletNo), ([box weight]))
``````
0

Author Comment

ID: 41823336
This gave me 3 rows.
Can these three lines be combined into one?
0

Author Comment

ID: 41823659
think I may have solved it with stuff  function
0

LVL 40

Expert Comment

ID: 41824267
What is your SQL Server version?
0

Author Comment

ID: 41825419
SQL 2012
0

LVL 40

Accepted Solution

Sharath earned 500 total points
ID: 41825858
check this.
``````;with cte as (
select  PalletNo, convert(varchar,box_weight) + 'x' + convert(varchar,count(*)) boxes, count(*) cnt
from your_table group by PalletNo, box_weight),
cte2 as (
select PalletNo,
rtrim(substring(isnull((select ','+boxes from cte t1 where t1.PalletNo = t2.PalletNo for xml path('')),' '),2,2000)) boxes,
cnt
from cte t2)
select PalletNo, boxes,sum(cnt) total from cte2 group by PalletNo,boxes
``````
example
``````declare @table table (PalletNo int, box_weight int, cnt int)
insert @table values (19,10,1),(19,20,1),(19,20,1),(19,20,1)

;with cte as (
select  PalletNo, convert(varchar,box_weight) + 'x' + convert(varchar,count(*)) boxes, count(*) cnt
from @table group by PalletNo, box_weight),
cte2 as (
select PalletNo,
rtrim(substring(isnull((select ','+boxes from cte t1 where t1.PalletNo = t2.PalletNo for xml path('')),' '),2,2000)) boxes,
cnt
from cte t2)
select PalletNo, boxes,sum(cnt) total from cte2 group by PalletNo,boxes
/*
PalletNo	boxes	total
19	10x1,20x3	4
*/
``````
0

Author Closing Comment

ID: 41833078
Yes that worked perfect.
Also made it work with a stuff command

Tnx
0

## Featured Post

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.