How to select different values to a one string

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)

How to select different values to a one string

Author

Topic

yozhik
Starting Member

8 Posts

Posted - 2008-11-25 : 04:57:40

Hi,

Please help me to solve my problem. I have a table like this:
StringID
SetID
ProductID
EngineD
ParameterID
ResultValue
For each set there are several Products, for each Product there are several Engines, for each Engine there are several Parameters, for each Parameter there is a unique ResultValue.
I need to select for each Product all ResultValues in the same string. Is it possible at all?
Thank you in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 05:03:49

same string? do you mean as comma seperated?

yozhik
Starting Member

8 Posts

Posted - 2008-11-25 : 05:05:30

Exactly

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 05:05:57

if yes, use below

SELECT t.ProductID,LEFT(vl.ValList,LEN(vl.ValList)-1) AS ResultValues
FROM (SELECT DISTINCT ProductID FROM YourTable)t
CROSS APPLY (SELECT ResultValue + ','
             FROM Yourtable
             WHERE ProductID=t.ProductID
             FOR XML PATH(''))vl(ValList)

yozhik
Starting Member

8 Posts

Posted - 2008-11-25 : 05:11:41

I tried this statement and got a message that "CROSS APPLY is not supported". Do you have other ideas?
Thanks a lot.

yozhik
Starting Member

8 Posts

Posted - 2008-11-25 : 05:12:11

I tried this statement and got a message that "CROSS APPLY is not supported". Do you have other ideas?
Thanks a lot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 05:14:16

quote:
Originally posted by yozhik

I tried this statement and got a message that "CROSS APPLY is not supported". Do you have other ideas?
Thanks a lot.

Are you using sql 2005?

yozhik
Starting Member

8 Posts

Posted - 2008-11-25 : 05:15:38

yes

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 05:17:36

then check if compatibility level used is 90. use below

EXEC sp_dbcmptlevel 'your database name here'

yozhik
Starting Member

8 Posts

Posted - 2008-11-25 : 05:20:07

it's 90

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 05:49:57

quote:
Originally posted by yozhik

it's 90

t

then this should work.
which client tool you're using?

yozhik
Starting Member

8 Posts

Posted - 2008-11-25 : 06:11:28

Query Analyzer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 06:41:34

quote:
Originally posted by yozhik

Query Analyzer

its working for me anyways.
if not try below solution

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=114993

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 06:59:28

Use SSMS 2005 query window and try again.

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

yozhik
Starting Member

8 Posts

Posted - 2008-11-25 : 09:13:10

I'll try again and will check the reference. Many thanks for your efforts.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-25 : 11:50:00

You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources