Hello Again,

Thanks for posting the extra stuff.

The reason why I asked is, I like to try out the featured scripts, but like everyone else, I don’t have a lot of time to devote to them. So I appreciate anything the author can do to make it easier. (At this point I would also suggest considering using the "dbo" schema for public posts).

With your table creation and population code in place, I then got the following errors:-

Msg 402, Level 16, State 1, Line 6

The data types ntext and varchar are incompatible in the equal to operator.

Msg 207, Level 16, State 1, Line 6

Invalid column name 'columnC'.

Msg 402, Level 16, State 1, Line 5

The data types ntext and varchar are incompatible in the equal to operator.

Msg 207, Level 16, State 1, Line 5

Invalid column name 'columnC'.

I had to change table1.columnb to be an nvarchar, and also standardise the "columnc" casing (unfortunately, we have case-sensitive servers). This resulted in no errors, but empty result sets.

By changing the where clause to "WHERE dbo.table2.columnb = 'properties'" I believe I am now getting the results that you expect.

Below is my amended version of your script. May be yiou could try it out in your environment and let me know?

Regards,

John Marsh

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table1]') AND type in (N'U'))

DROP TABLE [dbo].[table1]

Go

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table2]') AND type in (N'U'))

DROP TABLE [dbo].[table2]

Go

CREATE TABLE dbo.[table1] (

[columnA] [numeric](18, 0) NOT NULL ,

[columnb] [nvarchar](4000) COLLATE Latin1_General_CI_AS NULL

-- [columnb] [ntext] COLLATE Latin1_General_CI_AS NULL

) ON [PRIMARY] -- TEXTIMAGE_ON [PRIMARY]

INSERT dbo.[table1] VALUES (1, 'Michael.Jordan@domain.com')

INSERT dbo.[table1] VALUES (2, 'Red')

INSERT dbo.[table1] VALUES (3, 'Lebron.James@domain.com')

INSERT dbo.[table1] VALUES (4, 'Black')

INSERT dbo.[table1] VALUES (5, 'Kobe.Bryant@domain.com')

INSERT dbo.[table1] VALUES (6, 'Purple')

INSERT dbo.[table1] VALUES (7, 'Earvin.Johnson@domain.com')

INSERT dbo.[table1] VALUES (8, 'Yellow')

INSERT dbo.[table1] VALUES (9, 'Charles.Barkely@domain.com')

INSERT dbo.[table1] VALUES (10, 'White')

GO

CREATE TABLE dbo.[table2] (

[columnA] [numeric](18, 0) NOT NULL ,

[columnb] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,

[columnc] [numeric](18, 0) NULL ,

[columnd] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,

[columne] [int] NULL

) ON [PRIMARY]

INSERT dbo.[table2] VALUES (1, 'properties', 1, 'email', 5)

INSERT dbo.[table2] VALUES (2, 'properties', 1, 'clubcolour', 5)

INSERT dbo.[table2] VALUES (3, 'properties', 1, 'email', 5)

INSERT dbo.[table2] VALUES (4, 'properties', 1, 'clubcolour', 5)

INSERT dbo.[table2] VALUES (5, 'properties', 1, 'email', 5)

INSERT dbo.[table2] VALUES (6, 'properties', 1, 'clubcolour', 5)

INSERT dbo.[table2] VALUES (7, 'properties', 1, 'email', 5)

INSERT dbo.[table2] VALUES (8, 'properties', 1, 'clubcolour', 5)

INSERT dbo.[table2] VALUES (9, 'properties', 1, 'email', 5)

INSERT dbo.[table2] VALUES (10, 'properties', 1, 'clubcolour', 5)

GO

-- Example 1

DECLARE @email NVARCHAR(250)

BEGIN

SELECT @email = COALESCE(@email + ', ', '') + (CAST(columnd AS varchar(30)))

FROM dbo.table1 INNER JOIN dbo.table2 ON dbo.table1.columnA = dbo.table2.columnA

WHERE dbo.table2.columnb = 'properties'

SELECT @email

END

GO

-- Example 2

DECLARE @email VARCHAR(250)

BEGIN

SELECT @email = COALESCE(@email + ', ', '') + '''' + (CAST(columnd AS varchar(30))) + ''''

FROM dbo.table1 INNER JOIN dbo.table2 ON dbo.table1.columnA = dbo.table2.columnA

WHERE dbo.table2.columnb = 'properties'

SET @email = '[' + @email + ']'

SELECT @email

END

GO