How to consolidate multiple rows into a single column

Hello,
I would like some help on developing a SQL query.
I have a Team table and a Person Table. For simplicity sake, lets say
the Team has a key and team name. The Person has a person key, team
key, and person name.
I want to query for all team members, and store the results in a single
column. So, the resulting view would have three columns: team key, team
name, and a list of all people on the team.
Any pointers or tips appreciated,
J Wolfgang GoerlichSELECT T.team_key, T.team_name, P.person_name
FROM Team AS T
JOIN Person AS P
ON T.team_key = P.team_key ;
David Portas
SQL Server MVP
--|||Here is a sample:
=====
CREATE TABLE Team
(
TeamID INT,
TeamName VARCHAR(20)
)
GO
CREATE TABLE Person
(
PersonID INT,
TeamID INT,
PersonName VARCHAR(50)
)
GO
INSERT INTO Team VALUES (1, 'Development')
INSERT INTO Team VALUES (2, 'Release')
INSERT INTO Person VALUES (1, 1, 'Bob')
INSERT INTO Person VALUES (2, 1, 'Mason')
INSERT INTO Person VALUES (3, 2, 'Chris')
INSERT INTO Person VALUES (4, 2, 'Scott')
INSERT INTO Person VALUES (5, 2, 'Bruce')
GO
=====
The above just creates some sample tables and data. Now, we can define a
function that concatenates the list of team members as follows:
=====
IF (OBJECT_ID ('dbo.formTeamList') IS NOT NULL)
DROP FUNCTION dbo.formTeamList
GO
CREATE FUNCTION dbo.formTeamList (@.teamID INT)
RETURNS VARCHAR(8000) AS
BEGIN
DECLARE @.teamList VARCHAR(8000); SET @.teamList = ''
SELECT
@.teamList = @.teamList + ', ' + ISNULL (PersonName, '')
FROM
Person
WHERE
TeamID = @.teamID
RETURN (STUFF (@.teamList, 1, 2, ''))
END
GO
=====
Once done, we can test this out as follows:
=====
SELECT
TeamID, TeamName, dbo.formTeamList (TeamID)
FROM
Team
=====
Although the method shown above works, it is not recommended as there are
limitations:
(1) The function is called for every row in the team table. This can cause
performance problems for large lists.
(2) The function can only concatenate a list of 8000 characters in length
(4000 if you are using UniCode).
Such logic is usually best handled in the application tier of your program,
sinnce you have good flexibility to rotate rows into columns.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
<jwgoerlich@.gmail.com> wrote in message
news:1131967790.112616.168370@.g44g2000cwa.googlegroups.com...
> Hello,
> I would like some help on developing a SQL query.
> I have a Team table and a Person Table. For simplicity sake, lets say
> the Team has a key and team name. The Person has a person key, team
> key, and person name.
> I want to query for all team members, and store the results in a single
> column. So, the resulting view would have three columns: team key, team
> name, and a list of all people on the team.
> Any pointers or tips appreciated,
> J Wolfgang Goerlich
>

how to connect to multiple database?

Is it possible to connect to 3 database and execute a cross db query?

I usually use SqlConnection(connectionString) for a single connection

As I understand, an open SQLConnection will connect to a SQL instance rather than just a SQL database, which means you can perform query on all objects in all databases if you have sufficient permission, just like what you can do in Query Analyzer. Is this what you want? If yes, you can write your query using qualified object name:

select * from db1.dbo.tbl1 as t1 join db2.dbo.tbl2 as t2

on t1.id=t2.id

How to connect to a DB multiple times through page

Hey all,

I am still pretty new to all of this and I am having problems accessing a the same DB twice in my page. I want to pull information once in one spot, but then pull different information in a different spot on the page. Anyway, in the first spot, I have the following code:

Dim conStringAsString = WebConfigurationManager.ConnectionStrings("DataConn").ConnectionString

Dim conAsNew SqlConnection(conString)

Dim cmdAsNew SqlCommand("Select Team1.TeamID as Team1ID, Team1.TeamName as HomeTeam, Team2.TeamID as Team2ID, Team2.TeamName as AwayTeam, Football_Schedule.ScheduleID as ScheduleID from (Select * from Football_Teams) as Team1, (Select * from Football_Teams) as Team2, Football_Schedule where Football_schedule.team1 = Team1.teamid AND Football_Schedule.team2 = Team2.teamid order by Football_Schedule.WeekNum, Team1.TeamName, Team2.Teamname", con)Using con

con.Open()

Dim RSAs SqlDataReader = cmd.ExecuteReader()

While RS.Read()

blah blah blah

End While

End Using

Then in my other spot on the page I have the following:

Dim cmdresultsAsNew SqlCommand("Select Users.Firstname, sum(PointsID) as TotalPoints from Football_Input, Football_Schedule, Users where Football_Input.TeamID = Football_Schedule.winID and users.userid = Football_Input.UserID Group by Users.firstname")

Using con

con.Open()

Dim RDAs SqlDataReader = cmdresults.ExecuteReader()

While RD.Read()%>

<tr>

<td>

<%=RD%>

</td>

</tr>

<%EndWhile

EndUsing

When I try to execute I get this error, "ExecuteReader: Connection property has not been initialized." on the following line, "

RDAs SqlDataReader = cmdresults.ExecuteReader()" Any ideas? If possible a little explanation on how multiple connections to the same database work would be nice just for future reference.

Thanks in advance!!,

Chris

I think you are not using separate file for the code behind.

fischecp:

Dim cmdAsNew SqlCommand("Select Team1.TeamID as Team1ID, Team1.TeamName as HomeTeam, Team2.TeamID as Team2ID, Team2.TeamName as AwayTeam, Football_Schedule.ScheduleID as ScheduleID from (Select * from Football_Teams) as Team1, (Select * from Football_Teams) as Team2, Football_Schedule where Football_schedule.team1 = Team1.teamid AND Football_Schedule.team2 = Team2.teamid order by Football_Schedule.WeekNum, Team1.TeamName, Team2.Teamname", con)

fischecp:

Dim cmdresultsAsNew SqlCommand("Select Users.Firstname, sum(PointsID) as TotalPoints from Football_Input, Football_Schedule, Users where Football_Input.TeamID = Football_Schedule.winID and users.userid = Football_Input.UserID Group by Users.firstname")

Did you observe that you haven't provided the connection for the second command ?

Even if you would have done so, unless you close the previous datareader you'll get another error when trying to bind the second datareader stating you already have a reader attached to this connection. One more thing, you're trying to open the same connection again in your second code. This will also result in an error stating the connection is already open.

Now, my advices to you are:

If possible, use code behind files. This way you will be able to differentiate your UI and Code.

In the code behind write small functions for the tasks you've mentioned above. Create a connection object in each of the function and dispose it before the code leaves the function.

You can use data binding techniques to bind the data returned in your datareader to any of the UI controls.

Hope this will help.

|||

fischecp:

Dim cmdresultsAsNew SqlCommand("Select Users.Firstname, sum(PointsID) as TotalPoints from Football_Input, Football_Schedule, Users where Football_Input.TeamID = Football_Schedule.winID and users.userid = Football_Input.UserID Group by Users.firstname")

i think you forgot to give "con" as SqlConnection in New SqlCommand arguments... as..

Dim cmdresultsAsNew SqlCommand("SelectUsers.Firstname, sum(PointsID) as TotalPoints from Football_Input,Football_Schedule, Users where Football_Input.TeamID =Football_Schedule.winID and users.userid = Football_Input.UserID Groupby Users.firstname",con)

also make sure you close the connection each time after operation completes.. and re-open for a new operation..

Good Luck./.

|||

Ok, I appreciate the help. At least my page runs without errors, however! Now I get the page to load but in place of the data from the database is, "System.Data.SqlClient.SqlDataReader" in every line where the information from the database should be displaying.

Here is my code that I fixed (At least I think I fixed haha)

con =New SqlConnection(conString)

Dim cmdresultsAsNew SqlCommand("Select Users.Firstname, sum(PointsID) as TotalPoints from Football_Input, Football_Schedule, Users where Football_Input.TeamID = Football_Schedule.winID and users.userid = Football_Input.UserID Group by Users.firstname", con)

Using con

con.Open()

Dim RDAs SqlDataReader = cmdresults.ExecuteReader()

While RD.Read()%>

<tr>

<td>

<%=RD%>

</td>

</tr>

<%EndWhile

RD.close()

con.close()

EndUsing

I also closed out my previous connection and datareader like you guys mentioned. I'll post the code for that just incase. And I will use functions to clean it all up. I just would like to see how it works first =)

Dim conStringAsString = WebConfigurationManager.ConnectionStrings("DataConn").ConnectionString

Dim conAsNew SqlConnection(conString)

Dim cmdAsNew SqlCommand("Select Team1.TeamID as Team1ID, Team1.TeamName as HomeTeam, Team2.TeamID as Team2ID, Team2.TeamName as AwayTeam, Football_Schedule.ScheduleID as ScheduleID from (Select * from Football_Teams) as Team1, (Select * from Football_Teams) as Team2, Football_Schedule where Football_schedule.team1 = Team1.teamid AND Football_Schedule.team2 = Team2.teamid order by Football_Schedule.WeekNum, Team1.TeamName, Team2.Teamname", con)Using con

con.Open()

Dim RSAs SqlDataReader = cmd.ExecuteReader()

While RS.Read()

EndWhile

con.Close()

RS.Close()

EndUsing

Any Ideas? Thanks again guys,

Chris

|||

<%=RD%>

You probably want to specify which field you are trying to display on the page. Like:

<%= RD.Item(0) %>

Or "columnname" instead of index.

|||

haha wow, I have had my head so deep into this I didn't even stop to think that. Sometimes you just need to take a step back =)

Thanks again guys! you solved everything!

sql

How to connect generic database field to report?

Hello,

I want to make a report where multiple users can use the same report to connect to their databases and then print out the report with information from those databases. Both databases have the exact same tables and fields but the data that is in them is different. However, I have only been able to figure out how to connect the report to one specific database, and therefore the report always prints out information from that database instead of the user specified one. So let's say I want my report to print out the name that is in the database field Name for any database I connect to, how would I do this?

Assuming SSRS 2005, you can use an expression-based connection string as demonstrated in the ExpressionBasedConnection sample report in this download. Since I think it is a no-no to pass the connection string as a report parameter, the report gets it from the Report Server config file.|||I looked at your samples but I'm not sure I quite understand them. It looks like your ExpressionBasedConnection report just connected to the database named AdventureWorks. But my reports are going to be connecting to databases that have the same underlying structure but with variable names that I won't know when creating the report. I will only know the name of the database when the user runs our software to connect to whatever database they use. But I still want to create a report that will use, for example, the Name, Address, State fields from the Customer table, but I don't want to hook it up to one specific database. How do I do this?|||

OK, but somehow the end-user has to specify the database name at some point, correct? Let's assume that the user will pass the database name as a report parameter. Then, you can use an expression-based connection string in the report data source (it must be private), to establish connection to that database.

Did I miss anything?

|||Yes at some point the user will specify what database they want to connect to but I don't think I fully understand how to use that database in the report. So let's say I want to add a dataset to my report by the name of Company. So in the Dataset tab of the report I select new dataset and I get to the Dataset dialog box. So I name the dataset Company. I go into the "Datasource connection" dialog box by clicking the ... button. I've created a parameter for the dataset call ConnectionStr, so therefore in the Connection String edit box, I type " = Parameters!Connection.Value". Then I click OK that dialog and return to the Dataset dialog box. For the query string I type "SELECT * FROM Company" and then click OK. So in the datasets toolbar, I can see a dataset named Company but it doesn't have any of the company fields in it like Name, Address, etc. How to I get these fields in the dataset so I can use them in my report? Since the report doesn't know what the ConnectionStr parameter is yet it doesn't seem like it will be able to do this.|||Start with a normal connection string. Click the Refresh Fields button on the Data tab in the Report Designer. Then change to an expression-based connection string.|||Alright, so I did what you suggested, but when I drag a field from the dataset I want it says First(Fields!Name.Value, "Company"), but I don't want a report that just prints the information of the first company in the dataset, I want a report printed for every company that is in the dataset after my query has been applied.|||This usually happens when the data region is bound to a different dataset than the one you are dragging the fields from. Take a look at the Dataset property of the data region and clear it (or set to Company). Then, you can re-drag your fields or remove the First function so the field reference is =Fields!Name.Value.|||So I've accomplished this, and now I am having a similar problem of connecting a generic data source to the reportviewer. When I'm in the Data Sources window and I select Add New Data Source, in order to add a new one, I have to select a specific database. But I don't want the reportviewer to connect to one specific data source, I just want it to print out the report that I have made following your tips, how do I do this?

How to Configure Virtual SQL Server to listen on Multiple ports

We have Active / Passive Cluster .We need to cofigure SQL Server Virtual Server (DBVirtual) to listen on multiple ports like (1433,9901,9902...etc).
Please let me know how we can achieve this.
Thanks for the help
294453 INF: How to Set Up SQL Server 2000 to Listen on Multiple Static TCP
Ports
http://support.microsoft.com/?id=294453
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

How to Configure Virtual SQL Server to listen on Multiple ports

We have Active / Passive Cluster .We need to cofigure SQL Server Virtual Ser
ver (DBVirtual) to listen on multiple ports like (1433,9901,9902...etc).
Please let me know how we can achieve this.
Thanks for the help294453 INF: How to Set Up SQL Server 2000 to Listen on Multiple Static TCP
Ports
http://support.microsoft.com/?id=294453
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

How to configure SQL server to use multiple processor

hi,
We have a system with two processors. What settings should we do in sql
server 2000 standard edition to use both the processors. Can anyone suggest?
Thanks
PremHello,
By itself SQL Server will use both processors. There is no change required.
Thanks
Hari
"Prem" <Prem_a20@.hotmail.com> wrote in message
news:uoDh1q8SHHA.4956@.TK2MSFTNGP04.phx.gbl...
> hi,
> We have a system with two processors. What settings should we do in sql
> server 2000 standard edition to use both the processors. Can anyone
> suggest?
> Thanks
> Prem
>
>|||underprocessable

How to configure SQL server to use multiple processor

hi,
We have a system with two processors. What settings should we do in sql
server 2000 standard edition to use both the processors. Can anyone suggest?
Thanks
PremHello,
By itself SQL Server will use both processors. There is no change required.
Thanks
Hari
"Prem" <Prem_a20@.hotmail.com> wrote in message
news:uoDh1q8SHHA.4956@.TK2MSFTNGP04.phx.gbl...
> hi,
> We have a system with two processors. What settings should we do in sql
> server 2000 standard edition to use both the processors. Can anyone
> suggest?
> Thanks
> Prem
>
>

How to configure multiple subscriber to same publisher using filte

Hi,
I have a base table in publisher db. I have a column, using which i want to
filter it and replicate it to certain dbs. how do i dynamically do it ?
Like for eg, consider this table :
ID Project name center
1 A NY
2 B LON
3 C PAR
now, i want to filter using the column "center". if center = 'NY', then i
must direct it to a particular subscriber. if it is "LON" then it must be
directed to someother subscriber. also, i must accomplish this using merge
replication
for my requirement, i am not able to use multiple publishers to accomplish
it. i must use one publisher, with a filter which varies dynamically
depending on the subscriber.
please explain how i can accomplish this.
PS : I'm new to databases. sorry if this question is very basic
Ki,
this question is not at all basic
You can use dynamic filtering in merge replication. Set up the filter as
center = HOST_NAME(). In the merge agent, before initializing, edit the
command-line parameters and add -HOSTNAME NY for the NY subscriber and so on
for the others.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

how to configure multiple databases for one asp.net website

Hello:
I am working on a website that will require the use of multiple
databases - a unique database for each user in fact. It is somewhat of
a light CRM application and it is important to keep a user's customer
information in their own database.
So I am hoping to get input on the best way to implement this. The
approach I am using so far includes storing the database connection
string in a property of the user's profile object. However, this
requires referencing the profile object each time I hit the database.
And for pages that are using the sqldatasource control, that means
assigning the connectionstring property for that control in the
page_load event outside of an "if not page.ispostback" block. So I
wonder if there isn't a better way.
Any feedback would be really appreciated here.
ChrisHow about storing the user's database name in their profile object and/or in
a session variable. Then you can prefix all of your calls with the database
name:
... "EXEC " + session("database_name") + ".dbo.ProcedureName";
Of course, if you are doing this for scalability reasons, you will probably
want to consider multiple connection strings for different servers...
<chrishalldba@.yahoo.com> wrote in message
news:1138581470.361914.172540@.o13g2000cwo.googlegroups.com...
> Hello:
> I am working on a website that will require the use of multiple
> databases - a unique database for each user in fact. It is somewhat of
> a light CRM application and it is important to keep a user's customer
> information in their own database.
> So I am hoping to get input on the best way to implement this. The
> approach I am using so far includes storing the database connection
> string in a property of the user's profile object. However, this
> requires referencing the profile object each time I hit the database.
> And for pages that are using the sqldatasource control, that means
> assigning the connectionstring property for that control in the
> page_load event outside of an "if not page.ispostback" block. So I
> wonder if there isn't a better way.
> Any feedback would be really appreciated here.
> Chris
>|||How about storing the user's database name in their profile object and/or in
a session variable. Then you can prefix all of your calls with the database
name:
... "EXEC " + session("database_name") + ".dbo.ProcedureName";
Of course, if you are doing this for scalability reasons, you will probably
want to consider multiple connection strings for different servers...
<chrishalldba@.yahoo.com> wrote in message
news:1138581470.361914.172540@.o13g2000cwo.googlegroups.com...
> Hello:
> I am working on a website that will require the use of multiple
> databases - a unique database for each user in fact. It is somewhat of
> a light CRM application and it is important to keep a user's customer
> information in their own database.
> So I am hoping to get input on the best way to implement this. The
> approach I am using so far includes storing the database connection
> string in a property of the user's profile object. However, this
> requires referencing the profile object each time I hit the database.
> And for pages that are using the sqldatasource control, that means
> assigning the connectionstring property for that control in the
> page_load event outside of an "if not page.ispostback" block. So I
> wonder if there isn't a better way.
> Any feedback would be really appreciated here.
> Chris
>|||Aaron:
Thanks for your reply. I actually left out some stuff in my post to
keep the thread somewhat open-ended. Your suggestion actually looks
very close to what I ended up implementing. I am storing a database
connection string in a profile object, then loading that into a session
variable when the user logs in. It sounds like I'm on the right track
so that's reassuring. Thanks for the peace of mind!
Chris

How to concatenate multiple rows into one field?

Hi,

I hope someone here can help me.

We have a product table which has a many-to-many relation
to a category table (joined through a third "ProductCategory" table):

[product] --< [productCategory] >-- [category]
--- ------ ----
productID productCategoryID categoryID
productName productID categoryName
categoryID

We want to get a view where each product occupies just one row, and
any multiple category values are combined into a single value, eg
(concatenating with commas):

Product Category
------
cheese dairy
cheese solid
milk dairy
milk liquid
beer liquid

will become:

Product Category
------
cheese dairy, solid
milk dairy, liquid
beer liquid

What is the best way to do it in SQL?

Thanks and regards,
Dmitri"mitmed" <mitmed@.yahoo.com> wrote in message
news:c2fa9a07.0408182248.684dfd7a@.posting.google.c om...
> Hi,
> I hope someone here can help me.
> We have a product table which has a many-to-many relation
> to a category table (joined through a third "ProductCategory" table):
> [product] --< [productCategory] >-- [category]
> --- ------ ----
> productID productCategoryID categoryID
> productName productID categoryName
> categoryID
> We want to get a view where each product occupies just one row, and
> any multiple category values are combined into a single value, eg
> (concatenating with commas):
> Product Category
> ------
> cheese dairy
> cheese solid
> milk dairy
> milk liquid
> beer liquid
> will become:
> Product Category
> ------
> cheese dairy, solid
> milk dairy, liquid
> beer liquid
> What is the best way to do it in SQL?
> Thanks and regards,
> Dmitri

The usual answer is that you should do this in the client, not in TSQL, but
if you must then the only reliable way is using a cursor.

http://www.aspfaq.com/show.asp?id=2279

Simon|||Thanks for your reply Simon,

I completely agree with you that the best place for this type of code
is on the client side. The issue is that my client side is a Crystal
Report (in VB.NET) and i don't know how to do this kind of processing
there. The report i'm trying to produce is the list of products and
their details including categories a product belongs to. I would
really appreciate if somebody could point me to a good crystal report
resource, where it shows how to do things like that if it's possible.

Regards,
Dmitri

"Simon Hayes" <sql@.hayes.ch> wrote in message news:<4124eaa5_2@.news.bluewin.ch>...
> "mitmed" <mitmed@.yahoo.com> wrote in message
> news:c2fa9a07.0408182248.684dfd7a@.posting.google.c om...
> > Hi,
> > I hope someone here can help me.
> > We have a product table which has a many-to-many relation
> > to a category table (joined through a third "ProductCategory" table):
> > [product] --< [productCategory] >-- [category]
> > --- ------ ----
> > productID productCategoryID categoryID
> > productName productID categoryName
> > categoryID
> > We want to get a view where each product occupies just one row, and
> > any multiple category values are combined into a single value, eg
> > (concatenating with commas):
> > Product Category
> > ------
> > cheese dairy
> > cheese solid
> > milk dairy
> > milk liquid
> > beer liquid
> > will become:
> > Product Category
> > ------
> > cheese dairy, solid
> > milk dairy, liquid
> > beer liquid
> > What is the best way to do it in SQL?
> > Thanks and regards,
> > Dmitri
> The usual answer is that you should do this in the client, not in TSQL, but
> if you must then the only reliable way is using a cursor.
> http://www.aspfaq.com/show.asp?id=2279
> Simon|||You can do this very easily with the RAC utility/tool for S2k.
No sql coding required.

For info on concatenation over rows see:
http://www.rac4sql.net/onlinehelp.asp?topic=236

RAC v2.2 and QALite @.
www.rac4sql.net

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||mitmed (mitmed@.yahoo.com) writes:
> I completely agree with you that the best place for this type of code
> is on the client side. The issue is that my client side is a Crystal
> Report (in VB.NET) and i don't know how to do this kind of processing
> there. The report i'm trying to produce is the list of products and
> their details including categories a product belongs to. I would
> really appreciate if somebody could point me to a good crystal report
> resource, where it shows how to do things like that if it's possible.

We use Crystal in our system (and we hate it!), but we never let Crystal
near SQL Server itself. The "database" we tell Crystal about is text files
with all the columns. The actual queries are submitted from VB6, and then
we feed Crystal one of more recordsets, typically augmented with other stuff
that the VB code puts in.

Exactly how that translates to in VB .Net I don't know, although it is
possible to work with ADO Recordset if you use the OleDb .Net data
provider. Then again, who wants to use ADO recordsets if you are in .Net?

I should add that my notion of how we use Crystal is somewhat foggy. I
try to stay away from Crystal as much as I can.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

how to concatenate multiple rows into one

I want to Concatenate multiple rows into one string to show on the
report. Can someone help plz.Adnan,
U need to use Cursors...in the stored procedure.
Thats the only solution i know.....
"Adnan" wrote:
> I want to Concatenate multiple rows into one string to show on the
> report. Can someone help plz.
>

How to Combine results from multiple records into one

Hello,

I have a table which has the following structure:

ID MessageText
001 Hello
001 There
001 Working
003 See
003 you
003 Next
003 Time

How to build a query or store procedure to return result like this:

ID MessageText
001 Hello There Working
003 See you Next Time

Your help/advice is greatly appreciated.

Thanks, FicisaYou don't have anything that would identify the order of the words in the sentence....|||The order is not important, as long as I can put them together into one field.|||USE Northwind
GO

CREATE TABLE myTable99(WordOrder int IDENTITY(1,1), [ID] char(3), MessageText varchar(255))
GO

INSERT INTO myTable99([ID], MessageText)
SELECT '001', 'Hello' UNION ALL
SELECT '001', 'There' UNION ALL
SELECT '001', 'Working' UNION ALL
SELECT '003', 'See' UNION ALL
SELECT '003', 'you' UNION ALL
SELECT '003', 'Next' UNION ALL
SELECT '003', 'Time'
GO

DECLARE @.messagetext varchar(2000), @.MAX_ID char(3), @.MIN_ID char(3)

DECLARE @.myTable99 TABLE ([ID] char(3), messagetext varchar(2000))

SELECT @.MAX_ID = MAX([ID])
, @.MIN_ID = MIN([ID])
FROM myTable99

SELECT @.messagetext = ''

WHILE @.MIN_ID <> @.MAX_ID
BEGIN
SELECT @.messagetext = COALESCE(@.messagetext+ ' ','') + MessageText
FROM myTable99
WHERE [ID] = @.MAX_ID

INSERT INTO @.myTable99([ID], MessageText)
SELECT @.MAX_ID, @.MessageText

SELECT @.MAX_ID = MAX([ID])
FROM myTable99
WHERE [ID] < @.MAX_ID

SELECT @.messagetext = ''
END

SELECT @.messagetext = COALESCE(@.messagetext+ ' ','') + MessageText
FROM myTable99
WHERE [ID] = @.MIN_ID

INSERT INTO @.myTable99([ID], MessageText)
SELECT @.MIN_ID, @.MessageText

SELECT * FROM @.myTable99
GO

DROP TABLE myTable99
GO|||aaaaaaaaaaaarggggh! code!!!

my eyes!!!!! they're BLEEEEEEEEEDING!!

hey, how's this for a solution:

select ID
, group_concat(MessageText) as MessageText
from atable
group
by ID

see GROUP BY functions (http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html)

not sql server?

pity, eh

:cool:|||That's mySQL not SQL Server|||Ok, how about the hybred approach:CREATE TABLE dbo.tMessages (
Id CHAR(3) NOT NULL
, MessageText VARCHAR(20) NOT NULL
)

INSERT dbo.tMessages (Id, MessageText)
SELECT '001', 'Hello'
UNION ALL SELECT '001', 'There'
UNION ALL SELECT '001', 'Working'
UNION ALL SELECT '003', 'See'
UNION ALL SELECT '003', 'you'
UNION ALL SELECT '003', 'Next'
UNION ALL SELECT '003', 'Time'

DROP FUNCTION dbo.fGrunge
GO
CREATE FUNCTION dbo.fGrunge (
@.id CHAR(3)
) RETURNS VARCHAR(4000) AS
BEGIN
DECLARE @.c VARCHAR(20), @.r VARCHAR(8000)
SET @.r = ''

DECLARE zfGrunge CURSOR FOR SELECT MessageText
FROM dbo.tMessages
WHERE id = @.id
ORDER BY MessageText

OPEN zfGrunge
FETCH zfGrunge INTO @.c

WHILE 0 = @.@.fetch_status
BEGIN
SET @.r = @.r + ' ' + @.c
FETCH zfGrunge INTO @.c
END

CLOSE zfGrunge
DEALLOCATE zfGrunge

RETURN SubString(@.r, 2, 8000)
END
GO

SELECT a.id, dbo.fGrunge(a.id)
FROM dbo.tMessages AS a
GROUP BY a.id
ORDER BY a.id

DROP TABLE dbo.tMessages-PatP|||Pat,

A cursor?

Damn, it's only Monday....|||Oh wow,

Perfect!!!

Brett's answer works great. There sure are some programming, it would be nice to have group_concat() function in MS SQL. Forgot I can create one of my own. Thanks very much Pat!!!

Now here's another question, I was trying to use cursor to solve it, like in Pat's function, does it have any disadvantage/advantage over Brett's solution?

Thank you a bunch!!!

ficisa|||I can do it with oodles of left joins, but that gets ugly too. While cursors are a good way to kill an application, they are intent on killing this one anyway.

Trawling the result set is better done on the middle tier or the client anyway. This is fundamentally poor design. I was just offering this as a solution to the stated problem, not necessarily advocating it!

-PatP|||How much data you talking about?

Why not perf bechmark both and let us know...(ya gotta do something...:D )

But I'm thinking (ok, ok, don't all be sooo amazed) that the cursor will be slower...|||Originally posted by Brett Kaiser
That's mySQL not SQL Server

dude, i knew that

(something about grandmothers and eggs)

that's why i posted it!!

so that you sql server guys could SEE HOW IT SHOULD BE DONE

:cool:|||Originally posted by r937
dude, i knew that

(something about grandmothers and eggs)

that's why i posted it!!

so that you sql server guys could SEE HOW IT SHOULD BE DONE

:cool:

Rudy, where is that in the ANSI guide?

I can't find it...

:D|||ansi?

shirley you jest

as if microsoftborg gave two figs for ansi|||Don't call me shirley...

I prefer laverne|||Ok, I tested with my function below and Pat's function using a cursor, with 3436 groups, both took same amount of time of 4 seconds to complete. Hmmm....

So how about this as the function?

CREATE FUNCTION dbo.Group_Concat_Message

(@.InstitutionNumber char(6) )

RETURNS varchar(5000)

AS

BEGIN

DECLARE @.messagetext varchar(5000)

SELECT @.messagetext = COALESCE(@.messagetext+ ' ','') + MessageText
FROM vInstWithMessage
WHERE InstitutionNumber = @.InstitutionNumber

Return @.messagetext

END|||Why not...

Still think you'll end up worrying about the order of the data at some point..

GOOD LUCK...

and OH MY GOD...it's after 5:00!

Later..

How to Combine Multiple Rows Data into single Record or String based on a common field.

Hellow Folks.

Here is the Original Data in my single SQL 2005 Table:

Department: Sells:

1 Meat

1 Rice

1 Orange

2 Orange

2 Apple

3 Pears

The Data I would like read separated by Semi-colon:

Department: Sells:

1 Meat;Rice;Orange

2 Orange;Apple

3 Pears

I would like to read my data via SP or VStudio 2005 Page . Any help will be appreciated. Thanks..

Hi,

you can use the following Function in SQL server:

USE NORTHWIND
GO
CREATE FUNCTION ProductList (@.CategoryIDINT)
RETURNSVARCHAR(1000)
AS
BEGIN
DECLARE @.ProductsVARCHAR(1000)
SELECT@.Products =COALESCE(@.Products +', ','') + ProductName
FROM Products
WHERE CategoryID = @.CategoryID
ORDER BY ProductNameASC
RETURN @.Products
END
GO
SELECTDISTINCT CategoryID, dbo.ProductList (CategoryID)AS ProductList
FROM Products
GO

and this is based on your table: 

USE NORTHWINDCS
GO
CREATE FUNCTION ProductList (@.CategoryIDINT)
RETURNSVARCHAR(1000)
AS
BEGIN
DECLARE @.ProductsVARCHAR(1000)
SELECT@.Products =COALESCE(@.Products +', ','') + sells
FROM table1
WHERE Department = @.CategoryID
ORDER BY sellsASC
RETURN @.Products
END
GO
SELECTDISTINCT Department, dbo.ProductList (department)AS ProductList
FROM table1
GO
 

thanks

|||

SharpGuy, your solution works. Thanks and have a great Thanksgiving...

How to combine Multiple Dataset result in each row of the report

Hi,

I need to produce a report in MS SQL Server Reporting Service 2005 which has some date fields & need to pass these date fields to find out the no_of_User on that date from another database. For example

Itemid

Availabilty_Start_Dt

Availabilty_End_Dt

User_On_St_DT (this info is in another DB & need to pass Availabilty_Start_Dt )

User_On_ED_DT (this info is in another DB & need to pass Availabilty_Start_Dt )

I am facing two problem here. Since hereis two different dataset needed how can I combine the dataset result to produce each row of the report.

REally appreciate if anyone can show me some light on it.

Regards,

write a strored procedure in the backend and try to write the two queries and combine the results and store in a temp table.

use the stored procedure in the reporting services. Backend is the best solution for it.