Array from Table - 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

Other Forums

SQL Server 6.5 \ SQL Server 7.0

Array from Table

Author

Topic

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-03-28 : 05:07:11

Dear all,
How to get a single column values(contains Multile rows) list values as a string of array from a table

Thanks in advance
Dana

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-28 : 05:11:45

[code]
declare @str varchar(1000)
select @str = ''
select @str = @str + col1 + ','
from table
[/code]

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-03-28 : 05:16:25

Dear Khtan,
I tried like this
declare @str varchar(1000)
select @str = ''
select @str = @str + group + ',' from activitydet

and I got error as
Invalid operator for data type. Operator equals add, type equals ntext.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-28 : 05:28:14

quote:
Originally posted by danasegarane76

Dear Khtan,
I tried like this
declare @str nvarchar(4000)
~~select @str = ''~~
select @str = coalesce(@str + ',', '') + convert(nvarchar(50), group) from activitydet

and I got error as
Invalid operator for data type. Operator equals add, type equals ntext.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-03-28 : 05:46:05

Dear Harsh,
I am using VB.How to get the contents in a string.I tried but it returns as command executed succesfully

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-28 : 06:01:18

quote:
Originally posted by danasegarane76

Dear Khtan,
I tried like this
declare @str nvarchar(4000)
~~select @str = ''~~
select @str = coalesce(@str + ',', '') + convert(nvarchar(50), group) from activitydet

Select @str as arr

and I got error as
Invalid operator for data type. Operator equals add, type equals ntext.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-03-28 : 06:03:20

Sorry I am not able to get you

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-28 : 06:16:05

Create stored proc and add given code to it. Also you can return value of @str variable as an output parameter instead of final SELECT statement. In your VB code, you can then get the csv list of values from this output parameter.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-03-28 : 06:19:06

Thanks Harsh :)

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-03-30 : 02:12:11

Dear Harsh,
Can you Explain the Coalsece Function.Because it not clear in SQLMSDN

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-30 : 03:48:08

Try this sample code and see if you understand what is happening:

select coalesce('a', 'b', null)
select coalesce('a', null, 'b')
select coalesce(null, 'b', 'a')

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-03-30 : 05:23:24

Dear Harsh,
Thanks for the reply.What about the cancat part ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-30 : 05:41:26

The initial solution which i gave is equivalent to following pseudocode:

for each row in table
{
  if @str is null
     @str = '' + column //execute for the first round of loop
  else
     @str = @str + ',' + column
}

I think this should be pretty clear now.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-03-30 : 05:49:22

Thanks Harsh,
How to retrun the values like in this format '1','2','3'

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-30 : 05:56:20

[code]declare @str nvarchar(4000)
select @str = coalesce(@str + ',', '') + '''' + convert(nvarchar(50), group) + '''' from activitydet
Select @str as arr[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-30 : 06:17:31

Are you using VB?

Use .GetString function and set comma as row delimiter (instead of vbCrLf), and CHR(39) as column delimiter.

sOptions = CHR(39) & rs.GetString(",", CHR(39)) & CHR(39)

Peter Larsson
Helsingborg, Sweden

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-03-30 : 06:33:46

Dear Harsh,
I have another issue.The return values size cannot be declared.Because this will varry from size to size.Is there any other method avl to solve this issue

Thanks in Advance
Dana

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-03-30 : 06:35:23

Dear Peter,
I am using Vb.net :)
Is there any method avl in vb.net

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-30 : 06:46:09

In SQL approach anyway, you can't store more than 8000 characters if you are using varchar as data type for the variable.

I don't suppose there is any equivalent of GetString() of classic ADO in ADO.Net...Arghhh!

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

danasegarane76
Posting Yak Master

242 Posts

Posted - 2007-03-30 : 06:55:07

Dear Harsha,
I have another issue.If the row value has a value like ' ',than i want to omit this entry.But now this returns as 'a',',','c'.How can i solve this

Sorry to disturb u

Dana

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-30 : 06:57:45

A simple WHERE?
WHERE COALESCE(Col1, '') <> ''

Peter Larsson
Helsingborg, Sweden

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources