Concatenating using FOR XML - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Concatenating using FOR XML

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-26 : 16:50:40

Greeting
For following rows how can I use FOR XML to collapse the result grouped by Col5 and then Col3 and concatenate Col1 and sum Col2?


Col1   Col2     Col3    Col4            Col5
XS57   4.00	818320	2009-03-27 	47985                   
XS57   1.00	818240	2009-03-26 	47985                   
XS57   1.00	818240	2009-03-26 	47985                   
XS58   5.00	819124	2009-03-26 	47985                   
XS59   1.00	818242	2009-03-27 	47985                   
XS59   1.00	818242	2009-03-27 	47985                   
XS76   1.00	818236	2009-03-27 	47985                   
XS76   1.00	818236	2009-03-27 	47985                   
XS76   1.00	818236	2009-03-27 	47985                   
XS76   1.00	818236	2009-03-27 	47985

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-26 : 16:54:43

For the above sample data, what is the expected output?

E 12°55'05.63"
N 56°04'39.26"

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-26 : 16:58:33

Take for example the following rows


XS76   1.00	818236	2009-03-27 	47985                   
XS77   1.00	818236	2009-03-27 	47985                   
XS78   1.00	818236	2009-03-27 	47985                   
XS79   1.00	818236	2009-03-27 	47985

I would like to get 1 row


XS76,XS77,XS78,XS79,   4.00	818236	2009-03-27 	47985

Thanks Peso

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-26 : 17:01:45

And what about Col4? What to do if there are different dates?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-26 : 17:03:01

For concatenation using FOR XML, see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

E 12°55'05.63"
N 56°04'39.26"

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-26 : 17:04:19

if there are different dates then I want to show that as a new row, same for Col3, Col5
Thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-26 : 17:06:47

So you want to
1. Group by Col3, Col4 and Col5?
2. Concatenate Col1?
3. Sum Col2?

E 12°55'05.63"
N 56°04'39.26"

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-26 : 17:20:27

Si senor! I think I alsmot got it with that FOR XMl link you sent me. The grouping is messing me up

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-26 : 17:24:21

Can you try this...

declare @t table (col1 varchar(10), col2 decimal(10,2), col3 int, col4 varchar(10), col5 int)
insert @t
select 'XS76', 1.00, 818236, '2009-03-27', 47985 union all
select 'XS77', 1.00, 818236, '2009-03-27', 47985 union all
select 'XS78', 1.00, 818236, '2009-03-27', 47985 union all
select 'XS79', 1.00, 818236, '2009-03-27', 47985 union all
select 'XS80', 1.00, 818236, '2009-03-28', 47985

SELECT STUFF((SELECT ',' + [col1] FROM @t WHERE col3 = t.col3 AND col4 = t.col4 AND col5 = t.col5 FOR XML PATH('')),1,1,'')as [newcol],sum(t.col2),t.col3,t.col4,t.col5
FROM @t t
GROUP BY col3,col4,col5

Result
-------
XS76,XS77,XS78,XS79 4.00 818236 2009-03-27 47985
XS80 1.00 818236 2009-03-28 47985

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-26 : 17:26:20

Kaching! Thanks Vijay

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-26 : 17:31:17

np..just returning the favor

.

You still haven't forgotten those crazy JOINs.. have you? lol.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources