Consolidate Rows on Select

I want to condense several rows of a table using a select
statement, function, or stored procedure
For Example
mytable :
1 a
2 b
2 c
2 d
3 a
so that my select should result in
1 a
2 b,c,d
3 a
Any ideas or suggestions?
While this code references the sample database in my book (CHA2), the
pattern will work for your problem as well. In this example, the @.EventDates
variable is used to gather, or denomalize, the EventDate column:
USE CHA2
DECLARE
@.EventDates VARCHAR(1024)
SET @.EventDates = ''
SELECT @.EventDates = @.EventDates + CONVERT(VARCHAR(15), a.d,107 ) + '; '
FROM (Select DateBegin as [d] FROM Event
JOIN Tour
ON Event.TourID = Tour.TourID
WHERE Tour.[Name] = 'Outer Banks Lighthouses') as a
SELECT Left(@.EventDates, Len(@.EventDates)-1) AS 'Outer Banks Lighthouses
Events'
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:392801c4aa4b$477a6810$a401280a@.phx.gbl...
>I want to condense several rows of a table using a select
> statement, function, or stored procedure
> For Example
> mytable :
> 1 a
> 2 b
> 2 c
> 2 d
> 3 a
> so that my select should result in
> 1 a
> 2 b,c,d
> 3 a
> Any ideas or suggestions?

Considering blank spaces

I need to make a function for coding string in which i make difference
between strings that are identical except from final blank spaces. In SQL 20
0
exists an option (collate set or something else) that can make SQL
distinguish betwene two string like these:
'MYSTRING1'
'MTSTRING1 '
ThanksOne method...
if(cast('as ' as varbinary) = cast('as' as varbinary))
print 'match'
else
print 'does not match'
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Try this:
USE tempdb
GO
CREATE FUNCTION MyStringCompare(@.str1 nvarchar(MAX), @.str2 nvarchar(MAX))
RETURNS nvarchar(15)
AS
BEGIN
IF ((@.str1 + N'#') = (@.str2 + N'#'))
RETURN N'match'
RETURN N'don''t match'
END
GO
SELECT tempdb.dbo.MyStringCompare(N'MYSTRING1', N'MYSTRING1') -- -> match
SELECT tempdb.dbo.MyStringCompare(N'MYSTRING1', N'MYSTRING1 ') -- -> don't
match
SELECT tempdb.dbo.MyStringCompare(null, N'MYSTRING1 ') -- -> don't match
SELECT tempdb.dbo.MyStringCompare(N'MYSTRING1', null) -- -> don't match
SELECT tempdb.dbo.MyStringCompare(null, null) -- -> don't match
Greetings,
Urs
"checcouno" wrote:

> I need to make a function for coding string in which i make difference
> between strings that are identical except from final blank spaces. In SQL
200
> exists an option (collate set or something else) that can make SQL
> distinguish betwene two string like these:
> 'MYSTRING1'
> 'MTSTRING1 '
> Thanks|||How about this:
declare @.a varchar(30)
declare @.b varchar(30)
set @.a = 'MYSTRING1'
set @.b = 'MTSTRING1 '
IF (@.a = @.b AND DATALENGTH(@.a) = DATALENGTH(@.b))
PRINT 'Matched'
ELSE
PRINT 'Different'
Roy Harvey
Beacon Falls, CT
On Fri, 30 Jun 2006 00:26:01 -0700, checcouno
<checcouno@.discussions.microsoft.com> wrote:

>I need to make a function for coding string in which i make difference
>between strings that are identical except from final blank spaces. In SQL 2
00
>exists an option (collate set or something else) that can make SQL
>distinguish betwene two string like these:
>'MYSTRING1'
>'MTSTRING1 '
>Thanks|||>> I need to make a function for coding string in which make difference betw
een strings that are identical except from final blank spaces. <<
Be very careful about this. SQL pads shorter strings with blanks for
comparing them, so your function could destroy expected behavior and
you would wind up with your own private language. Otherwise, use the
DATALENGTH() function; it is standard and portable.

connections from app

Hi All,
I need a help from experts. We have an vb.net app using sqlserver.
I review the app and I saw a function declaring a connection, open, execute
sp and close. This app runs once a day for couple of minutes.
Now I suggest that this app should have one defined connection and in the
program we can open and close de connection many times. But the developer
keep saying that it doesn't matter.
My question is how is the best way to handle connections inside of an app in
a way to use better the resource of sqlserver server?
I hope this is enough description for you.
Tks in advance.
JohnnyBest practice is to open a connection as late as possible before you use it,
and close it as early as possible after you have finished with it.
This does not mean the you need to destroy and re-create the objects used to
access the DB or that you should close the connection between each operation
in a serial chain of database commands, just dont leave a connection open if
your application is idle.
Mr Tea
"JFB" <help@.jfb.com> wrote in message
news:%234dogSmLFHA.700@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I need a help from experts. We have an vb.net app using sqlserver.
> I review the app and I saw a function declaring a connection, open,
> execute
> sp and close. This app runs once a day for couple of minutes.
> Now I suggest that this app should have one defined connection and in the
> program we can open and close de connection many times. But the developer
> keep saying that it doesn't matter.
> My question is how is the best way to handle connections inside of an app
> in
> a way to use better the resource of sqlserver server?
> I hope this is enough description for you.
> Tks in advance.
> Johnny
>

connection to SQL Server files (*.mdf) require SQL server express 2005 to function properl

I dont have the SQL EXPRESS installed instead I have SQL Standard Edition.

I have two SQL Server instances installed.

1- UserLT (this is sql 2000)
2- UserLT\SQL2005 (this is SQL 2005 named instance)

But when i try to add a database to my VS website project I get the following error:

Connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly. please verify the installation of the component or download from the URL: go.microsoft.com/fwlink/?linkId=4925

I went in Tools>Opetions>DataBase tools>Data Connection>Sql Server Instance Name (blank for default)

and changed the "SQLEXPRESS" to "USERLT\SQL2005".

But I still get the same error message. Any ideas how i can resolve this issue?

Hello,

Attach the files to your SQL server instance using for example the SQL management Studio and change your connectionstring to connect into this database.

|||

Well the thing is that I dont have a database created yet. I am trying to create a new database. If i create a database in SQL 2005 then my connectionstring will point to the database on sql server. I need the database to reside in the VS project like a stand alone .mdf file so that I can deply the database with my project.

Does that make sense? Thanks for your help.

|||

Hi,

You may open your Machine.Config file (the file is in %SystemRoot%\Microsoft.NET\Framework\ver. number\CONFIG ). Try to find the ConnectionString node, modify the setting and make it look like

<add name="LocalSqlServer" connectionString="data source=UserLT\SQL2005;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" />

Thanks.