Showing multiple group values in one row

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
stalebAsked:
Who is Participating?
 
SharathData EngineerCommented:
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

Open in new window

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
*/

Open in new window

0
 
Barry CunneyCommented:
Hi Staleb,
Please try the following:

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

Open in new window

0
 
stalebAuthor Commented:
This gave me 3 rows.
Can these three lines be combined into one?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
stalebAuthor Commented:
think I may have solved it with stuff  function
0
 
SharathData EngineerCommented:
What is your SQL Server version?
0
 
stalebAuthor Commented:
SQL 2012
0
 
stalebAuthor Commented:
Yes that worked perfect.
Also made it work with a stuff command

Tnx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.