concatenating Varchar and Text

I am trying to write a select statement that concatenates a Varchar and Text
field into a Text.
So I basically want something like:
Select VarcharColumn + TextColumn from tablea
I know that I could convert the TextColumn to varchar(8000) and concatenate
that with the varchar column but there may be instances where the TextColumn
exceeds 8000 bytes. So I would need the datatype of this concatenated field
to be of type TEXT.
Any help would be appreciated.
ThanksYou will have to use UPDATETEXT to concatenate text columns. Check out the
details, syntax and examples of UPDATETEXT in SQL Server Books Online.
Anith

concatenating Varchar and Text

I am trying to write a select statement that concatenates a Varchar and Text
field into a Text.
So I basically want something like:
Select VarcharColumn + TextColumn from tablea
I know that I could convert the TextColumn to varchar(8000) and concatenate
that with the varchar column but there may be instances where the TextColumn
exceeds 8000 bytes. So I would need the datatype of this concatenated field
to be of type TEXT.
Any help would be appreciated.
ThanksYou will have to use UPDATETEXT to concatenate text columns. Check out the
details, syntax and examples of UPDATETEXT in SQL Server Books Online.
--
Anith

concatenating Varchar and Text

I am trying to write a select statement that concatenates a Varchar and Text
field into a Text.
So I basically want something like:
Select VarcharColumn + TextColumn from tablea
I know that I could convert the TextColumn to varchar(8000) and concatenate
that with the varchar column but there may be instances where the TextColumn
exceeds 8000 bytes. So I would need the datatype of this concatenated field
to be of type TEXT.
Any help would be appreciated.
Thanks
You will have to use UPDATETEXT to concatenate text columns. Check out the
details, syntax and examples of UPDATETEXT in SQL Server Books Online.
Anith

Concatenating two floats with a comma in the middle

i am using the following code to try and get the following results:
convert(varchar,r.LowerStrike)+ ',' + convert(varchar,r.UpperStrike)
to get:
(for example) "100.22,44.5"
But i get the following error: Error converting data type varchar to
float.
I assume sqlserver is trying to convert the comma to a float to do an
addition.
I thought that the fact that i converted the two floats to varchars
would have stopped this, but it doesn't.
Does anyone know why?
<arun.hallan@.gmail.com> wrote in message
news:1138889221.983004.20020@.g47g2000cwa.googlegro ups.com...
>i am using the following code to try and get the following results:
> convert(varchar,r.LowerStrike)+ ',' + convert(varchar,r.UpperStrike)
> to get:
> (for example) "100.22,44.5"
>
> But i get the following error: Error converting data type varchar to
> float.
> I assume sqlserver is trying to convert the comma to a float to do an
> addition.
> I thought that the fact that i converted the two floats to varchars
> would have stopped this, but it doesn't.
> Does anyone know why?
>
Your code snippet worked fine for me using the following DDL. Can you post
your DDL and maybe we can find the error?
CREATE TABLE #Foo (
LowerStrike float,
UpperStrike float
)
INSERT #Foo VALUES (100.22, 44.5)
INSERT #Foo VALUES (889.38, 4830.0)
SELECT *
FROM #Foo
SELECT CONVERT(varchar, r.LowerStrike) + ', ' + CONVERT(varchar,
r.UpperStrike) AS 'NewValue'
FROM #Foo r
DROP TABLE #Foo
Rick Sawtell
MCT, MCSD, MCDBA
|||I'm not sure what my DDL is.
It's the sqk server at work - not sure where those things are kept.
|||<arun.hallan@.gmail.com> wrote in message
news:1138897900.963273.254320@.z14g2000cwz.googlegr oups.com...
> I'm not sure what my DDL is.
> It's the sqk server at work - not sure where those things are kept.
>
Check here for more info.
http://www.aspfaq.com/etiquette.asp?id=5006
Rick Sawtell
MCT, MCSD, MCDBA

Concatenating two floats with a comma in the middle

i am using the following code to try and get the following results:
convert(varchar,r.LowerStrike)+ ',' + convert(varchar,r.UpperStrike)
to get:
(for example) "100.22,44.5"
But i get the following error: Error converting data type varchar to
float.
I assume sqlserver is trying to convert the comma to a float to do an
addition.
I thought that the fact that i converted the two floats to varchars
would have stopped this, but it doesn't.
Does anyone know why?<arun.hallan@.gmail.com> wrote in message
news:1138889221.983004.20020@.g47g2000cwa.googlegroups.com...
>i am using the following code to try and get the following results:
> convert(varchar,r.LowerStrike)+ ',' + convert(varchar,r.UpperStrike)
> to get:
> (for example) "100.22,44.5"
>
> But i get the following error: Error converting data type varchar to
> float.
> I assume sqlserver is trying to convert the comma to a float to do an
> addition.
> I thought that the fact that i converted the two floats to varchars
> would have stopped this, but it doesn't.
> Does anyone know why?
>
Your code snippet worked fine for me using the following DDL. Can you post
your DDL and maybe we can find the error?
CREATE TABLE #Foo (
LowerStrike float,
UpperStrike float
)
INSERT #Foo VALUES (100.22, 44.5)
INSERT #Foo VALUES (889.38, 4830.0)
SELECT *
FROM #Foo
SELECT CONVERT(varchar, r.LowerStrike) + ', ' + CONVERT(varchar,
r.UpperStrike) AS 'NewValue'
FROM #Foo r
DROP TABLE #Foo
Rick Sawtell
MCT, MCSD, MCDBA|||I'm not sure what my DDL is.
It's the sqk server at work - not sure where those things are kept.|||<arun.hallan@.gmail.com> wrote in message
news:1138897900.963273.254320@.z14g2000cwz.googlegroups.com...
> I'm not sure what my DDL is.
> It's the sqk server at work - not sure where those things are kept.
>
Check here for more info.
http://www.aspfaq.com/etiquette.asp?id=5006
Rick Sawtell
MCT, MCSD, MCDBA

concatenating strings like summing numbers

I'd like to join string within a group, preferable with a separator, the same
way I'd sum values.
So, I'd like to write somethign like
=Join( Fields!Email.Value, ";" )
to obtain a string of semicolon separated substrings, the same way I'd say
=Sum( Fields!Number.Value) to get the sum of numeric field.
Does anyone know a way to do this?Having exactly the same problem. Thought of writing custom code, but not sure
what arguments to pass. The code had an array of strings as an argument, but
when i called the function the same way I would call an aggregate, got an
error message saying that aggregates can take only numeric values as
arguments..
"Max" wrote:
> I'd like to join string within a group, preferable with a separator, the same
> way I'd sum values.
> So, I'd like to write somethign like
> =Join( Fields!Email.Value, ";" )
> to obtain a string of semicolon separated substrings, the same way I'd say
> =Sum( Fields!Number.Value) to get the sum of numeric field.
> Does anyone know a way to do this?
>
>

Concatenating strings in a Select statment

I have this code:
declare @.var varchar(3000)
select @.var = @.var + column1 + ', '
from table1
select @.var
This statements give as a result all the values in column1 followed each by
a coma. My problem is that in a particular sever that doesn't work ok. If I
run the statements inside a proc it only returs the last value in the table,
but if I run it outside the proc (same query) it gives me the correct
results. Does anyone know why could that be?..
THanks>> Does anyone know why could that be?..
The SELECT statement you have is not a valid or supported in t-SQL. It is
simply a hack which seems to work in some cases, but breaks in a variety of
scenarios. Avoid such make-shift constructs due to its undocumented & risky
nature.
Anith

Concatenating strings in a CTE

Hi,

I am trying to create a concatenation of strings in a CTE, to show a "breadcrumb" path for a parent-child hierarchy. I get the error message,

Msg 240, Level 16, State 1, Line 3

Types don't match between the anchor and the recursive part in column "path_name" of recursive query "pc_hier_cte1".

Here are some SQL statements to study the situation. The first CTE merely computes the length of the "breadcrumb" string -- and it works. In the second CTE I try to perform the string concatenation, and that's where I get the error message.

Thanks for your help with this.

Dan

drop table #temp

drop table #pc_hier

create table #pc_hier

(

id int,

parent_id int,

name varchar(100)

)

insert into #pc_hier

select 1, 0, 'top level node' union all

select 2, 1, 'second level node 2' union all

select 3, 1, 'second level node 3' union all

select 4, 1, 'second level node 4' union all

select 20, 2, 'third level node 20' union all

select 21, 2, 'third level node 21' union all

select 22, 2, 'third level node 22' union all

select 30, 3, 'third level node 30' union all

select 31, 3, 'third level node 31' union all

select 32, 3, 'third level node 32' union all

select 40, 4, 'third level node 40' union all

select 41, 4, 'third level node 41' union all

select 42, 4, 'third level node 42'

;

with pc_hier_cte (id, parent_id, name, dist_from_parent, path_length) AS

(

select

ph.id,

ph.parent_id,

ph.name,

0 as dist_from_parent,

0 as path_length -- do not include top-level parent name in the path string

from #pc_hier ph

left outer join #pc_hier ph1

on ph.parent_id = ph1.id

where ph1.id is null -- never a parent

union all

select

ph.id,

ph.parent_id,

ph.name,

dist_from_parent + 1,

(phc.path_length + len(' > ' + ph.name)) as path_length

from #pc_hier ph

inner join pc_hier_cte phc

on ph.parent_id = phc.id

)

select

id,

parent_id,

name,

dist_from_parent,

path_length

into #temp

from pc_hier_cte

order by 4, 3, 1, 2

OPTION (MAXRECURSION 10);

select *

from #temp

order by 4,3,1,2

;

-- Here is the part that does not work, where I am trying to concatenate strings.

drop table #temp1

with pc_hier_cte1 (id, parent_id, name, dist_from_parent, path_length, path_name) AS

(

select

ph.id,

ph.parent_id,

ph.name,

0 as dist_from_parent,

0 as path_length, -- do not include top-level parent name in the path string

'' as path_name

from #pc_hier ph

left outer join #pc_hier ph1

on ph.parent_id = ph1.id

where ph1.id is null -- never a parent

union all

select

ph.id,

ph.parent_id,

ph.name,

dist_from_parent + 1,

(phc.path_length + len(' > ' + ph.name)) as path_length,

(phc.path_name + ' > ' + ph.name) as path_name

from #pc_hier ph

inner join pc_hier_cte1 phc

on ph.parent_id = phc.id

)

select

id,

parent_id,

name,

dist_from_parent,

path_length,

path_name

into #temp1

from pc_hier_cte1

order by 4, 3, 1, 2

OPTION (MAXRECURSION 10);

Hmmmm, by reading a post from today I solved this problem: I just needed to use CAST to define the value as a large VARCHAR. (I thank Kent Waldrop My07 for his post.)

Here is the fix.

drop table #temp1

with pc_hier_cte1 (id, parent_id, name, dist_from_parent, path_length, path_name) AS

(

select

ph.id,

ph.parent_id,

ph.name,

0 as dist_from_parent,

0 as path_length, -- do not include top-level parent name in the path string

cast('' as varchar(1000)) as path_name

from #pc_hier ph

left outer join #pc_hier ph1

on ph.parent_id = ph1.id

where ph1.id is null -- never a parent

union all

select

ph.id,

ph.parent_id,

ph.name,

dist_from_parent + 1,

(phc.path_length + len(' > ' + ph.name)) as path_length,

cast((phc.path_name + ' > ' + ph.name) as varchar(1000)) as path_name

from #pc_hier ph

inner join pc_hier_cte1 phc

on ph.parent_id = phc.id

)

select

id,

parent_id,

name,

dist_from_parent,

path_length,

path_name

into #temp1

from pc_hier_cte1

order by 4, 3, 1, 2

OPTION (MAXRECURSION 10);

select *

from #temp1

order by 4,3,1,2

;

Concatenating Strings

Suppose that I get a resultset of state abbreviations from dataset. Is there a way to write a code so that final output would be concatenation of states separated by a ",".

I tried maintaining a variable in the "Code" block of the report. Apparently, the delcarations do not work as expected.

I want to do something like this:

Dim str As String

and then str should be updated as each row gets rendered. Is that possible?

Thanks.

You may want to read this blog article about "custom aggregates": http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

-- Robert

Concatenating strings

I have a table with seven different descriptor fields, and at times it's
necessary to concatenate all seven into a single field in a query. I tried
simply concatenating the fields together, but if a single input field has a
null the output is a null. It does this even though the
concat_null_yields_null property is set to false, which will never cease to
mystify me.
Regardless, I can probably get the results I want by writing an extensive
CASE statement, but was wondering if there was a simpler method. I'm
wondering if there's a similar function to COALESCE -- instead of returning
the first non-null field I want to return all non-null fields. Either that o
r
is there some other reason besides concat_null_yields_null why null input
would return null using simple string concatenation.
Thanks in advance.Use function ISNULL.
Example.
select 'SQL ' + isnull(cast(null as varchar(25), 'Server')
go
AMB
"mike" wrote:

> I have a table with seven different descriptor fields, and at times it's
> necessary to concatenate all seven into a single field in a query. I tried
> simply concatenating the fields together, but if a single input field has
a
> null the output is a null. It does this even though the
> concat_null_yields_null property is set to false, which will never cease t
o
> mystify me.
> Regardless, I can probably get the results I want by writing an extensive
> CASE statement, but was wondering if there was a simpler method. I'm
> wondering if there's a similar function to COALESCE -- instead of returnin
g
> the first non-null field I want to return all non-null fields. Either that
or
> is there some other reason besides concat_null_yields_null why null input
> would return null using simple string concatenation.
> Thanks in advance.|||You need to say:
SELECT COALESCE(col1, '')+COALESCE(col2, '')+...+COALESCE(colN, '') ...
Or, do the concatenation at the client/presentation tier.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"mike" <mike@.discussions.microsoft.com> wrote in message
news:594CA1BB-46BB-4A34-9C5C-D4EEBB4B9985@.microsoft.com...
> I have a table with seven different descriptor fields, and at times it's
> necessary to concatenate all seven into a single field in a query. I tried
> simply concatenating the fields together, but if a single input field has
a
> null the output is a null. It does this even though the
> concat_null_yields_null property is set to false, which will never cease
to
> mystify me.
> Regardless, I can probably get the results I want by writing an extensive
> CASE statement, but was wondering if there was a simpler method. I'm
> wondering if there's a similar function to COALESCE -- instead of
returning
> the first non-null field I want to return all non-null fields. Either that
or
> is there some other reason besides concat_null_yields_null why null input
> would return null using simple string concatenation.
> Thanks in advance.

Concatenating string variables doesn't appear to work properly

When executing the following statements:
declare @.x char(10), @.y char(10)
set @.x = 'abc'
set @.y = @.x + 'def'
select @.x
select @.y
the results are:
abc
abc
I expect @.y should be equal to 'abcdef'. If I change the var types to
int for example, then @.y is summed correctly. Can anyone tell me why,
or what I'm doing wrong? Thanks.
DanOn Feb 21, 1:42 pm, dan.for...@.matrikon.com wrote:
> When executing the following statements:
> declare @.x char(10), @.y char(10)
> set @.x = 'abc'
> set @.y = @.x + 'def'
> select @.x
> select @.y
> the results are:
> abc
> abc
> I expect @.y should be equal to 'abcdef'. If I change the var types to
> int for example, then @.y is summed correctly. Can anyone tell me why,
> or what I'm doing wrong? Thanks.
> Dan
@.x is defined as a CHAR(10), and as we know, the CHAR datatype
includes trailing spaces. When you assign the value 'abc' to @.x, its
value is really 'abc '. When you then append 'def' to it, you
are actually getting 'abc def', but since @.y is also defined as
CHAR(10), it can only hold the first 10 characters, which are
'abc '. Use VARCHAR(10) instead.|||In addition to Tracy's precise comment, this might help explaining it
further.
select datalength(@.x), datalength(@.x+'def')
--
-oj
<dan.forest@.matrikon.com> wrote in message
news:1172086921.411352.88210@.v33g2000cwv.googlegroups.com...
> When executing the following statements:
> declare @.x char(10), @.y char(10)
> set @.x = 'abc'
> set @.y = @.x + 'def'
> select @.x
> select @.y
> the results are:
> abc
> abc
> I expect @.y should be equal to 'abcdef'. If I change the var types to
> int for example, then @.y is summed correctly. Can anyone tell me why,
> or what I'm doing wrong? Thanks.
> Dan
>|||On Feb 21, 12:49 pm, "Tracy McKibben" <tracy.mckib...@.gmail.com>
wrote:
> On Feb 21, 1:42 pm, dan.for...@.matrikon.com wrote:
>
>
> > When executing the following statements:
> > declare @.x char(10), @.y char(10)
> > set @.x = 'abc'
> > set @.y = @.x + 'def'
> > select @.x
> > select @.y
> > the results are:
> > abc
> > abc
> > I expect @.y should be equal to 'abcdef'. If I change the var types to
> > int for example, then @.y is summed correctly. Can anyone tell me why,
> > or what I'm doing wrong? Thanks.
> > Dan
> @.x is defined as a CHAR(10), and as we know, the CHAR datatype
> includes trailing spaces. When you assign the value 'abc' to @.x, its
> value is really 'abc '. When you then append 'def' to it, you
> are actually getting 'abc def', but since @.y is also defined as
> CHAR(10), it can only hold the first 10 characters, which are
> 'abc '. Use VARCHAR(10) instead.- Hide quoted text -
> - Show quoted text -
Thanks. I wasn't aware of the trailing spaces.
Dan|||On Feb 21, 2:36 pm, dan.for...@.matrikon.com wrote:
> Thanks. I wasn't aware of the trailing spaces.
> Dan
That's the "simplest" way to describe the difference between CHAR and
VARCHAR. CHAR is for fixed-length strings and always contains the
number of characters it's defined for, whereas VARCHAR (variable-CHAR)
is for variable length strings, and only contains what you
specifically put in it.

Concatenating string variables doesn't appear to work properly

When executing the following statements:
declare @.x char(10), @.y char(10)
set @.x = 'abc'
set @.y = @.x + 'def'
select @.x
select @.y
the results are:
abc
abc
I expect @.y should be equal to 'abcdef'. If I change the var types to
int for example, then @.y is summed correctly. Can anyone tell me why,
or what I'm doing wrong? Thanks.
DanOn Feb 21, 1:42 pm, dan.for...@.matrikon.com wrote:
> When executing the following statements:
> declare @.x char(10), @.y char(10)
> set @.x = 'abc'
> set @.y = @.x + 'def'
> select @.x
> select @.y
> the results are:
> abc
> abc
> I expect @.y should be equal to 'abcdef'. If I change the var types to
> int for example, then @.y is summed correctly. Can anyone tell me why,
> or what I'm doing wrong? Thanks.
> Dan
@.x is defined as a CHAR(10), and as we know, the CHAR datatype
includes trailing spaces. When you assign the value 'abc' to @.x, its
value is really 'abc '. When you then append 'def' to it, you
are actually getting 'abc def', but since @.y is also defined as
CHAR(10), it can only hold the first 10 characters, which are
'abc '. Use VARCHAR(10) instead.|||In addition to Tracy's precise comment, this might help explaining it
further.
select datalength(@.x), datalength(@.x+'def')
-oj
<dan.forest@.matrikon.com> wrote in message
news:1172086921.411352.88210@.v33g2000cwv.googlegroups.com...
> When executing the following statements:
> declare @.x char(10), @.y char(10)
> set @.x = 'abc'
> set @.y = @.x + 'def'
> select @.x
> select @.y
> the results are:
> abc
> abc
> I expect @.y should be equal to 'abcdef'. If I change the var types to
> int for example, then @.y is summed correctly. Can anyone tell me why,
> or what I'm doing wrong? Thanks.
> Dan
>|||On Feb 21, 12:49 pm, "Tracy McKibben" <tracy.mckib...@.gmail.com>
wrote:
> On Feb 21, 1:42 pm, dan.for...@.matrikon.com wrote:
>
>
>
>
>
>
>
>
> @.x is defined as a CHAR(10), and as we know, the CHAR datatype
> includes trailing spaces. When you assign the value 'abc' to @.x, its
> value is really 'abc '. When you then append 'def' to it, you
> are actually getting 'abc def', but since @.y is also defined as
> CHAR(10), it can only hold the first 10 characters, which are
> 'abc '. Use VARCHAR(10) instead.- Hide quoted text -
> - Show quoted text -
Thanks. I wasn't aware of the trailing spaces.
Dan|||On Feb 21, 2:36 pm, dan.for...@.matrikon.com wrote:
> Thanks. I wasn't aware of the trailing spaces.
> Dan
That's the "simplest" way to describe the difference between CHAR and
VARCHAR. CHAR is for fixed-length strings and always contains the
number of characters it's defined for, whereas VARCHAR (variable-CHAR)
is for variable length strings, and only contains what you
specifically put in it.

Concatenating string variables doesn't appear to work properly

When executing the following statements:
declare @.x char(10), @.y char(10)
set @.x = 'abc'
set @.y = @.x + 'def'
select @.x
select @.y
the results are:
abc
abc
I expect @.y should be equal to 'abcdef'. If I change the var types to
int for example, then @.y is summed correctly. Can anyone tell me why,
or what I'm doing wrong? Thanks.
Dan
On Feb 21, 1:42 pm, dan.for...@.matrikon.com wrote:
> When executing the following statements:
> declare @.x char(10), @.y char(10)
> set @.x = 'abc'
> set @.y = @.x + 'def'
> select @.x
> select @.y
> the results are:
> abc
> abc
> I expect @.y should be equal to 'abcdef'. If I change the var types to
> int for example, then @.y is summed correctly. Can anyone tell me why,
> or what I'm doing wrong? Thanks.
> Dan
@.x is defined as a CHAR(10), and as we know, the CHAR datatype
includes trailing spaces. When you assign the value 'abc' to @.x, its
value is really 'abc '. When you then append 'def' to it, you
are actually getting 'abc def', but since @.y is also defined as
CHAR(10), it can only hold the first 10 characters, which are
'abc '. Use VARCHAR(10) instead.
|||In addition to Tracy's precise comment, this might help explaining it
further.
select datalength(@.x), datalength(@.x+'def')
-oj
<dan.forest@.matrikon.com> wrote in message
news:1172086921.411352.88210@.v33g2000cwv.googlegro ups.com...
> When executing the following statements:
> declare @.x char(10), @.y char(10)
> set @.x = 'abc'
> set @.y = @.x + 'def'
> select @.x
> select @.y
> the results are:
> abc
> abc
> I expect @.y should be equal to 'abcdef'. If I change the var types to
> int for example, then @.y is summed correctly. Can anyone tell me why,
> or what I'm doing wrong? Thanks.
> Dan
>
|||On Feb 21, 12:49 pm, "Tracy McKibben" <tracy.mckib...@.gmail.com>
wrote:
> On Feb 21, 1:42 pm, dan.for...@.matrikon.com wrote:
>
>
>
>
>
> @.x is defined as a CHAR(10), and as we know, the CHAR datatype
> includes trailing spaces. When you assign the value 'abc' to @.x, its
> value is really 'abc '. When you then append 'def' to it, you
> are actually getting 'abc def', but since @.y is also defined as
> CHAR(10), it can only hold the first 10 characters, which are
> 'abc '. Use VARCHAR(10) instead.- Hide quoted text -
> - Show quoted text -
Thanks. I wasn't aware of the trailing spaces.
Dan
|||On Feb 21, 2:36 pm, dan.for...@.matrikon.com wrote:
> Thanks. I wasn't aware of the trailing spaces.
> Dan
That's the "simplest" way to describe the difference between CHAR and
VARCHAR. CHAR is for fixed-length strings and always contains the
number of characters it's defined for, whereas VARCHAR (variable-CHAR)
is for variable length strings, and only contains what you
specifically put in it.

Concatenating SQL Scripts

This is a bit off topic for a SQL group but perhaps someone has some
experience with what I am trying to do.
I am going to deployment with dozens of individual object scripts (tables,
procs, etc) . I want to combine all the individual scripts into one large
one so the realease engineer doesn't have to worry about keeping track of
them all.
So basically what I want to do is concatenate all the individual files into
a larger file.
I borrowed some code and patched together a batch file that runs in the
command processor and looks like this:
echo off
chdir C:\Documents and Settings\dave\My Documents\SQL\Deploy
rem Create tables
echo Generate CREATE TABLE SQL script [CreateTables.sql] ...
echo PRINT ' Create tables ...' > CreateTables.sql
cat ./Tables/dbo.table1.tab ./Tables/dbo.table2.tab ./Tables/dbo.table3.tab[vbcol=seagreen]
But this fails on the command "cat" ("'cat' is not recognized as an internal
or external command, operable program or batch file").
Does anyone have any experience with something like this?
How can I concatenate the files through the command line? I can copy them
all to a file but then I have to worry about ordering (or I have to rename
my files).
Thanks for any ideas.
Dave
Try the type command is windows (type * > newfilename.sql...)
cat is on unix...
"Dave" <dave@.nospam.ru> wrote in message
news:e4xIXRYJFHA.1176@.TK2MSFTNGP12.phx.gbl...
> This is a bit off topic for a SQL group but perhaps someone has some
> experience with what I am trying to do.
> I am going to deployment with dozens of individual object scripts (tables,
> procs, etc) . I want to combine all the individual scripts into one large
> one so the realease engineer doesn't have to worry about keeping track of
> them all.
> So basically what I want to do is concatenate all the individual files
> into
> a larger file.
> I borrowed some code and patched together a batch file that runs in the
> command processor and looks like this:
> echo off
> chdir C:\Documents and Settings\dave\My Documents\SQL\Deploy
> rem Create tables
> echo Generate CREATE TABLE SQL script [CreateTables.sql] ...
> echo PRINT ' Create tables ...' > CreateTables.sql
> cat ./Tables/dbo.table1.tab ./Tables/dbo.table2.tab
> ./Tables/dbo.table3.tab
>
> But this fails on the command "cat" ("'cat' is not recognized as an
> internal
> or external command, operable program or batch file").
> Does anyone have any experience with something like this?
> How can I concatenate the files through the command line? I can copy them
> all to a file but then I have to worry about ordering (or I have to rename
> my files).
> Thanks for any ideas.
> Dave
>
>
|||Thanks but when I change to the followingm I get the error: "The syntax of
the command is incorrect."
type ./Tables/dbo.table1.tab ./Tables/dbo.table2.tab ./Tables/dbo.table3.tab
> CreateTables.sql
Can you be more specific on the syntax
"dfate" <dfate2001-rock@.yahoo.com> wrote in message
news:eqWuHUYJFHA.484@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Try the type command is windows (type * > newfilename.sql...)
> cat is on unix...
> "Dave" <dave@.nospam.ru> wrote in message
> news:e4xIXRYJFHA.1176@.TK2MSFTNGP12.phx.gbl...
(tables,[vbcol=seagreen]
large[vbcol=seagreen]
of[vbcol=seagreen]
them[vbcol=seagreen]
rename
>

Concatenating SQL Scripts

This is a bit off topic for a SQL group but perhaps someone has some
experience with what I am trying to do.
I am going to deployment with dozens of individual object scripts (tables,
procs, etc) . I want to combine all the individual scripts into one large
one so the realease engineer doesn't have to worry about keeping track of
them all.
So basically what I want to do is concatenate all the individual files into
a larger file.
I borrowed some code and patched together a batch file that runs in the
command processor and looks like this:
echo off
chdir C:\Documents and Settings\dave\My Documents\SQL\Deploy
rem Create tables
echo Generate CREATE TABLE SQL script [CreateTables.sql] ...
echo PRINT ' Create tables ...' > CreateTables.sql
cat ./Tables/dbo.table1.tab ./Tables/dbo.table2.tab ./Tables/dbo.table3.tab[vbcol=seagreen]
But this fails on the command "cat" ("'cat' is not recognized as an internal
or external command, operable program or batch file").
Does anyone have any experience with something like this?
How can I concatenate the files through the command line? I can copy them
all to a file but then I have to worry about ordering (or I have to rename
my files).
Thanks for any ideas.
DaveTry the type command is windows (type * > newfilename.sql...)
cat is on unix...
"Dave" <dave@.nospam.ru> wrote in message
news:e4xIXRYJFHA.1176@.TK2MSFTNGP12.phx.gbl...
> This is a bit off topic for a SQL group but perhaps someone has some
> experience with what I am trying to do.
> I am going to deployment with dozens of individual object scripts (tables,
> procs, etc) . I want to combine all the individual scripts into one large
> one so the realease engineer doesn't have to worry about keeping track of
> them all.
> So basically what I want to do is concatenate all the individual files
> into
> a larger file.
> I borrowed some code and patched together a batch file that runs in the
> command processor and looks like this:
> echo off
> chdir C:\Documents and Settings\dave\My Documents\SQL\Deploy
> rem Create tables
> echo Generate CREATE TABLE SQL script [CreateTables.sql] ...
> echo PRINT ' Create tables ...' > CreateTables.sql
> cat ./Tables/dbo.table1.tab ./Tables/dbo.table2.tab
> ./Tables/dbo.table3.tab
>
> But this fails on the command "cat" ("'cat' is not recognized as an
> internal
> or external command, operable program or batch file").
> Does anyone have any experience with something like this?
> How can I concatenate the files through the command line? I can copy them
> all to a file but then I have to worry about ordering (or I have to rename
> my files).
> Thanks for any ideas.
> Dave
>
>|||Thanks but when I change to the followingm I get the error: "The syntax of
the command is incorrect."
type ./Tables/dbo.table1.tab ./Tables/dbo.table2.tab ./Tables/dbo.table3.tab
> CreateTables.sql
Can you be more specific on the syntax
"dfate" <dfate2001-rock@.yahoo.com> wrote in message
news:eqWuHUYJFHA.484@.TK2MSFTNGP15.phx.gbl...
> Try the type command is windows (type * > newfilename.sql...)
> cat is on unix...
> "Dave" <dave@.nospam.ru> wrote in message
> news:e4xIXRYJFHA.1176@.TK2MSFTNGP12.phx.gbl...
(tables,[vbcol=seagreen]
large[vbcol=seagreen]
of[vbcol=seagreen]
them[vbcol=seagreen]
rename[vbcol=seagreen]
>

Concatenating SQL Scripts

This is a bit off topic for a SQL group but perhaps someone has some
experience with what I am trying to do.
I am going to deployment with dozens of individual object scripts (tables,
procs, etc) . I want to combine all the individual scripts into one large
one so the realease engineer doesn't have to worry about keeping track of
them all.
So basically what I want to do is concatenate all the individual files into
a larger file.
I borrowed some code and patched together a batch file that runs in the
command processor and looks like this:
echo off
chdir C:\Documents and Settings\dave\My Documents\SQL\Deploy
rem Create tables
echo Generate CREATE TABLE SQL script [CreateTables.sql] ...
echo PRINT ' Create tables ...' > CreateTables.sql
cat ./Tables/dbo.table1.tab ./Tables/dbo.table2.tab ./Tables/dbo.table3.tab
>> CreateTables.sql
But this fails on the command "cat" ("'cat' is not recognized as an internal
or external command, operable program or batch file").
Does anyone have any experience with something like this?
How can I concatenate the files through the command line? I can copy them
all to a file but then I have to worry about ordering (or I have to rename
my files).
Thanks for any ideas.
DaveTry the type command is windows (type * > newfilename.sql...)
cat is on unix...
"Dave" <dave@.nospam.ru> wrote in message
news:e4xIXRYJFHA.1176@.TK2MSFTNGP12.phx.gbl...
> This is a bit off topic for a SQL group but perhaps someone has some
> experience with what I am trying to do.
> I am going to deployment with dozens of individual object scripts (tables,
> procs, etc) . I want to combine all the individual scripts into one large
> one so the realease engineer doesn't have to worry about keeping track of
> them all.
> So basically what I want to do is concatenate all the individual files
> into
> a larger file.
> I borrowed some code and patched together a batch file that runs in the
> command processor and looks like this:
> echo off
> chdir C:\Documents and Settings\dave\My Documents\SQL\Deploy
> rem Create tables
> echo Generate CREATE TABLE SQL script [CreateTables.sql] ...
> echo PRINT ' Create tables ...' > CreateTables.sql
> cat ./Tables/dbo.table1.tab ./Tables/dbo.table2.tab
> ./Tables/dbo.table3.tab
>> CreateTables.sql
>
> But this fails on the command "cat" ("'cat' is not recognized as an
> internal
> or external command, operable program or batch file").
> Does anyone have any experience with something like this?
> How can I concatenate the files through the command line? I can copy them
> all to a file but then I have to worry about ordering (or I have to rename
> my files).
> Thanks for any ideas.
> Dave
>
>|||Thanks but when I change to the followingm I get the error: "The syntax of
the command is incorrect."
type ./Tables/dbo.table1.tab ./Tables/dbo.table2.tab ./Tables/dbo.table3.tab
> CreateTables.sql
Can you be more specific on the syntax
"dfate" <dfate2001-rock@.yahoo.com> wrote in message
news:eqWuHUYJFHA.484@.TK2MSFTNGP15.phx.gbl...
> Try the type command is windows (type * > newfilename.sql...)
> cat is on unix...
> "Dave" <dave@.nospam.ru> wrote in message
> news:e4xIXRYJFHA.1176@.TK2MSFTNGP12.phx.gbl...
> > This is a bit off topic for a SQL group but perhaps someone has some
> > experience with what I am trying to do.
> >
> > I am going to deployment with dozens of individual object scripts
(tables,
> > procs, etc) . I want to combine all the individual scripts into one
large
> > one so the realease engineer doesn't have to worry about keeping track
of
> > them all.
> >
> > So basically what I want to do is concatenate all the individual files
> > into
> > a larger file.
> >
> > I borrowed some code and patched together a batch file that runs in the
> > command processor and looks like this:
> >
> > echo off
> > chdir C:\Documents and Settings\dave\My Documents\SQL\Deploy
> >
> > rem Create tables
> > echo Generate CREATE TABLE SQL script [CreateTables.sql] ...
> >
> > echo PRINT ' Create tables ...' > CreateTables.sql
> >
> > cat ./Tables/dbo.table1.tab ./Tables/dbo.table2.tab
> > ./Tables/dbo.table3.tab
> >> CreateTables.sql
> >
> >
> > But this fails on the command "cat" ("'cat' is not recognized as an
> > internal
> > or external command, operable program or batch file").
> >
> > Does anyone have any experience with something like this?
> >
> > How can I concatenate the files through the command line? I can copy
them
> > all to a file but then I have to worry about ordering (or I have to
rename
> > my files).
> >
> > Thanks for any ideas.
> > Dave
> >
> >
> >
> >
>

Concatenating SQL query results on one line.

Guys,
Here is an example.
I have a table with hors_id, hors_name.
Another table has hors_id, owner_name.
That second table contains multiple entries for that hors_id, as there can be multiple owners.
How do I construct a query that would return the following info on one line:
hors_id, hors_name, owner_name(1), owner_name(2), owner_name(3).
The logic for the solution seems as follows:
Query should return distinct id and name for the horse, loop through the owners in the second table, and append the owners to a variable while the hors_id is the same.
Any suggestions of a generic code to implement?
I tried different coding, so far doesn't work.
Thanks.Originally posted by bigfootguy
Guys,
Here is an example.
I have a table with hors_id, hors_name.
Another table has hors_id, owner_name.
That second table contains multiple entries for that hors_id, as there can be multiple owners.
How do I construct a query that would return the following info on one line:
hors_id, hors_name, owner_name(1), owner_name(2), owner_name(3).
The logic for the solution seems as follows:
Query should return distinct id and name for the horse, loop through the owners in the second table, and append the owners to a variable while the hors_id is the same.
Any suggestions of a generic code to implement?
I tried different coding, so far doesn't work.
Thanks.

Hi BigFoot,

Since SQL Server does not support Cross-Tab constructs, you will have to do some more work. I worked out a solution, but for the Customers and Orders table in the NorthWind demo database; so please translate my answer into your problem.

First of all, you have to know, how much Orders you may expect at least. You can query the actual maximum by

SELECT MAX(Num)
FROM (SELECT COUNT(*) AS Num, CustomerID
FROM Orders
GROUP BY CustomerID) T

As much orders you expect, as much views you have to create:

1) CREATE VIEW Order1 AS SELECT MAX(orderid) AS ID, customerid FROM Orders GROUP BY customerid

2) CREATE VIEW Orders2 AS SELECT MAX(orderid) AS ID, orders.CustomerID FROM Orders, Orders1 WHERE orders.CustomerID = Orders1.CustomerID AND Orders.OrderID < Orders1.ID GROUP BY orders.CustomerID

3) CREATE VIEW Orders3 AS SELECT MAX(orderid) AS ID, orders.CustomerID FROM Orders, Orders2 O WHERE orders.CustomerID = O.CustomerID AND Orders.OrderID < O.ID GROUP BY orders.CustomerID

Got the point? Select one or no order by customer per view, excluding the orders already selected in earlier views.

Having created those views, you may select you required result as :

SELECT C.CustomerID, C.CompanyName, O1.ID AS Order1,
O2.ID AS Order2, O3.ID AS Order3
FROM Customers C LEFT OUTER JOIN
(Orders1 O1 LEFT OUTER JOIN
(Orders2 O2 LEFT OUTER JOIN
Orders3 O3 ON O2.CustomerID = O3.CustomerID) ON
O1.CustomerID = O2.CustomerID) ON
C.CustomerID = O1.CustomerID

This works fine if your expected number of orders can be limited. If not, you will have to write a stored procedure returning your recordset.

Cheers :p|||If your hors_owners table has some field for categoring the owners (say, owner_type) for each hors, you can write a cross-tab query that will place each owner_type in its own column. You could use any type description you want, as long as each hors has at most one of each type. For example, "Primary_Owner", "Secondary_Owner", "Investor", or even an ID like "1", "2", "3"... If you search books on-line for "Crosstab", they show a good example of how to write such a query. If you still have problems, post them to the forum.

If you can't create an owner_type field, well that that is a "hors of a different color". (I couldn't resist...). This is one of those rare situations where I would recommend using a cursor, because you won't need to hard-code the number of owners. If you aren't returning hundreds or thousands of hors records, then consider putting the cursor logic in a user-defined function named something like "udf_HorsOwner_String". Your end-query could then be as simple as:

Select *, dbo.udf_HorsOwnerString(hors_id) from tbl_hors

If you need more guidance, post again when you have an idea of what direction you want to take with this.

blindman

concatenating sql fields and parameters

Hi,
I am trying to run a update stored procedure where one of the fields is
dynamic eg.
UPDATE table
SET field_ + @.number = @.a_value
WHERE (key = @.key_value)
@.number is chosen by the user and the field_## can be anything from field_01
to field_99.
Is there a way i can do this? The above method doesn’t work.
Any help will be greatly appreciated.
Many ThanksIn t-SQL, you will have to use each columns explcitly in the SET clause,
with commas separating each column assignments. For syntax, refer to the
topic UPDATE in SQL Server Books Online.
Perhaps with Dynamic SQL you might be able to kludge it out. For details,
refer to EXEC & sp_ExecuteSQL in SQL Server Books Online. On a side note, it
is possible that you have a flawed design which force you to use such
meaningless constructs in your code.
Anith|||Hi Vortex
consider rewriting as:
EXECUTE('UPDATE table SET field_' + @.number + ' = ' +@.a_value + ' WHERE
key = ' + @.key_value)
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"vortex" wrote:

> Hi,
> I am trying to run a update stored procedure where one of the fields is
> dynamic eg.
> UPDATE table
> SET field_ + @.number = @.a_value
> WHERE (key = @.key_value)
> @.number is chosen by the user and the field_## can be anything from field_
01
> to field_99.
> Is there a way i can do this? The above method doesn’t work.
> Any help will be greatly appreciated.
> Many Thanks
>|||I will give it a try,
I am creating a stored procedure with your update command, if I use the
method you suggested will SQL have to compile the sp every time a new value
is used or will it just compile the once. (Speed is required, that is why I
am using a sp)
Thanks
Khalid
"Chandra" wrote:
> Hi Vortex
> consider rewriting as:
> EXECUTE('UPDATE table SET field_' + @.number + ' = ' +@.a_value + ' WHERE
> key = ' + @.key_value)
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> ---
>
> "vortex" wrote:
>|||If you can suggest a better way of doing it, i would be a very happy bunny a
s
i have a lot more stored procedures to write :(
thanks
"Anith Sen" wrote:

> In t-SQL, you will have to use each columns explcitly in the SET clause,
> with commas separating each column assignments. For syntax, refer to the
> topic UPDATE in SQL Server Books Online.
>
> Perhaps with Dynamic SQL you might be able to kludge it out. For details,
> refer to EXEC & sp_ExecuteSQL in SQL Server Books Online. On a side note,
it
> is possible that you have a flawed design which force you to use such
> meaningless constructs in your code.
> --
> Anith
>
>|||It is not as ease as it seems. For example, Chandra's solution will fail if
@.number is tinyint/int/bigint because you can not those data types have
greater precedence than varchar so sql server will try to convert 'UPDATE
table SET field_' to tinyint/int/bigint and this will give an error. The sam
e
will happen @.a_value, you have to quote it between apostrophes for char /
varchar / datetime values. The same with @.key_value. You will have to use
dynamic sql and bunch on lines to accomodate the statement to the variables
data type.
I will not write about readability and maintenance of your final code, you
can guess what will be the result.
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"vortex" wrote:

> I will give it a try,
> I am creating a stored procedure with your update command, if I use the
> method you suggested will SQL have to compile the sp every time a new valu
e
> is used or will it just compile the once. (Speed is required, that is why
I
> am using a sp)
> Thanks
> Khalid
> "Chandra" wrote:
>
>|||>> If you can suggest a better way of doing it,..
Better way of doing an UPDATE or changing the design? Regarding the UPDATE,
did you refer to the manual for exact syntax?
Regarding the design, with simple one-liners as in your initial post, it is
hard to suggest anything meaningful. Post some more information regarding
this table, the entity type that is being modelled and the attributes
involved. Also provide some details regarding the business model and how
this table fits into the overall schema.
Generally it is hard to provide accurate design suggestions over newsgroup
responses, however with the above requested info, you could perhaps get
started.
Anith

Concatenating rows into one field for summary

Hi,
I am looking for a way of putting multiple values from different rows into
one field, for example:
The dataset returns 3 rows, with two fields, first name and surname like
below:
FirstName Surname
Joe Bloggs
David Beckham
Sue Smith
I want to get all of those surnames, and put them say into the final totals
of the report so they look like Bloggs,Beckham,Smith.
Can anyone see a way of archiving this.
Cheers
LukeYou cant do it in the same query because you will be using the first name if
not then you can use this method. what you can do is to create a seperate
dataset with the following method and refer this dataset in your report in
final totals
select @.aa = COALESCE(surnames + ', ', '') from ..... etc.. etc...ofcourse
you
need to fillup. :-)
any problem let me know.
Amarnath
"lukethepunk" wrote:
> Hi,
> I am looking for a way of putting multiple values from different rows into
> one field, for example:
> The dataset returns 3 rows, with two fields, first name and surname like
> below:
> FirstName Surname
> Joe Bloggs
> David Beckham
> Sue Smith
> I want to get all of those surnames, and put them say into the final totals
> of the report so they look like Bloggs,Beckham,Smith.
> Can anyone see a way of archiving this.
> Cheers
> Luke
>|||Hi,
Thanks thats almost got me what i want (i'd never heard of the COALESCE
function!)
The only problem i've got now is there are duplicate values ending up in the
end string, eg: Bloggs, Bloggs, Smith, Beckham, Beckham
Is there anyway i can keep them out using the sql, or will i have to use
some custom code in the report to keep them uniquie?
Cheers
Luke
"Amarnath" wrote:
> You cant do it in the same query because you will be using the first name if
> not then you can use this method. what you can do is to create a seperate
> dataset with the following method and refer this dataset in your report in
> final totals
> select @.aa = COALESCE(surnames + ', ', '') from ..... etc.. etc...ofcourse
> you
> need to fillup. :-)
> any problem let me know.
> Amarnath
>
> "lukethepunk" wrote:
> > Hi,
> >
> > I am looking for a way of putting multiple values from different rows into
> > one field, for example:
> >
> > The dataset returns 3 rows, with two fields, first name and surname like
> > below:
> >
> > FirstName Surname
> > Joe Bloggs
> > David Beckham
> > Sue Smith
> >
> > I want to get all of those surnames, and put them say into the final totals
> > of the report so they look like Bloggs,Beckham,Smith.
> >
> > Can anyone see a way of archiving this.
> >
> > Cheers
> > Luke
> >|||Hi luke,
unfortunetly there is no such thing like distinct coalesce.. May be what you
can do is to take a distinct and then pass the result set to coalesce. Pl try.
Amarnath
"lukethepunk" wrote:
> Hi,
> Thanks thats almost got me what i want (i'd never heard of the COALESCE
> function!)
> The only problem i've got now is there are duplicate values ending up in the
> end string, eg: Bloggs, Bloggs, Smith, Beckham, Beckham
> Is there anyway i can keep them out using the sql, or will i have to use
> some custom code in the report to keep them uniquie?
> Cheers
> Luke
>
> "Amarnath" wrote:
> > You cant do it in the same query because you will be using the first name if
> > not then you can use this method. what you can do is to create a seperate
> > dataset with the following method and refer this dataset in your report in
> > final totals
> > select @.aa = COALESCE(surnames + ', ', '') from ..... etc.. etc...ofcourse
> > you
> > need to fillup. :-)
> >
> > any problem let me know.
> >
> > Amarnath
> >
> >
> >
> > "lukethepunk" wrote:
> >
> > > Hi,
> > >
> > > I am looking for a way of putting multiple values from different rows into
> > > one field, for example:
> > >
> > > The dataset returns 3 rows, with two fields, first name and surname like
> > > below:
> > >
> > > FirstName Surname
> > > Joe Bloggs
> > > David Beckham
> > > Sue Smith
> > >
> > > I want to get all of those surnames, and put them say into the final totals
> > > of the report so they look like Bloggs,Beckham,Smith.
> > >
> > > Can anyone see a way of archiving this.
> > >
> > > Cheers
> > > Luke
> > >|||Hi,
I have managed to get it working by passing the results to some custom code,
and looping through the string and sending back only one of each.
Thanks for your help
Luke
"Amarnath" wrote:
> Hi luke,
> unfortunetly there is no such thing like distinct coalesce.. May be what you
> can do is to take a distinct and then pass the result set to coalesce. Pl try.
> Amarnath
> "lukethepunk" wrote:
> > Hi,
> >
> > Thanks thats almost got me what i want (i'd never heard of the COALESCE
> > function!)
> >
> > The only problem i've got now is there are duplicate values ending up in the
> > end string, eg: Bloggs, Bloggs, Smith, Beckham, Beckham
> >
> > Is there anyway i can keep them out using the sql, or will i have to use
> > some custom code in the report to keep them uniquie?
> >
> > Cheers
> > Luke
> >
> >
> > "Amarnath" wrote:
> >
> > > You cant do it in the same query because you will be using the first name if
> > > not then you can use this method. what you can do is to create a seperate
> > > dataset with the following method and refer this dataset in your report in
> > > final totals
> > > select @.aa = COALESCE(surnames + ', ', '') from ..... etc.. etc...ofcourse
> > > you
> > > need to fillup. :-)
> > >
> > > any problem let me know.
> > >
> > > Amarnath
> > >
> > >
> > >
> > > "lukethepunk" wrote:
> > >
> > > > Hi,
> > > >
> > > > I am looking for a way of putting multiple values from different rows into
> > > > one field, for example:
> > > >
> > > > The dataset returns 3 rows, with two fields, first name and surname like
> > > > below:
> > > >
> > > > FirstName Surname
> > > > Joe Bloggs
> > > > David Beckham
> > > > Sue Smith
> > > >
> > > > I want to get all of those surnames, and put them say into the final totals
> > > > of the report so they look like Bloggs,Beckham,Smith.
> > > >
> > > > Can anyone see a way of archiving this.
> > > >
> > > > Cheers
> > > > Luke
> > > >

Concatenating result sets

Hello,
I have a stored procedure that calls itself recursively. At each step a new
result set is generated so when the query completes I get a bunch of
individual result sets, each containing one line. The problem is that the
query takes quit a bit of time(about half a minute) and I get a "Resource is
low, some results are dropped" message when testing in the Query analyzer.
Is there a way of concatenating these result sets in a way the UNION
statement does? I'm thinking of trying a temporary table.
Cheerstemp table would achieve what you're trying to do, but are you sure you
need to be doing it? when you're getting into recursive stored
procedures and loads of little result sets you're using SQL for
something it wasn't really intended for (in my opinion, and yes, other
people have their own differing opinion).
are you able to say a bit more about the query, it may be possible to
do it without recursion.
Cheers
Will|||temporary table is the only way to do it. and the result from the stored pro
c
cannot be recursively stored into a temporary table because INSERT EXEC
cannot be nested.
hope this helps|||What I'm trying to do is read a tree in a way that I could build a certain
hierarchical data structure in my C# code.
The table structure is simple: a node ID, a node description and the ID of
the parent node. The C# object is a class that contains an ID, a description
and an array of children(of the same type).
I thought of recursively read the tree and again recursively build the C#
object structure.
"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1144081590.593096.155650@.g10g2000cwb.googlegroups.com...
> temp table would achieve what you're trying to do, but are you sure you
> need to be doing it? when you're getting into recursive stored
> procedures and loads of little result sets you're using SQL for
> something it wasn't really intended for (in my opinion, and yes, other
> people have their own differing opinion).
> are you able to say a bit more about the query, it may be possible to
> do it without recursion.
> Cheers
> Will
>|||it's better to read all data from table and build tree in C# in single
iteration over records in dataset.
alternatively, implement recursion in c# passing dataset as parameter and
looking for relevant records.
peter|||Hi, Gabriel
If you use (or you are planning to use) SQL Server 2005, you should
take a look at recursive CTE-s, that solve this problem in a very
elegant way:
http://msdn.microsoft.com/msdnmag/i...TSQLinYukon/#S7
http://msdn2.microsoft.com/en-US/library/ms186243.aspx
http://msdn.microsoft.com/library/e...TSQLEnhance.asp
Razvan|||Yeah I think that may be a bit wiser. I got a bit excited with the recursion
from a programmer's point of view but I guess resources are also to be
thaught about besides algorithms...
Thanks
"Rogas69" <rogas69@.no_spamers.o2.ie> wrote in message
news:e%23cgoA0VGHA.5468@.TK2MSFTNGP14.phx.gbl...
> it's better to read all data from table and build tree in C# in single
> iteration over records in dataset.
> alternatively, implement recursion in c# passing dataset as parameter and
> looking for relevant records.
> peter
>|||Look at Razvan's post below. This logic can be done with SQL without using
recursive stored procedures. It is not only more elegant, as Razvan puts
it, but much more efficient.
"Gabriel Lacatus" <cyberdude@.nospam.nospam> wrote in message
news:eKmUaF0VGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Yeah I think that may be a bit wiser. I got a bit excited with the
recursion
> from a programmer's point of view but I guess resources are also to be
> thaught about besides algorithms...
> Thanks
> "Rogas69" <rogas69@.no_spamers.o2.ie> wrote in message
> news:e%23cgoA0VGHA.5468@.TK2MSFTNGP14.phx.gbl...
and
>|||And if not on SQL 2005 yet, maybe this example can be of help:
http://milambda.blogspot.com/2005/0...or-monkeys.html
ML
http://milambda.blogspot.com/