multiple column values into a single value

hi all,
I am trying to take all the values from a single varchar column and to inset
them into a variable so that they are seperated with the '-' sign.
I am trying to avoid cursors.
by the way- all the rows have a column with the same value in the table
for example:
create table test (col1 int, col2 varchar(20))
insert test values(1,'aaa')
insert test values(1,'bbb')
insert test values(1,'ccc')
the result sould look like this 'aaa-bbb-ccc'
thaks,
alonA cursor/iterative solution is best suited for this.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"alon" <alon@.discussions.microsoft.com> wrote in message
news:BEB95AAB-5C04-4466-B940-B00AAAAE0DE3@.microsoft.com...
> hi all,
> I am trying to take all the values from a single varchar column and to
> inset
> them into a variable so that they are seperated with the '-' sign.
> I am trying to avoid cursors.
> by the way- all the rows have a column with the same value in the table
> for example:
> create table test (col1 int, col2 varchar(20))
> insert test values(1,'aaa')
> insert test values(1,'bbb')
> insert test values(1,'ccc')
> the result sould look like this 'aaa-bbb-ccc'
> thaks,
> alon|||create table #test (col1 int, col2 varchar(20))
insert #test values(1,'aaa')
insert #test values(1,'bbb')
insert #test values(1,'ccc')
DECLARE @.concat VARCHAR(100)
SET @.concat=''
SELECT
CASE WHEN @.concat='' THEN '' ELSE '-' END + col2
FROM #test
DROP TABLE #Test
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"alon" <alon@.discussions.microsoft.com> wrote in message
news:BEB95AAB-5C04-4466-B940-B00AAAAE0DE3@.microsoft.com...
> hi all,
> I am trying to take all the values from a single varchar column and to
> inset
> them into a variable so that they are seperated with the '-' sign.
> I am trying to avoid cursors.
> by the way- all the rows have a column with the same value in the table
> for example:
> create table test (col1 int, col2 varchar(20))
> insert test values(1,'aaa')
> insert test values(1,'bbb')
> insert test values(1,'ccc')
> the result sould look like this 'aaa-bbb-ccc'
> thaks,
> alon|||Maybe this can help:
http://milambda.blogspot.com/2005/0...s-as-array.html
Of course you'll need to change it to meet your specific needs.
ML|||So you want to destroy First Normal Form (1NF) with a proprieetary
kludge? In a tiered archtiecture, display is done inthe front end and
not in the database. This is far more basic than just SQL programming.|||http://www.aspfaq.com/2529
"alon" <alon@.discussions.microsoft.com> wrote in message
news:BEB95AAB-5C04-4466-B940-B00AAAAE0DE3@.microsoft.com...
> hi all,
> I am trying to take all the values from a single varchar column and to
> inset
> them into a variable so that they are seperated with the '-' sign.
> I am trying to avoid cursors.
> by the way- all the rows have a column with the same value in the table
> for example:
> create table test (col1 int, col2 varchar(20))
> insert test values(1,'aaa')
> insert test values(1,'bbb')
> insert test values(1,'ccc')
> the result sould look like this 'aaa-bbb-ccc'
> thaks,
> alon

Multipe text file import to single table

All,
I need to import the contents of 1000+ text files into a single table
in SQL Server 7.0. The files are plain ascii, fields seperated by
"/".
Does anyone have ideas of how this can be achieved?
Regards,
A.Multiple BULK INSERT commands?
"Adrian Smith" <adriancsi@.aol.com> wrote in message
news:eb79b8a3.0311181012.76211750@.posting.google.com...
> All,
> I need to import the contents of 1000+ text files into a single table
> in SQL Server 7.0. The files are plain ascii, fields seperated by
> "/".
> Does anyone have ideas of how this can be achieved?
> Regards,
> A.|||Do they all have exactly the same format?
If so it may be easier to concatenate them into one single file... From a
DOS prompt you could use:
TYPE *.TXT > BIGFILE.TXT
(Assuming they all have .TXT extension and that they're the only files in
the folder with .TXT extension, of course)
From there you could then use either BULK COPY or BCP.
If for some reason you don't want to or can't concatenate them, you could
write a batch file that would call BCP, replacing the filename parameter in
the batch file with %1, then call the batch file for each file using (again
from a DOS prompt):
FOR %i IN (DIR *.TXT) DO INSERT.BAT %i
Or you could write the entire thing in a SQL script using a cursor and a
call to xp_cmdshell to get a list of the files... IMO the DOS methods are a
lot easier.
"Adrian Smith" <adriancsi@.aol.com> wrote in message
news:eb79b8a3.0311181012.76211750@.posting.google.com...
> All,
> I need to import the contents of 1000+ text files into a single table
> in SQL Server 7.0. The files are plain ascii, fields seperated by
> "/".
> Does anyone have ideas of how this can be achieved?
> Regards,
> A.