Concatenating fields in group by

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

Concatenating fields in group by

Author

Topic

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-09-19 : 11:25:58

Hi

If I am grouping by a certain field how can you concatenate varchar values that correspond to the rows being grouped without using function?

For instance below I have:


Col1     Col2    Value
===      ===     ====
A        B       100
A        C       150
X        Y       200
X        Z       250
F        G       300
F        N       350
F        R       400

And want to get:


NewCol  NewCol2  valueTotal
=====  =======   ==========
A A       B C    250
X X       Y Z    450

And I need to show only results under a value of 300 and Col1 count <= 2.

G

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 11:43:19

might be this?


SELECT REPLICATE(Col1 + ' ',Cnt), 
STUFF((SELECT ' ' + Col2 FROM #temp12345 WHERE Col1=t.Col1 ORDER BY Col2 FOR XML PATH('')),1,1,'') AS NewCol2,
Tot AS ValueTotal
FROM
(
SELECT Col1,COUNT(*) AS Cnt,SUM(Value) AS Tot
FROM #temp12345
GROUP BY Col1
HAVING COUNT(*) <=2
AND SUM(Value)<=300
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-19 : 11:55:32

Without using a function, I end up with something that looks like Page47's techniques (found here; http://www.sqlteam.com/article/converting-multiple-rows-into-a-csv-string-set-based-method).

create table #t ( id int identity(1,1), Col1 varchar(100), col1Ct int default 1, Col2 varchar(100), Value int, newCol1 varchar(100) default '', newCol2 varchar(100) default '', total int default 0)

insert #t (col1,col2,value)
select 'A','B',100
union all select 'A','C',150
union all select 'X','Y',200
union all select 'X','Z',250
union all select 'F','G',300
union all select 'F','N',350
union all select 'F','R',400

declare
    @newCol1 varchar(100),
    @col1Ct int,
    @newCol2 varchar(100),
	@total int,	
    @lasti varchar(100)

select
    @newCol1 = '',
    @col1Ct = 1,
    @newCol2 = '',
    @total = 0,        
    @lasti = ''
update
    #t
set
    @newCol1 = newCol1 = case when @lasti <> col1 then col1 else @newCol1 + ' ' + col1 end,
    @col1Ct = col1Ct = case when @lasti <> col1 then col1Ct else @col1Ct + 1 end,        
    @newCol2 = newCol2 = case when @lasti <> col1 then col2 else @newCol2 + ' ' + col2 end,    
    @total = total = case when @lasti <> col1 then value else @total + value end,        
    @lasti = col1

select 
	t.newCol1,
	t.newCol2,
	t.total
 from #t t
join 
(
	select MAX(id) id, col1
	from #t
	group by col1
) d on d.id = t.id
where t.col1Ct <3 AND t.total < 300

drop table #t

HTH.

EDIT. Fixed error in total calc...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 12:00:08

this is known as quirky update. see below for things to be taken care for this

http://visakhm.blogspot.com/2010/03/using-quirky-updates-to-develop-well.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-19 : 12:05:37

quote:
Originally posted by visakh16

this is known as quirky update. see below for things to be taken care for this

http://visakhm.blogspot.com/2010/03/using-quirky-updates-to-develop-well.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hello visakh16,

Nice article. Thanks for posting.

I think 'quirky' is a appropriate descriptor. :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 12:08:09

no probs

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-09-19 : 12:11:08

Thanks for help so far I will try some of this tomorrow with a fresh pair of eyes.

G

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2011-09-19 : 12:14:54

quote:
Originally posted by visakh16

might be this?

SELECT REPLICATE(Col1 + ' ',Cnt),
STUFF((SELECT ' ' + Col2 FROM #temp12345 WHERE Col1=t.Col1 ORDER BY Col2 FOR XML PATH('')),1,1,'') AS NewCol2,
Tot AS ValueTotal
FROM
(
SELECT Col1,COUNT(*) AS Cnt,SUM(Value) AS Tot
FROM #temp12345
GROUP BY Col1
HAVING COUNT(*) <=2
AND SUM(Value)<=300
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

This is very nice.

Thanks for posting.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-19 : 12:39:04

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-09-20 : 05:19:38

quote:
Originally posted by visakh16

might be this?

SELECT REPLICATE(Col1 + ' ',Cnt),
STUFF((SELECT ' ' + Col2 FROM #temp12345 WHERE Col1=t.Col1 ORDER BY Col2 FOR XML PATH('')),1,1,'') AS NewCol2,
Tot AS ValueTotal
FROM
(
SELECT Col1,COUNT(*) AS Cnt,SUM(Value) AS Tot
FROM #temp12345
GROUP BY Col1
HAVING COUNT(*) <=2
AND SUM(Value)<=300
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Hi thanks visakhm that works good. I have pluged in all my columns but still need to study stuff and replicate to work out exactly what it is doing so I understand it.

G

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 05:23:39

welcome
let me know if you need more clarification on any of above once you've dug through!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-09-20 : 09:20:05

Hi

I have been trying to understand the STUFF function and think I have an understanding of its operation, and by running the statement below:


select STUFF('1234567', 3, 3, 'replacement')

Result
======
12replacement567

select STUFF('1234567', 2, 3, 'replacement')

Result
======
1replacement67

And changing the numbers to see the different results.

However I still can't really fathom out what it is doing in my query; I think being in conjunction with the replicate function is confusing me a bit.

G

select REPLICATE(Productcode + ' ', cnt), 
	   STUFF((SELECT ' ' + businessunit 
	          FROM products 
	          WHERE ProductCode = t.productcode 
	          ORDER BY BusinessUnit 
	          FOR XML PATH('')),1,1,'') AS BusinessUnits,
	   total as valuetotal
	   FROM
      (select ProductCode, 
       COUNT(*) AS cnt, 
       SUM(ForecastValue) AS total
       from products
	   group by ProductCode
       having COUNT(*) <= 2
       AND SUM(ForecastValue) <=65000) t

I will continue trying to understand what the STUFF function is doing in this particular instance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 10:50:39

run this and see

SELECT ' ' + businessunit
FROM products
--WHERE ProductCode = t.productcode
ORDER BY BusinessUnit
FOR XML PATH('')

this will give you resultset in a space separated list
when you add it as correlated query in main query it will return the related businessunit in a space separated list. but it will have an additional leading space. the STUFF is used for removing it.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-09-20 : 11:28:05

Ok I am starting to build a better picture of what it is actually doing now, thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-20 : 11:32:59

welcome as always

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-09-21 : 11:10:26

Ok now The code I have is below, I need to concatenate both productcode and business unit hence the slight change to not using function replicate:

select STUFF((SELECT ', ' + ProductCode 
	          FROM products 
	          WHERE ProductCode = t.productcode 
	          ORDER BY ProductCode 
	          FOR XML PATH('')),1,1,'') AS ProductCode, 
	          STUFF((SELECT ', ' + businessunit 
	          FROM products 
	          WHERE ProductCode = t.productcode 
	          ORDER BY BusinessUnit 
	          FOR XML PATH('')),1,1,'') AS BusinessUnits,
	          total as valuetotal
	   FROM
      (select ProductCode
       ,COUNT(*) AS cnt
       ,SUM(ForecastValue) AS total
       from products
	   group by ProductCode
       having COUNT(*) <= 3
       AND SUM(ForecastValue) <=3365000) t

The raw data is like this:


productcode    businessunit     forecastvalue
============   =============    =============
1               BU1             20
1               BU1             30
1               BU2             10
2               BU5             30
2               BU8             50
3               BU3             55

And the code returns the data like this:


productcode    businessunit     valuetotal
============   =============    =============
1, 1, 1        BU1, BU1, BU2    60
2, 2           BU5, BU8         80
3              BU3              55

Which is fine but I cannot use STUFF function in MS Access as this statement will be used there. I figured I need to try and replicate the STUFF function by using various string manipulation functions in MS SQL and have came up with this:


STUFF = Left(ProductCode, LEN(ProductCode)) + ', ' + Right(ProductCode, Len(ProductCode) - (LEN(ProductCode)))
STUFF = Left(BusinessUnit, LEN(BusinessUnit)) + ', ' + Right(BusinessUnit, Len(BusinessUnit) - (LEN(BusinessUnit)))

But I cannot figure out how to use this in my select query instead of STUFF, I have a lot of problems understanding the syntax/joins etc I need to use if any.

Can anyone point me in the right direction?

G

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-21 : 11:44:37

[code]
select SUBSTRING(t.u,2,LEN(t.u)) AS ProductCode,
SUBSTRING(m.n,2,LEN(m.n)) AS BusinessUnits,
total as valuetotal
FROM
(select ProductCode
,COUNT(*) AS cnt
,SUM(ForecastValue) AS total
from products
group by ProductCode
having COUNT(*) <= 3
AND SUM(ForecastValue) <=3365000) t
CROSS APPLY (SELECT ', ' + ProductCode
FROM products
WHERE ProductCode = t.productcode
ORDER BY ProductCode
FOR XML PATH(''))t(u)
CROSS APPLY (SELECT ', ' + businessunit
FROM products
WHERE ProductCode = t.productcode
ORDER BY BusinessUnit
FOR XML PATH('')) m(n)

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2011-09-21 : 12:26:03

Thank you again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-21 : 12:28:01

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources