concatenation of data in a single field?.......

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 2000 Forums

Transact-SQL (2000)

concatenation of data in a single field?.......

Author

Topic

wormz666
Posting Yak Master

110 Posts

Posted - 2008-12-23 : 01:09:16

itemmas
-------------
id idesc classification
1 rice prime
2 salt prime
3 xtype vehicle
4 dtype vehicle

here what i want to do in my query>....
but im having difficuties with this....

classification ids
---------------------
prime 1,2
vehicle 3,4

thank you in advance....
im using ms sql 2000

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-23 : 06:05:43

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-05 : 04:29:22

select classification,stuff((select ',' + cast(id as varchar(12)) from itemmas where classification = e.classification for xml path('')),1,1,'') from itemmas e

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-01-05 : 07:32:59

quote:
Originally posted by bklr

select classification,stuff((select ',' + cast(id as varchar(12)) from itemmas where classification = e.classification for xml path('')),1,1,'') from itemmas e

This wont work in SQL Server 2000 which OP is using

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 09:13:26

[code]
CREATE FUNCTION GetRelatedIDs
(
@classification varchar(1000)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @IDList varchar(1000)

SELECT @IDList =COALESCE(@IDList ,'')+','
FROM itemmas
WHERE classification=@classification

RETURN LEFT(@IDList,LEN(@IDList)-1)
END

and invoke like this

SELECT DISTINCT classification,dbo.GetRelatedIDs(classification)
FROM itemmas
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources