how to solve it in sql2000? - 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 2000 Forums

Transact-SQL (2000)

how to solve it in sql2000?

Author

Topic

boyfriendalex
Starting Member

15 Posts

Posted - 2008-07-22 : 01:53:02

Hi.
I want the following result:
table1 table2
No, Name, NO Subject
1 leehong 1 math
2 tanzo 1 com
3 alex 2 biology
2 business
3 marketing

result:
1 leehong math,com
2 tanzo biology,business
3 alex marketing
i did it already in sql2005
select name,(select STUFF((select ',' + t2.subject AS
[text()] from table2 t2 where t2.no = t1.no
) ,1,2,'')) AS label
but it doesn't work in sql2000
Anyone help?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-22 : 02:26:16

the sub query may return more than 1 rows.

"i did it already in sql2005"
Yes, but not as it is. in 2005, you would have the FOR XML in there

For 2000 use this method
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx

KH
[spoiler]Time is always against us[/spoiler]

boyfriendalex
Starting Member

15 Posts

Posted - 2008-07-22 : 02:46:33

quote:
Originally posted by khtan

the sub query may return more than 1 rows.

"i did it already in sql2005"
Yes, but not as it is. in 2005, you would have the FOR XML in there

For 2000 use this method
http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx

KH
[spoiler]Time is always against us[/spoiler]

yeah i know already missing XML, I only wrote the code to explain my problem.... but it doesn't work error:
Incorrect syntax near the keyword 'FOR'.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-22 : 02:49:38

take a look at the link i posted

KH
[spoiler]Time is always against us[/spoiler]

boyfriendalex
Starting Member

15 Posts

Posted - 2008-07-22 : 02:58:10

quote:
Originally posted by khtan

take a look at the link i posted

KH
[spoiler]Time is always against us[/spoiler]

that is in sql2000,isn't it?
you see the following code:
SELECT t.BOMID,( SELECT STUFF(
(SELECT ', ' + t1.ITEMNAME AS [ text() ] FROM #TempList8 t1 WHERE t1.BOMID COLLATE DATABASE_DEFAULT = t.BOMID COLLATE
DATABASE_DEFAULT
ORDER BY t1.BOMID
FOR XML PATH('')),1,2,'')) AS LABEL
what part is incorrent?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-22 : 03:00:48

SQL 2000 does not support the FOR XML PATH

KH
[spoiler]Time is always against us[/spoiler]

boyfriendalex
Starting Member

15 Posts

Posted - 2008-07-22 : 03:04:36