Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create string from table Expand / Collapse
Author
Message
Posted Saturday, July 19, 2008 3:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 30, 2012 9:00 PM
Points: 7, Visits: 93
Comments posted to this topic are about the item Create string from table
Post #537142
Posted Monday, September 15, 2008 6:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 6:56 AM
Points: 1,330, Visits: 1,434
Hello,

Any chance you could post the Create Table statements to go with your script? Also some sample data Inserts would be useful.

Regards,

John Marsh


www.sql.lu
SQL Server Luxembourg User Group
Post #569393
Posted Monday, September 15, 2008 4:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 30, 2012 9:00 PM
Points: 7, Visits: 93
Hi John - I could, but would that really be useful here? Useful enough to warrant creating a table full of fake data? Or do you think that the example is otherwise not clear enough?
Post #569866
Posted Monday, September 15, 2008 9:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 36,767, Visits: 31,223
Dave (9/15/2008)
Hi John - I could, but would that really be useful here? Useful enough to warrant creating a table full of fake data? Or do you think that the example is otherwise not clear enough?


Yeaup... it would be useful here...


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #569908
Posted Monday, September 15, 2008 10:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, September 30, 2012 9:00 PM
Points: 7, Visits: 93
I looked for a way of modifying the original, but couldn't find it, so I'll just make it available here.

CREATE TABLE [db].[table1] (
[columnA] [numeric](18, 0) NOT NULL ,
[columnb] [ntext] COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
INSERT [db].[table1] VALUES (1, 'Michael.Jordan@domain.com')
INSERT [db].[table1] VALUES (2, 'Red')
INSERT [db].[table1] VALUES (3, 'Lebron.James@domain.com')
INSERT [db].[table1] VALUES (4, 'Black')
INSERT [db].[table1] VALUES (5, 'Kobe.Bryant@domain.com')
INSERT [db].[table1] VALUES (6, 'Purple')
INSERT [db].[table1] VALUES (7, 'Earvin.Johnson@domain.com')
INSERT [db].[table1] VALUES (8, 'Yellow')
INSERT [db].[table1] VALUES (9, 'Charles.Barkely@domain.com')
INSERT [db].[table1] VALUES (10, 'White')
GO

CREATE TABLE [db].[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 [db].[table2] VALUES (1, 'properties', 1, 'email', 5)
INSERT [db].[table2] VALUES (2, 'properties', 1, 'clubcolour', 5)
INSERT [db].[table2] VALUES (3, 'properties', 1, 'email', 5)
INSERT [db].[table2] VALUES (4, 'properties', 1, 'clubcolour', 5)
INSERT [db].[table2] VALUES (5, 'properties', 1, 'email', 5)
INSERT [db].[table2] VALUES (6, 'properties', 1, 'clubcolour', 5)
INSERT [db].[table2] VALUES (7, 'properties', 1, 'email', 5)
INSERT [db].[table2] VALUES (8, 'properties', 1, 'clubcolour', 5)
INSERT [db].[table2] VALUES (9, 'properties', 1, 'email', 5)
INSERT [db].[table2] VALUES (10, 'properties', 1, 'clubcolour', 5)
GO

Post #569926
Posted Monday, September 15, 2008 11:25 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 36,767, Visits: 31,223
Cool! I'd guessed wrong. ColumnC in the orginal code wasn't prefixed by a table alias and I'd guessed it was in Table1. Thanks.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #569934
Posted Tuesday, September 16, 2008 4:39 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 6:56 AM
Points: 1,330, Visits: 1,434
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


www.sql.lu
SQL Server Luxembourg User Group
Post #570069
Posted Tuesday, September 16, 2008 7:09 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:22 AM
Points: 36,767, Visits: 31,223
Ya beat me to it, John. :)

Not 100% sure but, using your good data, I think example 1 was meant to be something on the order of...

-- Example 1
DECLARE @EMail VARCHAR(8000)

SELECT @EMail = COALESCE(@EMail + ', ', '') + t1.ColumnB
FROM dbo.Table1 t1
INNER JOIN dbo.Table2 t2 ON t1.ColumnA = t2.ColumnA
WHERE t2.ColumnD = 'EMail'

SELECT @EMail
GO

...and the output was meant to be something like this...

---------------------------------------------------------------------------------------------------------------------------------
Michael.Jordan@domain.com, Lebron.James@domain.com, Kobe.Bryant@domain.com, Earvin.Johnson@domain.com, Charles.Barkely@domain.com
(1 row(s) affected)



--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #570705
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse