Query Help - 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 2012 Forums

Transact-SQL (2012)

Query Help

Author

Topic

keka3309
Starting Member

11 Posts

Posted - 2013-04-03 : 09:20:51

Hi All,

I go the data in the below format

A1 B1 C1
V0 13.5 13.5/13.9/14/16
V0 13.9 13.5/13.9/14/16
V0 14.0 13.5/13.9/14/16
V0 16.0 13.5/13.9/14/16
V1 15.0 10/12/15/16
V1 16.0 10/12/15/16
V1 12.0 10/12/15/16
V1 10.0 10/12/15/16

A1 and B1 are the columns wich i have in my table. I have to derive a new column C1 which should be grouped by A1 but the values in B1 need to concatenated instead of sum. Is this Possible.

Any help is much appreciated

Thanks

KEKA

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-03 : 10:04:51

[code]select
a.A1,
a.B1,
STUFF((select '/' as [text()],B1 as [text()] from Table1 b
where b.A1 = a.A1
for xml path('')),1,1,'') as C1
from
Table1 a[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-03 : 13:55:24

quote:

Originally posted by James K

select
	a.A1,
	a.B1,
	STUFF((select '/'  + CAST(B1 AS varchar(10)) as [text()] from Table1 b
	where b.A1 = a.A1
	for xml path('')),1,1,'') as C1
from
	Table1 a

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-03 : 14:21:07

Both should be functionally (and for the most part performance-wise) the same. I am guessing though because I don't know whether extracting the inner text from the XML is more (or less) expensive than casting the float/decimal to varchar(10).

keka3309
Starting Member

11 Posts

Posted - 2013-04-04 : 03:47:52

Thanks James and Vishak for your response

with out Cast My output is 0.000000000000000e+000,1.300000000000000e+001

after applying Cast my output is 0,13

Thanks
Keka

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-04 : 05:44:42

quote:
Originally posted by keka3309

Thanks James and Vishak for your response

with out Cast My output is 0.000000000000000e+000,1.300000000000000e+001

after applying Cast my output is 0,13

Thanks
Keka

yep.
that was why i did the cast

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources