Concatinating Variables

Hello,

How do I concatinate a variable. Here's the scenarios:

declare @.var1 varchar(20)
declare @.var2 varchar(20)
declare @.var3 varchar(20)
declare @.var4 varchar(20)
..
..
declare @.var32 varchar(20)

set @.var1 = 'Something 1'
set @.var2 = 'Something 2'
...
set @.var32 = 'Something 3'

/* I have to store the values of these individual variables. I wish to
have a "While" routine which iterates through the above variables. I
wish to have the variable name concatinated as that I do not have to
write numerous lines of code setting up individual 32 variables. How
could I use the '+' operator to join 'var' + @.count . Where count is
from 1 through 32. I am having some trouble with the syntax.*/

Regards,
VS[posted and mailed, please reply in news]

TinTin (lalalulu24@.yahoo.com) writes:
> How do I concatinate a variable. Here's the scenarios:
> declare @.var1 varchar(20)
> declare @.var2 varchar(20)
> declare @.var3 varchar(20)
> declare @.var4 varchar(20)
> .
> .
> declare @.var32 varchar(20)
> set @.var1 = 'Something 1'
> set @.var2 = 'Something 2'
> ...
> set @.var32 = 'Something 3'

SELECT @.concat = @.var1 + @.var2 + ... + @.var32

> /* I have to store the values of these individual variables. I wish to
> have a "While" routine which iterates through the above variables. I
> wish to have the variable name concatinated as that I do not have to
> write numerous lines of code setting up individual 32 variables. How
> could I use the '+' operator to join 'var' + @.count . Where count is
> from 1 through 32. I am having some trouble with the syntax.*/

Nah, you don't have a syntax problem, you have a mindset problem. When
you work in T-SQL, looping is something you don't do that often. This
is a very different language from VB or C++.

While T-SQL does have some looping constructs, normally you operate on
sets of data at a time through tables. While this may be more difficult
to grok initially, this is necessity to get performance with any size
of data volume.

Since I don't know why you have these 32 variables, and what your actual
business problem is, I cannot really tell what you should do instead.
But you should probably not concatenate 32 variables.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

You will probably run into scope problems with this approach.

There seems to be a design issue here, but without knowing more about the
table strucures and what you are actually trying to do it is hard to suggest
a better solution other than hard coding each one or (as per your previous
post) look at using a temporary tables.

John

"TinTin" <lalalulu24@.yahoo.com> wrote in message
news:2d5425d1.0406151243.3bf0165a@.posting.google.c om...
> Hello,
> How do I concatinate a variable. Here's the scenarios:
> declare @.var1 varchar(20)
> declare @.var2 varchar(20)
> declare @.var3 varchar(20)
> declare @.var4 varchar(20)
> .
> .
> declare @.var32 varchar(20)
> set @.var1 = 'Something 1'
> set @.var2 = 'Something 2'
> ...
> set @.var32 = 'Something 3'
> /* I have to store the values of these individual variables. I wish to
> have a "While" routine which iterates through the above variables. I
> wish to have the variable name concatinated as that I do not have to
> write numerous lines of code setting up individual 32 variables. How
> could I use the '+' operator to join 'var' + @.count . Where count is
> from 1 through 32. I am having some trouble with the syntax.*/
> Regards,
> VS|||Hello John and Erland,

Thankyou for replying to my message.

As a matter or fact I have total of 52 variables.

These values are stored in an Excel spreadsheet. I use the DTS service
to import this spreadsheet into a temp table in SQL Server. Now I need
to catch these 53 different numeric values and parse them into
appropriate tabels in my Database. I use the Cursor to traverse
through the records in temp table and all these 53 values are Fetched
in 1 single record.

Hope this helps.

Regards!

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<VbLzc.1167$hb6.9819346@.news-text.cableinet.net>...
> Hi
> You will probably run into scope problems with this approach.
> There seems to be a design issue here, but without knowing more about the
> table strucures and what you are actually trying to do it is hard to suggest
> a better solution other than hard coding each one or (as per your previous
> post) look at using a temporary tables.
> John
> "TinTin" <lalalulu24@.yahoo.com> wrote in message
> news:2d5425d1.0406151243.3bf0165a@.posting.google.c om...
> > Hello,
> > How do I concatinate a variable. Here's the scenarios:
> > declare @.var1 varchar(20)
> > declare @.var2 varchar(20)
> > declare @.var3 varchar(20)
> > declare @.var4 varchar(20)
> > .
> > .
> > declare @.var32 varchar(20)
> > set @.var1 = 'Something 1'
> > set @.var2 = 'Something 2'
> > ...
> > set @.var32 = 'Something 3'
> > /* I have to store the values of these individual variables. I wish to
> > have a "While" routine which iterates through the above variables. I
> > wish to have the variable name concatinated as that I do not have to
> > write numerous lines of code setting up individual 32 variables. How
> > could I use the '+' operator to join 'var' + @.count . Where count is
> > from 1 through 32. I am having some trouble with the syntax.*/
> > Regards,
> > VS|||Also:

53 values is the worse case scenario. Total number of variables could
be 3 to 53. Script needs to check how many total variables there are
and then likewise take action.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<VbLzc.1167$hb6.9819346@.news-text.cableinet.net>...
> Hi
> You will probably run into scope problems with this approach.
> There seems to be a design issue here, but without knowing more about the
> table strucures and what you are actually trying to do it is hard to suggest
> a better solution other than hard coding each one or (as per your previous
> post) look at using a temporary tables.
> John
> "TinTin" <lalalulu24@.yahoo.com> wrote in message
> news:2d5425d1.0406151243.3bf0165a@.posting.google.c om...
> > Hello,
> > How do I concatinate a variable. Here's the scenarios:
> > declare @.var1 varchar(20)
> > declare @.var2 varchar(20)
> > declare @.var3 varchar(20)
> > declare @.var4 varchar(20)
> > .
> > .
> > declare @.var32 varchar(20)
> > set @.var1 = 'Something 1'
> > set @.var2 = 'Something 2'
> > ...
> > set @.var32 = 'Something 3'
> > /* I have to store the values of these individual variables. I wish to
> > have a "While" routine which iterates through the above variables. I
> > wish to have the variable name concatinated as that I do not have to
> > write numerous lines of code setting up individual 32 variables. How
> > could I use the '+' operator to join 'var' + @.count . Where count is
> > from 1 through 32. I am having some trouble with the syntax.*/
> > Regards,
> > VS|||> through the records in temp table and all these 53 values are Fetched
> in 1 single record.

This sounds like a design problem too. Column values should be atomic values
not concatenated strings. On the other hand, 53 columns representing a list
of values seems unlikely to be the right design either: "Lists" are
analogous to *tables* not a set of values in a row.

Are you familiar with the concept of Normalization? Are you sure your
database is in at least Third Normal Form? If you don't have the correct
design to start with then you won't have the right foundation to build
concise, efficient and maintainable code and you'll have to struggle with
lots of cursors, loops and variables.

On the other hand, if you've already properly identified the entities and
attributes in your schema then post your CREATE TABLE statements so that we
can understand the problem and suggest how to tacke it.

Hope this helps.

--
David Portas
SQL Server MVP
--|||The table which I talk about is a temporary table which is created
within the procedure and would be deleted once I leave the stored
procedure. The sole purpose of the table would be to catch the values
from a seperate table which is extracted from Excel spreadsheet. It's
not dependent on any of the other tables in the database. Hence,
haven't looked upon Normalizing.

Here's the sample lines from the spreadsheet.I used the DTS to import
this into the database table.

Country Nigeria
Region African Continent
Gender Male Male Male Male Male Female
Female ...
Age Group 0-10 10-20 20-30 40-50 50-60 0-10
10-20 ...
Total 200 323 111 3232 333 555 333
..
..
..

Country Nicragua
Region African Continent
Gender Male Male Male Female Female Female
Age Group 0-4 15-20 20-30 0-4 15-20 20-30
Total 200 323 111 3232 112 441
..
..
..

Now... Age group can have several more or a lot fewer age categories.
I need to capture the individual values in the 'Age Group' and
'Total'. That is why I need to have 53 variables (Worst Case). I use a
Cursor to iterate through each of the above lines. Once I capture the
information, I need to send it to my database structure.

What's the best way to design a Stored Procedure. Messier way is to
have 53 varbs. (worst case) and have 2 tempory tables to store the
above 2 rows (Age Group, Total). If I follow this approach, I need to
iterate through the fields in the temporary variables to make sure I
do not encounter any NULLs in between. For example, the 2nd example
above ( Nicragua) will have nulls in all the fields from field 6
through 53.

Following is the structure of the temp table
create table temptable1(
var1 varchar (10),
var2 varchar (10),
..
..
..
var53 varchar (10)
)

Regards,
VS

lalalulu24@.yahoo.com (TinTin) wrote in message news:<2d5425d1.0406160522.73ecb03@.posting.google.com>...
> Also:
> 53 values is the worse case scenario. Total number of variables could
> be 3 to 53. Script needs to check how many total variables there are
> and then likewise take action.
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<VbLzc.1167$hb6.9819346@.news-text.cableinet.net>...
> > Hi
> > You will probably run into scope problems with this approach.
> > There seems to be a design issue here, but without knowing more about the
> > table strucures and what you are actually trying to do it is hard to suggest
> > a better solution other than hard coding each one or (as per your previous
> > post) look at using a temporary tables.
> > John
> > "TinTin" <lalalulu24@.yahoo.com> wrote in message
> > news:2d5425d1.0406151243.3bf0165a@.posting.google.c om...
> > > Hello,
> > > > How do I concatinate a variable. Here's the scenarios:
> > > > declare @.var1 varchar(20)
> > > declare @.var2 varchar(20)
> > > declare @.var3 varchar(20)
> > > declare @.var4 varchar(20)
> > > .
> > > .
> > > declare @.var32 varchar(20)
> > > > set @.var1 = 'Something 1'
> > > set @.var2 = 'Something 2'
> > > ...
> > > set @.var32 = 'Something 3'
> > > > /* I have to store the values of these individual variables. I wish to
> > > have a "While" routine which iterates through the above variables. I
> > > wish to have the variable name concatinated as that I do not have to
> > > write numerous lines of code setting up individual 32 variables. How
> > > could I use the '+' operator to join 'var' + @.count . Where count is
> > > from 1 through 32. I am having some trouble with the syntax.*/
> > > > Regards,
> > > VS|||It seems that the purpose of your stored procedure is to transform the
spreadsheet data into a form that you can use, presumably in a table. It is
possible to do this kind of transformation in SQL, it just isn't pretty!
Here
goes.

First, assume you have your sample data loaded into your temp table. I've
added a row number to help us later. You would obviously do this bit in DTS:

CREATE TABLE TempTable1 (rowno INTEGER IDENTITY PRIMARY KEY, col1
VARCHAR(20) NULL, col2 VARCHAR(20) NULL, col3 VARCHAR(20) NULL, col4
VARCHAR(20) NULL, col5 VARCHAR(20) NULL, col6 VARCHAR(20) NULL, col7
VARCHAR(20) NULL, col8 VARCHAR(20) NULL)

INSERT INTO TempTable1 (col1,col2) VALUES ('Country','Nigeria')
INSERT INTO TempTable1 (col1,col2) VALUES ('Region','African Continent')
INSERT INTO TempTable1 (col1,col2,col3,col4,col5,col6,col7,col8)
VALUES ('Gender','Male','Male','Male','Male','Male','Fema le','Female')
INSERT INTO TempTable1 (col1,col2,col3,col4,col5,col6,col7,col8)
VALUES ('Age Group','0-10','10-20','20-30','40-50','50-60','0-10','10-20')
INSERT INTO TempTable1 (col1,col2,col3,col4,col5,col6,col7,col8)
VALUES ('Total','200','323','111','3232','333','555','333 ')
INSERT INTO TempTable1 (col1,col2) VALUES ('Country','Nicaragua')
INSERT INTO TempTable1 (col1,col2) VALUES ('Region','African Continent')
INSERT INTO TempTable1 (col1,col2,col3,col4,col5,col6,col7)
VALUES ('Gender','Male','Male','Male','Female','Female',' Female')
INSERT INTO TempTable1 (col1,col2,col3,col4,col5,col6,col7)
VALUES ('Age Group','0-4','15-20','20-30','0-4','15-20','20-30')
INSERT INTO TempTable1 (col1,col2,col3,col4,col5,col6,col7)
VALUES ('Total','200','323','111','3232','112','441')

You haven't told us what the target structure is that you want to put the
data into. Based on your sample I'll assume it looks something like this:

CREATE TABLE SomeStats (country VARCHAR(20) NOT NULL /* REFERENCES Countries
(country) */, min_age INTEGER NOT NULL, max_age INTEGER NOT NULL, CHECK
(min_age>=0 AND max_age>min_age AND max_age<=120), gender CHAR(1) NOT NULL
CHECK (gender IN ('M','F')), stat INTEGER NOT NULL, PRIMARY KEY
(country,gender,min_age))

In reality you would probably want to use codes rather than country names.
The Continent name obviously belongs in the Countries table rather than here
so I've left it out.

Create a view:

CREATE VIEW TransformStats
AS
SELECT rowno, 1 AS col, col1 AS stat
FROM TempTable1
UNION ALL
SELECT rowno, 2 AS col, col2
FROM TempTable1
UNION ALL
SELECT rowno, 3 AS col, col3
FROM TempTable1
UNION ALL
SELECT rowno, 4 AS col, col4
FROM TempTable1
UNION ALL
SELECT rowno, 5 AS col, col5
FROM TempTable1
UNION ALL
SELECT rowno, 6 AS col, col6
FROM TempTable1
UNION ALL
SELECT rowno, 7 AS col, col7
FROM TempTable1
UNION ALL
SELECT rowno, 8 AS col, col8
FROM TempTable1

Finally, insert your data:

INSERT INTO SomeStats (country, min_age, max_age, gender, stat)
SELECT country,
LEFT(age,CHARINDEX('-',age)-1),
SUBSTRING(age,CHARINDEX('-',age)+1,20),
gender, stat
FROM
(SELECT
(SELECT stat
FROM TransformStats
WHERE rowno=
(SELECT MAX(rowno)
FROM TransformStats
WHERE col = 1
AND stat = 'Country'
AND rowno <= T.rowno)
AND col=2) AS country,
(SELECT stat
FROM TransformStats
WHERE rowno=
(SELECT MAX(rowno)
FROM TransformStats
WHERE col = 1
AND stat = 'Age Group'
AND rowno <= T.rowno)
AND col=T.col) AS age,
(SELECT LEFT(stat,1)
FROM TransformStats
WHERE rowno=
(SELECT MAX(rowno)
FROM TransformStats
WHERE col = 1
AND stat = 'Gender'
AND rowno <= T.rowno)
AND col=T.col) AS gender,
stat
FROM
TransformStats AS T
WHERE ISNUMERIC(stat)=1) AS T

This will of course fail if your spreadsheets aren't in a fairly regular,
predictable format. This is an unavoidable problem with spreadsheet data and
there isn't an easy solution except to find a reliable, structured data
source. Your data looks somewhat suspect anyway. Last time I checked my
atlas Nicragua [sic] wasn't in Africa :)

Hope this helps.

--
David Portas
SQL Server MVP
--

Concatinating two field and insert the result

Hii,
I need to concatinate two field and insert the result into each record. So far I managed to display the concatination but how do I insert it?
use northwind

select city, region,([city]+ +[region]) as uniqe
from customers
where region is not null
The resulting records in Quary
Anchorage AK AnchorageAK
Tsawassen BC TsawassenBC
Vancouver BC VancouverBC
San Francisco CA San FranciscoCA

Try it like this:

UPDATE
Customers
SET
city = city + ' ' + region
WHERE
region IS NOT NULL

concatinating string values from multiple rows

I currently have some SQL code that is used to build a string that is a concatination of string values across multiple rows. The subqueries in the script sometimes return NULL values so I use the following statement to change the default behavior of the concatination operator which prevents my query from returning NULL:

SET CONCAT_NULL_YIELDS_NULL ON

Here's the code snippet:

select DISTINCT

(SELECT CASE WHEN (t1.MaskValue & HDR.TranTypeID)=1 THEN ' ' + t1.description ELSE '' END FROM transactiontypes t1 WHERE (t1.MaskValue & HDR.TranTypeID)=1) +

(SELECT CASE WHEN (t2.MaskValue & HDR.TranTypeID)=2 THEN ' ' + t2.description ELSE '' END FROM transactiontypes t2 WHERE (t2.MaskValue & HDR.TranTypeID)=2) +

(SELECT CASE WHEN (t3.MaskValue & HDR.TranTypeID)=4 THEN ' ' + t3.description ELSE '' END FROM transactiontypes t3 WHERE (t3.MaskValue & HDR.TranTypeID)=4) +

(SELECT CASE WHEN (t4.MaskValue & HDR.TranTypeID)=8 THEN ' ' + t4.description ELSE '' END FROM transactiontypes t4 WHERE (t4.MaskValue & HDR.TranTypeID)=8) +

(SELECT CASE WHEN (t5.MaskValue & HDR.TranTypeID)=16 THEN ' ' + t5.description ELSE '' END FROM transactiontypes t5 WHERE (t5.MaskValue & HDR.TranTypeID)=16)) as 'Transaction Type'

FROM HDResponse HDR

Here's the underlying table structure:

CREATE TABLE [dbo].[TransactionTypes](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [MaskValue] [int] NOT NULL) ON [PRIMARY]

CREATE TABLE [dbo].[HDResponse](
[ResponseID] [int] IDENTITY(1,1) NOT NULL,
[LoggedDateTime] [datetime] NULL,
[ResponseTypeTripID] [int] NULL,
[ResponseTypeID] [int] NULL,
[ResponseTypeObjectID] [int] NULL,
[ObjectID] [int] NULL,
[IDHolderID] [int] NULL,
[TransportCode] [int] NULL,
[CardID] [int] NULL,
[IssueCode] [smallint] NULL,
[EventDateTime] [datetime] NULL,
[Response] [bit] NULL,
[TranTypeID] [int] NULL)
ON [PRIMARY]

The problem I am having is I need to be able to use the query above in a view used for reporting. Unfortunately, you cannot use SET CONCAT_NULL_YIELDS_NULL ON in a view. This causes my query to return NULL if any of the subqueries return NULL. I could create a function to do something similar and reference the function in the query but I can't help but think there must be a way to get this done in a single query.

Any thoughts or ideas would be greatly appreciated.

Thanks!!!!!

What version of SQL Server are you using? SET CONCAT_NULL_YEILDS_NULL is ON by default unless you have changed the settings after connecting to SQL Server. This is true for any connections made via ODBC/OLEDB. You can use the PIVOT operator in SQL Server 2005 to solve this problem. You can also do this using a standard SQL technique like below:

select max(case p.trantype when 1 then p.description else '' end) +
max(case p.trantype when 2 then ' ' + p.description else '' end) +
max(case p.trantype when 4 then ' ' + p.description else '' end) +
max(case p.trantype when 8 then ' ' + p.description else '' end) +
max(case p.trantype when 16 then ' ' + p.description else '' end) as "Transaction Type"
from (
select t.description, (t.MaskValue & h.TranTypeID) as trantype
from HDResponse as h
join transactiontypes as t
on (t.MaskValue & h.TranTypeID) in (1, 2, 4, 8, 16)
) as p

Concatinating SmallInt Data Type

Newbie question regarding a db I have inherited.

A table FullDocuments has a DocNo column with smallint data type and a SequenceNo column also with smallint data type.

DocNohas numbers that represent persons. SequenceNo has numbers thatrepresent specific documents associated with each person (DocNo).

So DocNo 5 and Sequence 3 represents the 3rd document associated with person 5.

My SELECT statement looks like this:

SELECT ReadingNo, SequenceNo

This returns data like this: 5 3

I would like to concatenate the SELECT statement to return like this: 5-3

So I made Sql like this:

SELECT ReadingNo + '-" + SequenceNo

Whichreturns a alias ('No Column Named') result value of 8 which is anarithmetic result instead of a string concatination that I want.

So my questions are:

1. Should the original database designer have used string data types forthese columns since they will never be used for math purposes?

2. Do I need to cast them to string data type (like nchar(4) - sinceneither column will ever exceed 4 digits) to get the result I desire?

3. Or can I keep them as smallint and modify my SELECT statement to allow concatination yielding a string result?

Select Cast(ReadingNo As VarChar(5)) + '-' + Cast(SequenceNo As VarChar(5)) As DocNo

Basically what you need to do is cast (or convert) the two columns as either char or varchar so the sql engine knows to concat instead of adding.

|||

Excellent. Thanks so much. For future reference (should I have to create such a db from scratch), should I use varchar data type in this situation? Or is there a good reason (such as file size) to use the smallint type for such data?

|||

Depends on the business needs of the colums in your database.

If you have a need for the columns as int, smallint ect then use that.

When designing databases always look at 1st what your going to do with the data you are storing to help you determine best model.

|||

Thanks again for your input.

Concatinating Select Results

Hey everyone,

I have an SSIS conversion issue. I'm pulling two tables from a DB2 database into SQL 2005. One table has a list of work orders, and the other has a list of work order comments. There is a unique identifier between the two tables so that a join can be used, however, due to size limitations, I need to be able to combine both tables.

The end result will be replicated out for SQL Mobile Edition and the file is too large when both tables exist so I am wanting to concatinate all the comments for each work order into a single text field in the work orders table.

Here is what I am wanting to accomplish:

UPDATE tblWorkOrders
SET Comments = (SELECT Comments
FROM tblComments
WHERE tblWorkOrders.ReqNum =
tblComments.ReqNum)

I know that this statement will not work because there is a one-to-many relationship between the tables so each work order could get multiple results.

I would appreciate any suggestions.

Thanks,

Lee.

There are probably a number of ways of doing this. The first thing that occurs to me is to use an asynchronous script component that takes a set of data (ordered by ReqNum). Inside the script component loop over the set of data, concatenating comments for each ReqNum.

-Jamie

|||Hey Jamie,

Thanks for the response. I'm very new to SSIS so that's a little over my head. Could you elaborate a little more on all that? Or can you think of an easier way of accomplishing this?

Thanks

Lee.

Concatinating child field

I'm developing an ASP.NET app with SQL back end. I'm trying to develop a use
r
interface that must reference information from two related tables and would
like to return the following (either in table, but preferably in datagrid):
ParentField1 ParentField2 Parent Field3 ChildField1Record1
ChildField1Record2
ChildField1Record3
ParentField1 ParentField2 ParentField3 ChildFeld1Record4
ChildFeld1Record5
ChildField1Record6 etc.
Is there anyway to write a TSQL function to concatinate the child records
into a dynamic field (there are about 400 parent records)
I could create a denormalized table that mimics the child table by has the
ParentID and ChildRecords concatinated that gets updated everytime the child
table changes, but that's just bad programming!
Any suggestions would be greatly appreciated.
Thanks in advance.well the question is not clear to me. but i guess u are trying to concatinat
e
the fields in the query
ucan do it as
select <field1> + <field2> + ..
FROM <Table>
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"AbeR" wrote:

> I'm developing an ASP.NET app with SQL back end. I'm trying to develop a u
ser
> interface that must reference information from two related tables and woul
d
> like to return the following (either in table, but preferably in datagrid)
:
> ParentField1 ParentField2 Parent Field3 ChildField1Record1
> ChildField1Record2
> ChildField1Record3
> ParentField1 ParentField2 ParentField3 ChildFeld1Record4
> ChildFeld1Record5
> ChildField1Record6 etc.
> Is there anyway to write a TSQL function to concatinate the child records
> into a dynamic field (there are about 400 parent records)
> I could create a denormalized table that mimics the child table by has the
> ParentID and ChildRecords concatinated that gets updated everytime the chi
ld
> table changes, but that's just bad programming!
> Any suggestions would be greatly appreciated.
> Thanks in advance.|||Hi Chandra,
Thanks for the response, and sorry for not being clear. Actually I am trying
to return contents of all records with the ParentID from the child table wit
h
the parent record from the parent table, so actually want to select
record1field1 + record2field1 + record3field1 from chldtable joining
parenttable without repeating the parent data for each record.
Regards,
AbeR
"Chandra" wrote:
> well the question is not clear to me. but i guess u are trying to concatin
ate
> the fields in the query
> ucan do it as
> select <field1> + <field2> + ..
> FROM <Table>
> please let me know if u have any questions
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "AbeR" wrote:
>|||>> Is there anyway to write a TSQL function to concatinate the child records
The terminology you used is a bit confusing.
Assuming that there is a one-to-many relationship that exist between the
tables, what you seem to be asking is to return a resultset with the single
value from the referenced table and all the values from the referencing
table as a contenated string. In general, such processing which demand heavy
looping and formatting of values should be done on the client side -- quite
often one should be able to leverage the functionality of a programming
language or a report-writer in such cases.
Doing this on the server often requires procedural logic, often disguised as
a user defined function or perhaps cursors. For some such workarounds, refer
to: http://tinyurl.com/aka4u
Anith|||Exactly. I was hoping not to create another dataset on the client system, bu
t
that's probably the best approach (While loops in C# within the app)...
DataSet1 will be read by the client and produce dataset two with all the
values from the referenced table as one string and then present in a datagri
d.
Many thanks for the advice and reference.
- AbeR
"Anith Sen" wrote:

> The terminology you used is a bit confusing.
> Assuming that there is a one-to-many relationship that exist between the
> tables, what you seem to be asking is to return a resultset with the singl
e
> value from the referenced table and all the values from the referencing
> table as a contenated string. In general, such processing which demand hea
vy
> looping and formatting of values should be done on the client side -- quit
e
> often one should be able to leverage the functionality of a programming
> language or a report-writer in such cases.
> Doing this on the server often requires procedural logic, often disguised
as
> a user defined function or perhaps cursors. For some such workarounds, ref
er
> to: http://tinyurl.com/aka4u
> --
> Anith
>
>