commas in col

How do I display comma separated values in a column using column A of tbl MMM

Say Col B of tbl NNN = Col C of Tbl MMM

I'm displaying values in tbl NNN & col A from MMM.
I have rows like
xxx yyy 1 zzz
xxx yyy 2 zzz
xxx yyy 3 zzz
but I want
xxx yyy 1,2,3 zzz

What's the SQL (MS SQL Server)?What's the MS SQL Server SQL for making rows

xxx yyy 1 zzz
xxx yyy 2 zzz
xxx yyy 3 zzz

into a row
xxx yyy 1,2,3 zzz

Comma Seprated Text

Hi all,
is it possible to print the results in comma seprated text.
i.e
col1
--
Jhon
Henery
should be displayed as Jhon,Henery
Is possble to display the results in single select statement?.
RegardsPreveen
Yes , it is. But I would recommend you doing such things on the client.
CREATE TABLE #Test
(
col VARCHAR(20) NOT NULL
)
INSERT INTO #Test VALUES ('Bill')
INSERT INTO #Test VALUES ('Clinton')
DECLARE @.p VARCHAR(50)
SET @.p=''
SELECT @.p=@.p+col+',' FROM #Test
SELECT LEFT(@.p,LEN(@.P)-1)
"Praveen" <apveen@.gmail.com> wrote in message
news:1127390507.779755.291720@.g43g2000cwa.googlegroups.com...
> Hi all,
> is it possible to print the results in comma seprated text.
> i.e
> col1
> --
> Jhon
> Henery
> should be displayed as Jhon,Henery
> Is possble to display the results in single select statement?.
> Regards
>|||It's possible... but you're better off doing this client side.
In ASP you can use GetString()
"Praveen" <apveen@.gmail.com> wrote in message
news:1127390507.779755.291720@.g43g2000cwa.googlegroups.com...
> Hi all,
> is it possible to print the results in comma seprated text.
> i.e
> col1
> --
> Jhon
> Henery
> should be displayed as Jhon,Henery
> Is possble to display the results in single select statement?.
> Regards
>|||A SELECT statement doesn't control how text is printed. A SELECT statement
just returns a result set. How you print or display that result is determine
d
by your client application.
In your case, if you do this in SQL how do you expect to determine which
value to display first and second? Here's one possibility:
SELECT MAX(col1)+','+MIN(col1)
FROM some_table ;
David Portas
SQL Server MVP
--

Comma seperated IDS in SELECT query with IN.

How can I use list of comma separated IDS in SELECT query with IN.
DECLARE @.TaskID varchar(200)
SET @.TaskID = '(30,32)'
SELECT DISTINCT UserID
FROM tbl_UserTask
WHERE TaskID IN @.TaskIDI do not want to use Dynamic SQL|||Look at Dejan's example
IF OBJECT_ID('dbo.TsqlSplit') IS NOT NULL
DROP FUNCTION dbo.TsqlSplit
GO
CREATE FUNCTION dbo.TsqlSplit
(@.List As varchar(8000))
RETURNS @.Items table (Item varchar(8000) Not Null)
AS
BEGIN
DECLARE @.Item As varchar(8000), @.Pos As int
WHILE DATALENGTH(@.List)>0
BEGIN
SET @.Pos=CHARINDEX(',',@.List)
IF @.Pos=0 SET @.Pos=DATALENGTH(@.List)+1
SET @.Item = LTRIM(RTRIM(LEFT(@.List,@.Pos-1)))
IF @.Item<>'' INSERT INTO @.Items SELECT @.Item
SET @.List=SUBSTRING(@.List,@.Pos+DATALENGTH(',
'),8000)
END
RETURN
END
GO
/* Usage example */
SELECT t1.*
FROM TsqlSplit('10428,10429') AS t1
declare @.inList varchar(50)
set @.inList='10428,10429'
select od.* from [order details] od
INNER JOIN
(SELECT Item
FROM dbo.TsqlSplit(@.InList)) As t
ON od.orderid = t.Item
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1140690585.836794.90030@.g14g2000cwa.googlegroups.com...
>I do not want to use Dynamic SQL
>|||Look at this function by Dejan Sarka:
http://solidqualitylearning.com/blo.../10/22/200.aspx
ML
http://milambda.blogspot.com/|||Thanks Uri,
It solved my problem.|||http://www.aspfaq.com/2248
"Adarsh" <shahadarsh@.gmail.com> wrote in message
news:1140690353.281661.41550@.v46g2000cwv.googlegroups.com...
> How can I use list of comma separated IDS in SELECT query with IN.
> DECLARE @.TaskID varchar(200)
> SET @.TaskID = '(30,32)'
> SELECT DISTINCT UserID
> FROM tbl_UserTask
> WHERE TaskID IN @.TaskID
>|||or
DECLARE @.TaskID varchar(200)
SET @.TaskID = '30,32'
SELECT DISTINCT UserID
FROM tbl_UserTask
WHERE ','+@.TaskID+',' like '%,'+cast(TaskID as varchar)+',%'
Madhivanan

comma separated values to stored procedures

Hi All,

i hv created a sp as

Create proc P @.iClientid varchar (100)
as
Begin
select * from clients where CONVERT(VACHAR(100),iClientid) in(
@.iclientid)
end

where iclientid = int data type in the clients table.
now if i pass @.iclientid as @.iclientid = '49,12,112'

but this statement throws an conversion error ( int to char error).

is there any way to fetch records from a select statement using a
string?

Thanks in Advance.shark wrote:

Quote:

Originally Posted by

Hi All,
>
i hv created a sp as
>
>
Create proc P @.iClientid varchar (100)
as
Begin
select * from clients where CONVERT(VACHAR(100),iClientid) in(
@.iclientid)

This does not work as you probably want. You get a selection criterium
with a single string @.iClientid. You could have written

select * from clients where CONVERT(VACHAR(100),iClientid) = @.iclientid

Quote:

Originally Posted by

end
>
where iclientid = int data type in the clients table.
now if i pass @.iclientid as @.iclientid = '49,12,112'
>
but this statement throws an conversion error ( int to char error).
>
is there any way to fetch records from a select statement using a
string?

If you need to pass in your id's the way you do, you can do it with
dynamic SQL, i.e. create a SQL statement and EXEC it.

robert|||See Erland's article on the subject:
http://www.sommarskog.se/arrays-in-sql.html
--
Hope this helps.

Dan Guzman
SQL Server MVP

"shark" <xavier.sharon@.gmail.comwrote in message
news:1152022816.209445.326230@.p79g2000cwp.googlegr oups.com...

Quote:

Originally Posted by

Hi All,
>
i hv created a sp as
>
>
Create proc P @.iClientid varchar (100)
as
Begin
select * from clients where CONVERT(VACHAR(100),iClientid) in(
@.iclientid)
end
>
where iclientid = int data type in the clients table.
now if i pass @.iclientid as @.iclientid = '49,12,112'
>
but this statement throws an conversion error ( int to char error).
>
is there any way to fetch records from a select statement using a
string?
>
Thanks in Advance.
>

|||Where ','+@.iClientid+',' like '%,+cast(iClientid as varchar(20))+',%'

Madhivanan

Dan Guzman wrote:

Quote:

Originally Posted by

See Erland's article on the subject:
http://www.sommarskog.se/arrays-in-sql.html
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"shark" <xavier.sharon@.gmail.comwrote in message
news:1152022816.209445.326230@.p79g2000cwp.googlegr oups.com...

Quote:

Originally Posted by

Hi All,

i hv created a sp as

Create proc P @.iClientid varchar (100)
as
Begin
select * from clients where CONVERT(VACHAR(100),iClientid) in(
@.iclientid)
end

where iclientid = int data type in the clients table.
now if i pass @.iclientid as @.iclientid = '49,12,112'

but this statement throws an conversion error ( int to char error).

is there any way to fetch records from a select statement using a
string?

Thanks in Advance.

Comma Separated Values

I need deptno and against/beside it comma separated ename's for join dept.deptno=emp.deptno in a single query.

I require deptno=10 against/beside ename=scott,blake,richard,moxon like this.

can u try this ?

select * from dept,emp where dept.deptno = emp.deptno and emp.ename in ('scott','blake','richard','moxon')

comma separated value

Suppose I have a table like this

code Value
1 a
1 a
1 b
2 c
2 c
1 d
2 g

Now my require ment that I want a distinct comma separated report about these data.Means for code 1 I need a comma separated distinct values.In this case it should be a,b,d
My output should be like this
1 a,b,d
2 c,g
Can anybody help me I can I do this with the help of a cursor or any other way?
SubhasishOriginally posted by subhasishray
Suppose I have a table like this

code Value
1 a
1 a
1 b
2 c
2 c
1 d
2 g

Now my require ment that I want a distinct comma separated report about these data.Means for code 1 I need a comma separated distinct values.In this case it should be a,b,d
My output should be like this
1 a,b,d
2 c,g
Can anybody help me I can I do this with the help of a cursor or any other way?
Subhasish

there is an article explaining exactly what u are looking for :
http://www.sqlteam.com/item.asp?ItemID=256

Regards,
Harshal.

comma separated resultant column

I would like the resultant column to be comma separated. Here is sample ddl
set nocount on
go
create table z_my_tbl_del
( i int,
n char(8)
)
go
insert z_my_tbl_del values(1,'john')
insert z_my_tbl_del values(2,'mary')
insert z_my_tbl_del values(3,'luke')
insert z_my_tbl_del values(4,'trisha')
insert z_my_tbl_del values(5,'sam')
go
select * from z_my_tbl_del
go
drop table z_my_tbl_del
go
The resultant column should be all in one line as as opposed to separate
lines.
n
======
john,mary,luke,trisha,samThe right way is to do display formatting in the front end. But if you
like doing things wrong and destroying 1NF, use a cursor. Someone else
will show you a proprietary way of doing .|||See if this helps:
http://groups-beta.google.com/group...
5bf366dd9e73e
AMB
"sqlster" wrote:

> I would like the resultant column to be comma separated. Here is sample dd
l
> set nocount on
> go
> create table z_my_tbl_del
> ( i int,
> n char(8)
> )
> go
> insert z_my_tbl_del values(1,'john')
> insert z_my_tbl_del values(2,'mary')
> insert z_my_tbl_del values(3,'luke')
> insert z_my_tbl_del values(4,'trisha')
> insert z_my_tbl_del values(5,'sam')
> go
> select * from z_my_tbl_del
> go
> drop table z_my_tbl_del
> go
> The resultant column should be all in one line as as opposed to separate
> lines.
> n
> ======
> john,mary,luke,trisha,sam|||Somehow I get the feeling that comma-separated values are really *in* this
year.
These things do not belog on the data layer!
But, that's fashion - useless but neat...
Look at this:
http://milambda.blogspot.com/2005/0...s-as-array.html
And what's bad about it? One of the things is the mangled informational
value - the actual relationship between the values 'john', 'mary', 'luke',
'trisha' and 'sam' gets distorted. Are these values cummulative or
alternative? What do they have in common? Did they have anything in common
before? Can they have anything in common ever again?
Anyway, use with care...
ML|||i suggest the following :
SELECT TOP 1 z_my_tbl_del_1.n + ',' + z_my_tbl_del_2.n + ',' +
z_my_tbl_del_3.n + ',' + z_my_tbl_del_4.n + ',' + z_my_tbl_del_5.n AS nn
FROM z_my_tbl_del z_my_tbl_del_1 INNER JOIN
z_my_tbl_del z_my_tbl_del_2 ON z_my_tbl_del_1.i <>
z_my_tbl_del_2.i INNER JOIN
z_my_tbl_del z_my_tbl_del_3 ON z_my_tbl_del_2.i <>
z_my_tbl_del_3.i AND z_my_tbl_del_1.i <> z_my_tbl_del_3.i INNER JOIN
z_my_tbl_del z_my_tbl_del_4 ON z_my_tbl_del_3.i <>
z_my_tbl_del_4.i AND z_my_tbl_del_1.i <> z_my_tbl_del_4.i AND
z_my_tbl_del_2.i <> z_my_tbl_del_4.i INNER JOIN
z_my_tbl_del z_my_tbl_del_5 ON z_my_tbl_del_1.i <>
z_my_tbl_del_5.i AND z_my_tbl_del_2.i <> z_my_tbl_del_5.i AND
z_my_tbl_del_3.i <> z_my_tbl_del_5.i AND
z_my_tbl_del_4.i <> z_my_tbl_del_5.i
Try it!!!
mario
"sqlster" wrote:

> I would like the resultant column to be comma separated. Here is sample dd
l
> set nocount on
> go
> create table z_my_tbl_del
> ( i int,
> n char(8)
> )
> go
> insert z_my_tbl_del values(1,'john')
> insert z_my_tbl_del values(2,'mary')
> insert z_my_tbl_del values(3,'luke')
> insert z_my_tbl_del values(4,'trisha')
> insert z_my_tbl_del values(5,'sam')
> go
> select * from z_my_tbl_del
> go
> drop table z_my_tbl_del
> go
> The resultant column should be all in one line as as opposed to separate
> lines.
> n
> ======
> john,mary,luke,trisha,sam

Comma Separated Output with .TXT extension

I have a somewhat strange situation where I need the output of a query thrown into a file with a .txt extension but I need the data in comma separate form. Any idea how I would get this accomplished?
Thanks in Advance,
TechRickIf you use bcp (from the command-line) you can specify the full filename

- Andy Abel|||You could use DTS. The fast way would be to choose the DTS Import/Export Wizard from the menu. Choose your source, then set your destination as a text file. Third select Query as your copy type, then input your query. Set your delimitor to be commas on the next screen. Then run, about 7 clicks to do.
You can also save the package to run again or edit it in DTS.|||Originally posted by achorozy
You could use DTS. The fast way would be to choose the DTS Import/Export Wizard from the menu. Choose your source, then set your destination as a text file. Third select Query as your copy type, then input your query. Set your delimitor to be commas on the next screen. Then run, about 7 clicks to do.
You can also save the package to run again or edit it in DTS.

Thanks so much for the info. I'm giving it a go but I'm running into an error. Perhaps you or someone else might know how to fix it.

Upon running the package I get this error: "Incomplete File Format Information - File Cannot Be Opened."

I don't know if this has anything to do with it, but I had to jimmy-rig the package to build it. I am using 3 temporary tables and 4 views in my query. DTS would not let me finish the package unless the main temporary table was already created. So, instead of having the query build it I just have the query delete and recreate it at the end so it is blank.

Any additional help would be appreciated.

TechRick|||Can you post the dts package ? Also, which version of sql server are you using - including service pack ?|||Originally posted by rnealejr
Can you post the dts package ? Also, which version of sql server are you using - including service pack ?

I am running 8.00.384 SP1 I believe.

Anyway, I was able to work it out by breaking the task up into smaller steps and running some of them through the job scheduler. I just had the last step run under the DTS package so I could get the output I wanted.

I had to break it up because I always seem to be above the 3200 charater limit for my queries to run as a job. Either my queries are too long or the job scheduler doesn't give enough characters...

Thanks for your assistance.

TechRick

Comma Separated List On Single Line

Hello,

I need to create a single row of data with comma separated fields like such:

Value1, Value2, Value3, Value 4

How can I achieve this with data from a dataset? I do have some other options, but want to consider this being done within the report itself.

Thanks.

You could make an class library with a function that takes several parameters and concatenates the parameters of this method comma-separated. You can call your function in a report:

http://geekswithblogs.net/davyknuysen/archive/2007/03/26/109901.aspx

|||

Brian,

This is how i do it.. this is my sproc to acheive that

ALTERProcedure [dbo].[rpt_LoanAttriApplied]

@.PlanIdint

AS

BEGIN

Declare @.List varchar(255)

Set @.List=' '

Select @.List= @.List+', '+ los.Name

From LoansAttriApplied laa

InnerJoin LoanOptions loson laa.LoanId= los.LoanId

Where PlanId= @.PlanId

selectRight(@.List,Len(@.List)- 2)

--set @.List = Right(@.List, Len(@.List) - 1)

End

|||

Ok, thanks for both.

comma separated list into stored procedure and order of records

I'm passing a comma separated list into a stored procedure
e.g. exec usp_returnProductsFromIdArray '5,4,1,99'
The comma list is being turned into a table (see usp_IntListToTable below)
Here's the proc that returns products that are in the list
CREATE procedure usp_returnProductsFromIdArray
@.prodIdList varchar(1000) = null
as
CREATE TABLE #prodIdTable (productId BIGINT)
if (@.prodIdList is not null)
EXEC usp_IntListToTable @.prodIdList,'#prodIdTable'
select productId, productTitle from products where productId in
(select productId from #prodIdTable)
drop table #prodIdTable
This works well, except I'd like the order of records returned to follow the
order of the IDs in the comma separated list, they aren't, they are in
ascending order (due to the key on the products table)
/*
How do I simulate an array inside a stored procedure?
http://www.aspfaq.com/show.asp?id=2248
*/
CREATE PROCEDURE dbo.usp_IntListToTable
@.cslist VARCHAR(8000),
@.tablename SYSNAME AS
BEGIN
DECLARE @.spot SMALLINT, @.str VARCHAR(8000), @.sql VARCHAR(8000)
WHILE @.cslist <> ''
BEGIN
SET @.spot = CHARINDEX(',', @.cslist)
IF @.spot>0
BEGIN
SET @.str = CAST(LEFT(@.cslist, @.spot-1) AS INT)
SET @.cslist = RIGHT(@.cslist, LEN(@.cslist)-@.spot)
END
ELSE
BEGIN
SET @.str = CAST(@.cslist AS INT)
SET @.cslist = ''
END
SET @.sql = 'INSERT INTO '+@.tablename+'
VALUES('+CONVERT(VARCHAR(100),@.str)+')'
EXEC(@.sql)
END
END
GOAdd a new column to the temp table to define the sequence. Increment an
integer value in the WHILE loop and insert that into the sequence
number column. Then do:
SELECT P.productid, P.producttitle
FROM products AS P
JOIN #prodIdTable AS T
ON P.productid = T.productid
ORDER BY T.sequence_no ;
David Portas
SQL Server MVP
--|||It works
Thanks very much!!!
www.xwords.co.uk
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129194249.222211.192330@.o13g2000cwo.googlegroups.com...
> Add a new column to the temp table to define the sequence. Increment an
> integer value in the WHILE loop and insert that into the sequence
> number column. Then do:
> SELECT P.productid, P.producttitle
> FROM products AS P
> JOIN #prodIdTable AS T
> ON P.productid = T.productid
> ORDER BY T.sequence_no ;
> --
> David Portas
> SQL Server MVP
> --
>

Comma operator in FROM clause - what is this?

I know how it functions, but what does this mean exactly, and where can
I find it in the books online?
SELECT *
FROM Table1, Table2
What is the name of that Comma's function?
Thanks,
JeremyMost often, this is what we consider an old style join, which you forgot to
restrict. Old style
join, as in
SELECT ...
FROM titles AS t, publishers AS p
WHERE t.pub_id = p.pub_id
Above is the same query as:
SELECT ...
FROM titles AS t
JOIN publishers AS p ON t.pub_id = p.pub_id
No, if you remove the WHERE clause from the first query, you get all rows fr
om titles, combined with
all rows from publihers. In most cases, a meaningless result. We call this c
artesian product, cross
join or unrestricted join. You can accomplish this using the new join syntax
as:
SELECT ...
FROM titles AS t CROSS JOIN publishers AS p
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jeremy Cowles" <jeremy.cowles@.gmail.com> wrote in message
news:1134662287.068773.293050@.g49g2000cwa.googlegroups.com...
>I know how it functions, but what does this mean exactly, and where can
> I find it in the books online?
> SELECT *
> FROM Table1, Table2
>
> What is the name of that Comma's function?
> Thanks,
> Jeremy
>|||The comma is simply a delimiter. In your from clause you can list all the
tables/views/in line views you are accessing, seperated by commas, then
define your join criteria in the where clause. If you have no join criteria
then the result is a cartesion product (every row in table 1 is joined with
every row in table 2).
"Jeremy Cowles" <jeremy.cowles@.gmail.com> wrote in message
news:1134662287.068773.293050@.g49g2000cwa.googlegroups.com...
> I know how it functions, but what does this mean exactly, and where can
> I find it in the books online?
> SELECT *
> FROM Table1, Table2
>
> What is the name of that Comma's function?
> Thanks,
> Jeremy
>|||Spanx!

Comma delisted items

I have an application that stores user profiles. One of the profile fields
allows for comma-separated items. Each item, when presented in a browser,
will allow for the user to click on and search the database for other users
with the same item in their profile.
Now, would it be better if instead of storing the items in one field as
"AAAA,BBBB,CCCC,DDDD", I store them in a separate table like this?
UserID Item
1111 AAAA
1112 CCCC
1113 BBBB
1114 AAAA
1115 DDDD
What are your thoughts?
Hi
See Anith's example
SELECT IDENTITY(INT) "n" INTO Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2
GO
DECLARE @.Ids VARCHAR(200)
SET @.Ids = '5,33,229,1,22'
SELECT SUBSTRING(@.Ids, n, CHARINDEX(',', @.Ids + ',', n) - n)
from numbers where substring(','+@.Ids,n,1)=','
AND n < LEN(@.Ids) + 1
GO
drop table Numbers
"Shabam" <blislecp@.hotmail.com> wrote in message
news:TO6dnaZLK6YNJ_bcRVn-iA@.adelphia.com...
> I have an application that stores user profiles. One of the profile
fields
> allows for comma-separated items. Each item, when presented in a browser,
> will allow for the user to click on and search the database for other
users
> with the same item in their profile.
> Now, would it be better if instead of storing the items in one field as
> "AAAA,BBBB,CCCC,DDDD", I store them in a separate table like this?
> UserID Item
> 1111 AAAA
> 1112 CCCC
> 1113 BBBB
> 1114 AAAA
> 1115 DDDD
> What are your thoughts?
>
>
>
|||Yes, it would be much better to do as you describe.
David Portas
SQL Server MVP
|||> Yes, it would be much better to do as you describe.
I was told that searches would be much slower as a result, since it would
require a join between 2 tables. Basically right now, there's a USER table
and all of a user's profile data is stored there. A few of the fields are
comma-delisted ones. The programmer is saying that by moving them to
separate tables, that it would slow down searches due to having to use join
statements.
What are your thoughts on this?

Comma delisted items

I have an application that stores user profiles. One of the profile fields
allows for comma-separated items. Each item, when presented in a browser,
will allow for the user to click on and search the database for other users
with the same item in their profile.
Now, would it be better if instead of storing the items in one field as
"AAAA,BBBB,CCCC,DDDD", I store them in a separate table like this?
UserID Item
1111 AAAA
1112 CCCC
1113 BBBB
1114 AAAA
1115 DDDD
What are your thoughts?Hi
See Anith's example
SELECT IDENTITY(INT) "n" INTO Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2
GO
DECLARE @.Ids VARCHAR(200)
SET @.Ids = '5,33,229,1,22'
SELECT SUBSTRING(@.Ids, n, CHARINDEX(',', @.Ids + ',', n) - n)
from numbers where substring(','+@.Ids,n,1)=','
AND n < LEN(@.Ids) + 1
GO
drop table Numbers
"Shabam" <blislecp@.hotmail.com> wrote in message
news:TO6dnaZLK6YNJ_bcRVn-iA@.adelphia.com...
> I have an application that stores user profiles. One of the profile
fields
> allows for comma-separated items. Each item, when presented in a browser,
> will allow for the user to click on and search the database for other
users
> with the same item in their profile.
> Now, would it be better if instead of storing the items in one field as
> "AAAA,BBBB,CCCC,DDDD", I store them in a separate table like this?
> UserID Item
> 1111 AAAA
> 1112 CCCC
> 1113 BBBB
> 1114 AAAA
> 1115 DDDD
> What are your thoughts?
>
>
>|||Yes, it would be much better to do as you describe.
David Portas
SQL Server MVP
--|||> Yes, it would be much better to do as you describe.
I was told that searches would be much slower as a result, since it would
require a join between 2 tables. Basically right now, there's a USER table
and all of a user's profile data is stored there. A few of the fields are
comma-delisted ones. The programmer is saying that by moving them to
separate tables, that it would slow down searches due to having to use join
statements.
What are your thoughts on this?

Comma delisted items

I have an application that stores user profiles. One of the profile fields
allows for comma-separated items. Each item, when presented in a browser,
will allow for the user to click on and search the database for other users
with the same item in their profile.
Now, would it be better if instead of storing the items in one field as
"AAAA,BBBB,CCCC,DDDD", I store them in a separate table like this?
UserID Item
1111 AAAA
1112 CCCC
1113 BBBB
1114 AAAA
1115 DDDD
What are your thoughts?Hi
See Anith's example
SELECT IDENTITY(INT) "n" INTO Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2
GO
DECLARE @.Ids VARCHAR(200)
SET @.Ids = '5,33,229,1,22'
SELECT SUBSTRING(@.Ids, n, CHARINDEX(',', @.Ids + ',', n) - n)
from numbers where substring(','+@.Ids,n,1)=','
AND n < LEN(@.Ids) + 1
GO
drop table Numbers
"Shabam" <blislecp@.hotmail.com> wrote in message
news:TO6dnaZLK6YNJ_bcRVn-iA@.adelphia.com...
> I have an application that stores user profiles. One of the profile
fields
> allows for comma-separated items. Each item, when presented in a browser,
> will allow for the user to click on and search the database for other
users
> with the same item in their profile.
> Now, would it be better if instead of storing the items in one field as
> "AAAA,BBBB,CCCC,DDDD", I store them in a separate table like this?
> UserID Item
> 1111 AAAA
> 1112 CCCC
> 1113 BBBB
> 1114 AAAA
> 1115 DDDD
> What are your thoughts?
>
>
>|||Yes, it would be much better to do as you describe.
--
David Portas
SQL Server MVP
--|||> Yes, it would be much better to do as you describe.
I was told that searches would be much slower as a result, since it would
require a join between 2 tables. Basically right now, there's a USER table
and all of a user's profile data is stored there. A few of the fields are
comma-delisted ones. The programmer is saying that by moving them to
separate tables, that it would slow down searches due to having to use join
statements.
What are your thoughts on this?

comma delimited text file to Database

Hi,
I want to select a comma delimited text file into a table.
Each field separated by a comma should be a field in the table.
e.g "faa,fee,fii,foo,fuu" should be insert as
field1 field2 field3 field4 field5
faa fee fii foo fuu
My file is not a fixed length.Hi
Have a look at DTS that comes with SQL Server.
Regards
Mike
"Givosky" wrote:
> Hi,
> I want to select a comma delimited text file into a table.
> Each field separated by a comma should be a field in the table.
> e.g "faa,fee,fii,foo,fuu" should be insert as
> field1 field2 field3 field4 field5
> faa fee fii foo fuu
> My file is not a fixed length.
>|||Hi Mike,
I don't want to use DTS as the current system is importing the text file
through Transact SQL, if i can get a transact statement that is equivalent to
Instr i'll be glad.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Have a look at DTS that comes with SQL Server.
> Regards
> Mike
> "Givosky" wrote:
> > Hi,
> > I want to select a comma delimited text file into a table.
> > Each field separated by a comma should be a field in the table.
> > e.g "faa,fee,fii,foo,fuu" should be insert as
> > field1 field2 field3 field4 field5
> > faa fee fii foo fuu
> >
> > My file is not a fixed length.
> >

comma delimited text file to Database

Hi,
I want to select a comma delimited text file into a table.
Each field separated by a comma should be a field in the table.
e.g "faa,fee,fii,foo,fuu" should be insert as
field1 field2 field3 field4 field5
faa fee fii foo fuu
My file is not a fixed length.
Hi
Have a look at DTS that comes with SQL Server.
Regards
Mike
"Givosky" wrote:

> Hi,
> I want to select a comma delimited text file into a table.
> Each field separated by a comma should be a field in the table.
> e.g "faa,fee,fii,foo,fuu" should be insert as
> field1 field2 field3 field4 field5
> faa fee fii foo fuu
> My file is not a fixed length.
>
|||Hi Mike,
I don't want to use DTS as the current system is importing the text file
through Transact SQL, if i can get a transact statement that is equivalent to
Instr i'll be glad.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Have a look at DTS that comes with SQL Server.
> Regards
> Mike
> "Givosky" wrote:
|||Try this:
BULK INSERT yourdb.dbo.yourtable FROM 'c:\temp\tbcp.csv'
with (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n' , TABLOCK)
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

comma delimited text file to Database

Hi,
I want to select a comma delimited text file into a table.
Each field separated by a comma should be a field in the table.
e.g "faa,fee,fii,foo,fuu" should be insert as
field1 field2 field3 field4 field5
faa fee fii foo fuu
My file is not a fixed length.Hi
Have a look at DTS that comes with SQL Server.
Regards
Mike
"Givosky" wrote:

> Hi,
> I want to select a comma delimited text file into a table.
> Each field separated by a comma should be a field in the table.
> e.g "faa,fee,fii,foo,fuu" should be insert as
> field1 field2 field3 field4 field5
> faa fee fii foo fuu
> My file is not a fixed length.
>|||Hi Mike,
I don't want to use DTS as the current system is importing the text file
through Transact SQL, if i can get a transact statement that is equivalent t
o
Instr i'll be glad.
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Have a look at DTS that comes with SQL Server.
> Regards
> Mike
> "Givosky" wrote:
>|||Try this:
BULK INSERT yourdb.dbo.yourtable FROM 'c:\temp\tbcp.csv'
with (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n' , TABLOCK)
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Comma Delimited Text File

Hi I'm pretty new to using Microsoft Visual C# .NET and I want to upload a comma delimited text file from my local machine into a table in an sql server database through a web app. How would I go about programming this and what controls do I need? Any help would be much appreciated. Thanks in advance.you can upload a file to the server...there are tons of articles that describe how you can do this.all you need to do is goooogle..now do you want to save the text file as it on the server and maintain a link in a table or do you want to parse the textfile and insert the records into a table ?|||I want to be able to parse the comma delimited textfile and insert the records into a table. Thanks.|||check out my blog i have an article ( under SQL Server 2000 ) tht has some sample code too..

hth|||

Hi,

Could you be able to point me to where is your blog link or the link to where the relate article for this topic. Really appreciate and thanks in advance.

comma delimited results help please

Would like to have a view created to display the result at the bottom of this message. We will be using Dreamweaver to display the information from this view. Also, for the record, we are using sql 2000. Any help would be greatly appreciated.

tblservers
servid servername
1 server1
2 server2
3 server3

tblapplications
appid appname
1 app1
2 app2
3 app3

tblapplink
id appid servid
1 1 1
2 1 2
3 1 3
4 2 1
5 2 3
6 3 1

we want to display this information as below:

appname serverlist
app1 server1, server2, server3
app2 server1, server3
app3 server1

Thank you very much,
mrtwohttp://www.dbforums.com/showthread.php?p=3710823#post3710823|||Thank you very much! It works just as we needed it to. We appreciate you taking time to submit a reply!!

One big frustration down, most likely several more to go!

Thanks again!

mrtwo

comma delimited list update stored procedure

I have a stored procedure that I want to use to update multiple records. I'm using ASP and the request form collection is returning values in a comma delimited list.
Example:
name1 = value1, value2, value3, etc.
name2 = value1, value2, value3, etc.
name3 = value1, value2, value3, etc.

Here is how I wrote my stored procedure:

CREATE PROCEDURE dbo.Sp_Update_ABR_Record
(
@.abrID int,
@.ddo varchar(50),
@.ay varchar(50),
@.strategy varchar(10),
@.budgacct varchar(10),
@.budgobj varchar(10),
@.origamt
varchar(50),
@.incrdecr varchar(50),
@.review char(10),
@.abrdetlsID varchar(50)
)
AS
UPDATE DIM_ABR_REQ_HDR
SET ABR_review = @.review
WHERE ABR_ID = @.abrID

UPDATE DIM_ABR_REQ_DETLS
SET ABR_DETLS_DDO = @.ddo, ABR_DETLS_AY = @.ay,
ABR_DETLS_STRATEGY = @.strategy, ABR_DETLS_BUDG_ACCT = @.budgacct,
ABR_DETLS_BUDG_OBJ = @.budgobj, ABR_DETLS_FUND_ORIG_AMT = convert(money, @.origamt), ABR_DETLS_FUND_INCR_DECR = convert(money, @.incrdecr)
WHERE
ABR_DETLS_ID = @.abrdetlsID
GO

The second update is where the comma delimited list needs to be handled. The first update is only updating one field once.

Is there a way to write the procedure to handle the comma delimited list? Or, is the way I have the stored procedure okay and I just need to handle the comma delimited list within the ASP code? I'm not sure which way I can accomplish this?

Thanks for any help.
-D-Hi,
I think providing values in XML format rather than comma delimited will deliver more flexibility to retrieve values from that.
Still you can write User Defined Functions to pass the comma delimited string into that and get particular value.
Regards,
Leila|||Which parameter is the list, and how did you intend to use it?

-PatP|||The request form collection will return each of these parameters in a comma delimited list, which the second update in the stored procedure would handle:

@.ddo varchar(50),
@.ay varchar(50),
@.strategy varchar(10),
@.budgacct varchar(10),
@.budgobj varchar(10),
@.origamt varchar(50),
@.incrdecr varchar(50),
@.abrdetlsID varchar(50)

So, should I use the split function and pass the information into the procedure that way? I've used the split funciton for one parameter, but not multiple parameters. So, I wasn't sure how to code for that?

Thank you for your help.
-D-|||If the number of parameters the same across the set then do parse them and execute the procedure once for each combination. Otherwise, you need to review this design and get away from doing thing this way.|||Yes, there are the same number of parameters for each variable in the set. So, if I use the split function:

ddolist = Split(Request.Form("ddo"),", "
strategylist = Split(Request.Form("strategy"),", "
aylist = Split(Request.Form("ay"),", "
budgobjlist = Split(Request.Form("budgobj"),", "
budgacctlist = Split(Request.Form("budgacct"),", "
incrdecrlist = Split(Request.Form("incrdecr"),", "
abrdetlsIDlist = Split(Request.Form("abrdetlsID"),", "

I can use any of the parameters to determine the number of loops by using Ubound?

i.e.:

Loop_Max = UBound(abrdetlsIDlist)
For x = 0 to Loop_Max
Command_Name.Parameters.Item("@.ddo").Value = ddolist(x)
Command_Name.Parameters.Item("@.strategy").Value = strategylist(x)
Command_Name.Parameters.Item("@.ay").Value = aylist(x)
Command_Name.Parameters.Item("@.budgobj").Value = budgobjlist(x)
Command_Name.Parameters.Item("@.budgacct").Value = budgacctlist(x)
Command_Name.Parameters.Item("@.incrdecr").Value = incrdecrlist(x)
Command_Name.Parameters.Item("@.abrdetlsID").Value = abrdetlsIDlist(x)
Command_Name.Execute()
Next

Would that be correct?

Thank you for your help.
Regards,
-D-