Concatenate strings in group column

I have following problem:
I want to display a concatenated string in a group row of a report.
The grouping works fine, i can "sum" numerical values etc. as
expected, but i could not find a way to concatenate strings (from the
rows that make up the group). "Join(...)" does not work (gives me an
error message).
I can use "First(...) & Last(...)" to concatenate the string columns
from the first and last row of the group, but couldn't find a way to
concatenate the string columns from all rows...
I am using SQL Server 2005 / Visual Studio 2005.I use a CLR aggregate directly in SQL Server. Hopes your datasource is SQL
Server 2005.
In reports, I use Replace function to change my separator character.
--
Jean-Pierre Riehl
http://blog.djeepy1.net
http://www.bewise.fr
"PR" <pr_gg@.egal.de> wrote in message
news:76e4156a-8805-4e71-927a-7f2c848f295d@.q77g2000hsh.googlegroups.com...
>I have following problem:
> I want to display a concatenated string in a group row of a report.
> The grouping works fine, i can "sum" numerical values etc. as
> expected, but i could not find a way to concatenate strings (from the
> rows that make up the group). "Join(...)" does not work (gives me an
> error message).
> I can use "First(...) & Last(...)" to concatenate the string columns
> from the first and last row of the group, but couldn't find a way to
> concatenate the string columns from all rows...
> I am using SQL Server 2005 / Visual Studio 2005.
>
>|||On Jan 17, 8:21 am, "Jean-Pierre Riehl" <jean-pierre.ri...@.bewise.fr>
wrote:
> I use a CLR aggregate directly in SQL Server. Hopes your datasource is SQL
> Server 2005.
> In reports, I use Replace function to change my separator character.
> --
> Jean-Pierre Riehlhttp://blog.djeepy1.nethttp://www.bewise.fr
> "PR" <pr...@.egal.de> wrote in message
> news:76e4156a-8805-4e71-927a-7f2c848f295d@.q77g2000hsh.googlegroups.com...
>
thanks for the idea. I do use SQL Server 2005, however, since this is
a "group" column in a report, i can't use SQL to concatenate the
strings as i deal with a grouped subset of the sql query result...|||On Jan 16, 9:31=A0am, PR <pr...@.egal.de> wrote:
> I have following problem:
> I want to display a concatenated string in a group row of a report.
> The grouping works fine, i can "sum" numerical values etc. as
> expected, but i could not find a way to concatenate strings (from the
> rows that make up the group). "Join(...)" does not work (gives me an
> error message).
> I can use "First(...) & Last(...)" =A0to concatenate the string columns
> from the first and last row of the group, but couldn't find a way to
> concatenate the string columns from all rows...
> I am using SQL Server 2005 / Visual Studio 2005.
I'm having a hard time visualizing what you want to display. Do you
want your concantenation in each detail row or just in a group header?
Can you mock up an example?|||On Jan 21, 4:06 pm, toolman <t...@.infocision.com> wrote:
> I'm having a hard time visualizing what you want to display. Do you
> want your concantenation in each detail row or just in a group header?
> Can you mock up an example?
I want the concatenation in each detail row. I have some group "sum"s
that work fine, and want to do the same for a string. Basically it's
just a normal grouping of columns with group sums etc.. just that i
need to concatenate a string besides building the group sums.|||On Jan 22, 4:23=A0am, PR <pr...@.egal.de> wrote:
> On Jan 21, 4:06 pm, toolman <t...@.infocision.com> wrote:
> > I'm having a hard time visualizing what you want to display. =A0Do you
> > want your concantenation in each detail row or just in a group header?
> > Can you mock up an example?
> I want the concatenation in each detail row. I have some group "sum"s
> that work fine, and want to do the same for a string. Basically it's
> just a normal grouping of columns with group sums etc.. just that i
> need to concatenate a string besides building the group sums.
Is this what you want?
=3D"String Expression " & SUM(Fields!Name.Value)
It would look something like:
String Expression 1234.56
If you're wanting to concantenate actual field values, say like if
you're grouping on a combination of fields then:
=3DFields!GroupField1.Value & ", " & Fields!GroupField2.Value
would give you something like City, State or Company, Division
HTH|||On Jan 22, 8:57 pm, toolman <t...@.infocision.com> wrote:
> Is this what you want?
> ="String Expression " & SUM(Fields!Name.Value)
> It would look something like:
> String Expression 1234.56
> If you're wanting to concantenate actual field values, say like if
> you're grouping on a combination of fields then:
> =Fields!GroupField1.Value & ", " & Fields!GroupField2.Value
> would give you something like City, State or Company, Division
> HTH
Sorry, no, i don't want to concatenate strings with the sum value of
the group.
I have a grouping in the report, where a sum value (i.e. =Sum(Fields!
Name.NumValue)) is written to one textfield of the row.
What i want to do is concatenate a string from the groups result set -
pretty much like the "Sum" sums up the numerical values from the
result set over the rows. If "Join" would work it would look like
=Join(Fields!Name.StringValue) for the other text field.. but
unfortunately that does not work.
Any ideas ?|||On Jan 23, 3:50=A0am, PR <pr...@.egal.de> wrote:
> On Jan 22, 8:57 pm, toolman <t...@.infocision.com> wrote:
>
> > Is this what you want?
> > =3D"String Expression " & SUM(Fields!Name.Value)
> > It would look something like:
> > String Expression 1234.56
> > If you're wanting to concantenate actual field values, say like if
> > you're grouping on a combination of fields then:
> > =3DFields!GroupField1.Value & ", " & Fields!GroupField2.Value
> > would give you something like City, State or Company, Division
> > HTH
> Sorry, no, i don't want to concatenate strings with the sum value of
> the group.
> I have a grouping in the report, where a sum value (i.e. =3DSum(Fields!
> Name.NumValue)) is written to one textfield of the row.
> What i want to do is concatenate a string from the groups result set -
> pretty much like the "Sum" sums up the numerical values from the
> result set over the rows. If "Join" would work it would look like
> =3DJoin(Fields!Name.StringValue) for the other text field.. but
> unfortunately that does not work.
> Any ideas ?
I think if you can get your Fields!Name.StringValue values into an
array, you could then use Join() to get what you want. Unfortunately,
I'm not enough of a VB or .NET guy to guide you through that.
Hopefully someone else can jump at this point. Sorry I can't get you
further.|||On Jan 23, 5:03 pm, toolman <t...@.infocision.com> wrote:
> I think if you can get your Fields!Name.StringValue values into an
> array, you could then use Join() to get what you want. Unfortunately,
> I'm not enough of a VB or .NET guy to guide you through that.
> Hopefully someone else can jump at this point. Sorry I can't get you
> further.
Thanks for your effort. Yes, Join() requires an array.. and i have no
idea how to convert the string values into an array to do that (and
could not find anything about it in the documentation).|||Hello, PR.
Did you find any solution to your problem? I have the same issue and I can't
find any solution for this...
Thank you.
--
Alexandra
"PR" wrote:
> On Jan 23, 5:03 pm, toolman <t...@.infocision.com> wrote:
> >
> > I think if you can get your Fields!Name.StringValue values into an
> > array, you could then use Join() to get what you want. Unfortunately,
> > I'm not enough of a VB or .NET guy to guide you through that.
> > Hopefully someone else can jump at this point. Sorry I can't get you
> > further.
> Thanks for your effort. Yes, Join() requires an array.. and i have no
> idea how to convert the string values into an array to do that (and
> could not find anything about it in the documentation).
>|||On Feb 22, 12:13 pm, Alexandra Ribeiro
<AlexandraRibe...@.discussions.microsoft.com> wrote:
> Hello, PR.
> Did you find any solution to your problem? I have the same issue and I can't
> find any solution for this...
> Thank you.
> --
> Alexandra
>
Hi Alexandra,
no, i haven't found a solution for this. Maybe using a sub-report is
an option, but i haven't tried this yet.sqlsql

Concatenate strings from different rows

Hello.

I have a table like:

Id Name Description OrderId
1 Microsoft This is a 0
1 Microsoft huge company. 1

I need to create a select query that will concatenate description for both entries and output it like

Microsoft - This is a huge company.

Try using the USE XML PATH () as part of the select statement; maybe something like:

Code Snippet

declare @.testData table
( id integer,
[Name] varchar(10),
Description varchar(15),
orderId integer
)
insert into @.testData
select 1, 'Microsoft', 'This is a', 0 union all
select 1, 'Microsoft', 'huge company.', 1

select distinct
[Name] + ' - ' +
( select description + ' ' as [text()]
from @.testData b
where a.id = b.id
order by orderId
for xml path ('')
) as outputString
from @.testData a

/*
outputString
--
Microsoft - This is a huge company.
*/

Concatenate strings after assigning text in place of bit strings

I have a whole bunch of bit fields in an SQL data base, which makes it a little messy to report on.

I thought a nice idea would be to assigne a text string/null value to each bit field and concatenate all of them into a result.

This is the basic logic goes soemthing like this:

select case new_accountant = 1 then 'acct/' end +

case new_advisor = 1 then 'adv/' end +

case new_attorney = 1 then 'atty/' end as String

from new_database

The output would be

Null, acct/, adv/, atty, acct/adv/, acct/atty/... acct/adv/atty/

So far, nothing I have tried has worked.

Any ideas?

I solved my own problem:

I needed to include an else statement

select fullname,
case when new_accountant = 1 then 'acct/' else '' end +
case when new_advisor = 1 then 'adv/' else '' end +
case when new_attorney = 1 then 'atty/' else '' end as Str1
from dbo.newdatabase

|||

I solved my own problem:

select fullname,
case when new_accountant = 1 then 'acct/' else '' end +
case when new_advisor = 1 then 'adv/' else '' end +
case when new_attorney = 1 then 'atty/' else '' end as Str1
from dbo.database

Concatenate String and Pass to FORMSOF?

The following query works perfectly (returning all words on a list called
"Dolch" that do not contain a form of "doing"):
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "doing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
However, what I really want to do requires me to piece two strings together,
resulting in a word like "doing". Any time I try to concatinate strings to
get this parameter, I get an error.
For example:
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "do' + 'ing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
I have also tried using & (as in "do' & 'ing") and various forms of single
and double quotes. Does anyone know a combination that will work?
FYI, in case this query looks goofy because of the unused "CombinedLexicons"
table, it is because the end result should be a working form of the
following...
Figuring out the string concatination is just a step toward this goal:
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
Thanks!
Have you tried using a variable
Something like
DECLARE @.String varchar(100)
SET @.String = 'do' + 'ing'
SET @.String = ' FORMSOF (INFLECTIONAL,"' + @.String + '")'
--PRINT @.String
SELECT ..........................
WHERE CONTAINS(dbo.Dolch.vchWord, @.String)
Andy
"HumanJHawkins" wrote:

> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings together,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused "CombinedLexicons"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>
>
>
|||Would this work for you?
declare @.string varchar(2000)
declare @.searchphrase varchar(200)
set @.searchphrase='do ing'
set @.string='SELECT [Dolch] AS [List Name], dbo.Dolch.vchWord '
select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
select @.string=@.string+ ' dbo.CombinedLexicons ON
CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
select @.string=@.string +replace(@.searchphrase,' ','')
select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
print @.string
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HumanJHawkins" <NoSpam@.NoSpam.Net> wrote in message
news:ZGmJd.5174$r27.4041@.newsread1.news.pas.earthl ink.net...
> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings
together,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused
"CombinedLexicons"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>
>
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OrzwtawAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Would this work for you?
>
> declare @.string varchar(2000)
> declare @.searchphrase varchar(200)
> set @.searchphrase='do ing'
> set @.string='SELECT [Dolch] AS [List Name], dbo.Dolch.vchWord '
> select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
> select @.string=@.string+ ' dbo.CombinedLexicons ON
> CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
> select @.string=@.string +replace(@.searchphrase,' ','')
> select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
> print @.string
It's taking me a while to see if this will work. Thanks for the suggestion.
It looks like a good path to take.

Concatenate String and Pass to FORMSOF?

The following query works perfectly (returning all words on a list called
"Dolch" that do not contain a form of "doing"):
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "doing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
However, what I really want to do requires me to piece two strings together,
resulting in a word like "doing". Any time I try to concatinate strings to
get this parameter, I get an error.
For example:
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "do' + 'ing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
I have also tried using & (as in "do' & 'ing") and various forms of single
and double quotes. Does anyone know a combination that will work?
FYI, in case this query looks goofy because of the unused "CombinedLexicons"
table, it is because the end result should be a working form of the
following...
Figuring out the string concatination is just a step toward this goal:
SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)
Thanks!Have you tried using a variable
Something like
DECLARE @.String varchar(100)
SET @.String = 'do' + 'ing'
SET @.String = ' FORMSOF (INFLECTIONAL,"' + @.String + '")'
--PRINT @.String
SELECT ..........................
WHERE CONTAINS(dbo.Dolch.vchWord, @.String)
Andy
"HumanJHawkins" wrote:

> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings togethe
r,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused "CombinedLexicon
s"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>
>
>|||Would this work for you?
declare @.string varchar(2000)
declare @.searchphrase varchar(200)
set @.searchphrase='do ing'
set @.string='SELECT [Dolch] AS [List Name], dbo.Dolch.vchWord '
select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
select @.string=@.string+ ' dbo.CombinedLexicons ON
CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
select @.string=@.string +replace(@.searchphrase,' ','')
select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
print @.string
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HumanJHawkins" <NoSpam@.NoSpam.Net> wrote in message
news:ZGmJd.5174$r27.4041@.newsread1.news.pas.earthlink.net...
> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings
together,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused
"CombinedLexicons"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>
>
>|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OrzwtawAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Would this work for you?
>
> declare @.string varchar(2000)
> declare @.searchphrase varchar(200)
> set @.searchphrase='do ing'
> set @.string='SELECT [Dolch] AS [List Name], dbo.Dolch.vchWord '
> select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
> select @.string=@.string+ ' dbo.CombinedLexicons ON
> CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
> select @.string=@.string +replace(@.searchphrase,' ','')
> select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
> print @.string
It's taking me a while to see if this will work. Thanks for the suggestion.
It looks like a good path to take.sqlsql

Concatenate String and Pass to FORMSOF?

The following query works perfectly (returning all words on a list called
"Dolch" that do not contain a form of "doing"):

SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "doing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)

However, what I really want to do requires me to piece two strings together,
resulting in a word like "doing". Any time I try to concatinate strings to
get this parameter, I get an error.

For example:

SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, "do' + 'ing")')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)

I have also tried using & (as in "do' & 'ing") and various forms of single
and double quotes. Does anyone know a combination that will work?

FYI, in case this query looks goofy because of the unused "CombinedLexicons"
table, it is because the end result should be a working form of the
following...
Figuring out the string concatination is just a step toward this goal:

SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
FROM dbo.Dolch LEFT OUTER JOIN
dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
WHERE (dbo.CombinedLexicons.vchWord IS NULL)

Thanks!Would this work for you?

declare @.string varchar(2000)
declare @.searchphrase varchar(200)
set @.searchphrase='do ing'
set @.string='SELECT [Dolch] AS[List Name], dbo.Dolch.vchWord '
select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
select @.string=@.string+ ' dbo.CombinedLexicons ON
CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
select @.string=@.string +replace(@.searchphrase,' ','')
select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
print @.string

--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HumanJHawkins" <NoSpam@.NoSpam.Net> wrote in message
news:ZGmJd.5174$r27.4041@.newsread1.news.pas.earthl ink.net...
> The following query works perfectly (returning all words on a list called
> "Dolch" that do not contain a form of "doing"):
> SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "doing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> However, what I really want to do requires me to piece two strings
together,
> resulting in a word like "doing". Any time I try to concatinate strings to
> get this parameter, I get an error.
> For example:
> SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, "do' + 'ing")')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> I have also tried using & (as in "do' & 'ing") and various forms of single
> and double quotes. Does anyone know a combination that will work?
> FYI, in case this query looks goofy because of the unused
"CombinedLexicons"
> table, it is because the end result should be a working form of the
> following...
> Figuring out the string concatination is just a step toward this goal:
> SELECT 'Dolch' AS[List Name], dbo.Dolch.vchWord
> FROM dbo.Dolch LEFT OUTER JOIN
> dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord,
> 'FORMSOF(INFLECTIONAL, ' + dbo.CombinedLexicons.vchWord + ')')
> WHERE (dbo.CombinedLexicons.vchWord IS NULL)
> Thanks!
>|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OrzwtawAFHA.1388@.TK2MSFTNGP09.phx.gbl...
> Would this work for you?
>
> declare @.string varchar(2000)
> declare @.searchphrase varchar(200)
> set @.searchphrase='do ing'
> set @.string='SELECT [Dolch] AS[List Name], dbo.Dolch.vchWord '
> select @.string=@.string+ ' FROM dbo.Dolch LEFT OUTER JOIN'
> select @.string=@.string+ ' dbo.CombinedLexicons ON
> CONTAINS(dbo.Dolch.vchWord,''FORMSOF(INFLECTIONAL, '
> select @.string=@.string +replace(@.searchphrase,' ','')
> select @.string=@.string +')'' WHERE (dbo.CombinedLexicons.vchWord IS NULL)'
> print @.string

It's taking me a while to see if this will work. Thanks for the suggestion.
It looks like a good path to take.

Concatenate string

Please help me with this if you can.
I have one table with CustomerID and some other data.
In other table i have CustomerID(the link with the first table) and Agent
The relation of the first with the second one is ONE TO MANY.
I want something like this:
Customer,'Agent1,Agent2,Agent3'

Is it possible.
Please help :)looks like you are trying to reverse pivot...

1) Are there always three agent rows per customer id ?
2) Is there a field which identifies the agent number per customer id?

throw up the ddl to these tables|||First Table
CustomerID|CustomerName

SecondTable
CustomerID|AgentName

Ex Records:

First Table
1| Gigi
2|Vasile

Second Table
1|Ionescu
1|Vasilescu
1|George
2|Marin
2|Preda

I want:
1|'Gigi'|'Ionescu,Vasilescu,George'
2|'Vasile'|'Marin,Preda'|||I did find a solution.
But i'm not sure that is the best one.
I use a function in MS SQL Server and a Cusor inside.
Here it is the function.
Anyway, i know that Cursors are slowly so please give me another solution if you know.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER FUNCTION admin_GetAgentsNames (@.CustomerId int)
RETURNS varchar(1000)
AS

BEGIN
DECLARE @.AgentsNames VARCHAR(1000)
DECLARE @.AgentFName VARCHAR(50)
DECLARE @.AgentLName VARCHAR(50)
DECLARE @.AgentId INT
SET @.AgentsNames = ''
DECLARE curGetAgents CURSOR FOR
SELECT DISTINCT AgentId,ISNULL(c.FNAME,''),ISNULL(c.LNAME,'')
FROM tbl_customerToLocalAgent_Pass AS a
JOIN tbl_Customer AS b ON a.AgentId=b.CustomerId
JOIN tbl_CustomerDetails AS c ON b.CustomerId=c.CustomerId
AND b.PreferredLanguageId=c.LanguageId
WHERE a.CustomerId=@.CustomerId

--Opent the cursor
OPEN curGetAgents
--Fetch the first record
FETCH NEXT FROM curGetAgents INTO @.AgentId,@.AgentFName,@.AgentLName

WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
--Concatenate the names
SET @.AgentsNames = @.AgentsNames + ',' + @.AgentFName + ' ' + @.AgentLName
--Get the next row
FETCH NEXT FROM curGetAgents INTO @.AgentId,@.AgentFName,@.AgentLName
END
--Close cursor
CLOSE curGetAgents
DEALLOCATE curGetAgents

RETURN(RIGHT(@.AgentsNames,LEN(@.AgentsNames)))

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO