|
Hello
Does anyone know how to get child rows of a row as a string?
I mean, lets say that i have 3 tables:
Students, Courses, and Student_Courses to track the courses that each student is taking
Each student can take many courses at the same time
What i want to retrive is:
Column 1 Column2
Alex 'Course1, Course2, Course3, Course6'
Alexei 'Course3, Course6'
Alexa 'Course2, Course3, Course6'
I dont want to use cursors and i would prefer to do this in SQL server
Im only wondering if SQL has a intruction to acomplish this or if anyone knows an easy way to do this
Thanks for your time
Alexei Rodriguez
|
|
|
|
|
AlexeiXX3 wrote: Does anyone know how to get child rows of a row as a string?
Check out this article[^].
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Try this:
<br />
create table student (StudentID int, Name varchar(50))<br />
create table student_course (StudentID int, CourseID int)<br />
create table course (CourseID int, Name varchar(50))<br />
<br />
insert into student select 1,'Fred'<br />
insert into student select 2,'Joe'<br />
insert into student select 3,'Mary'<br />
<br />
insert into course select 1,'Maths'<br />
insert into course select 2,'Cooking'<br />
insert into course select 3,'Languages'<br />
<br />
insert into student_course select 1,1<br />
insert into student_course select 1,3<br />
insert into student_course select 2,1<br />
insert into student_course select 3,1<br />
insert into student_course select 3,2<br />
insert into student_course select 3,3<br />
<br />
go<br />
<br />
<br />
CREATE FUNCTION dbo.udf_GetStudentCourses(@StudentID int)<br />
RETURNS VARCHAR(1000) AS<br />
<br />
BEGIN<br />
DECLARE @CourseList varchar(1000)<br />
<br />
SELECT @CourseList = COALESCE(@CourseList + ', ', '') + c.Name<br />
FROM Student_Course s, Course c<br />
WHERE s.StudentID = @StudentID<br />
and c.CourseID = s.CourseID<br />
<br />
RETURN @CourseList<br />
END<br />
go<br />
<br />
<br />
select Name,dbo.udf_GetStudentCourses(StudentID)<br />
from Student
Results:
Fred Maths, Languages
Joe Maths
Mary Maths, Cooking, Languages
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks both for your answer
Ill take the function approach suggested by Ashfield, it works great
Thanks again
Alexei Rodriguez
|
|
|
|
|
SITUATION:
An external process (web server) writes one row into a table (WipTable) in my SQL 2005 server, and it takes too much time for my 'INSERT' trigger to execute in once, since the data process and checking is big and time consuming.
This 'INSERT' trigger to this 'WipTable' need to end quickly, but, the trigger takes too much time to finish the transaction. The only step required at this stage is updating a 'confirmation status' field into the table.
My goal is simple:  avoiding the big delay before COMMITTING the first stage of the transaction, and instead split the task in 2 parts (like an Interrupt Service Routine would react):
1) first, do the simple things that place some data (to put a 'CONFIRMATION' status) and write one 'row task' into another table (RequestTable). Then, the RequestTable's TRIGGER would do the rest of the work in ANOTHER transaction.
2) The second table that would then be triggered would take more time to execute but
QUESTION:
How to 'force' the real and effective completion of the transaction, so that another (longer) transaction could begin just after it (being in fact 'triggered' by the first transaction because of an INSERT into a second table possessing an 'INSERT' trigger) ? 
|
|
|
|
|
I'm pretty sure a trigger won't accomplish that.
You could deposit your data in a table and another process (a Windows Service perhaps) could process it into the other tables.
|
|
|
|
|
Thanks for trying to help me.
I finally resorted the problem in using a scheduled job to do the "very" long part.
In summary:
-----------
Part 1) The trigger validates and gets the basics infos for the part 2 as a future work, and places all required entries into a 'REQUEST' table, and QUIT quickly.
Part 2) When the job is activated (could be 1-5 minutes later, for now), the rest of the long searches and inserts are done. Also, the 'request' entries are deletes after work completion.
In the future, I may consider creating a job 'on-the-fly' just before quitting the trigger, we'll see...
Thanks again.
(by the way, it is my first post into this forum, I'm glad to see that this community is really active, it encourages me to contribute more eventually...)
|
|
|
|
|
Hello all
Im trying to figure out an easy way to retrieve records using a simple query like:
SELECT * FROM Products WHERE ProductID IN (3,5,7,9)
This works, but what i need is to store the numbers (3,5,7,9) or any other combination in another table field and execute the select statement with something like the following:
SELECT * FROM Products WHERE ProductID IN (SELECT NumbersField FROM MyTable WHERE Field=@MyParam)
This gives me a syntax error:
Syntax error converting the varchar value '3,5,7,9' to a column of data type int.
Ill appeciate any help
Thanks in advance
Alexei Rodriguez
|
|
|
|
|
To get this to work, you would need to construct a dynamic SQL statement in a (n)varchar variable and execute it using the EXECUTE command.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Thanks for your help, this will save a lot of work 
This is how it looks:
<br />
declare @string varchar(1000)<br />
select @string=movieids from test where X = 1<br />
EXECUTE ('SELECT * FROM movies WHERE movie_id IN ('+ @string +')')<br />
I wonder what the difference is between EXE and EXECUTE??
Both of them work
Alexei Rodriguez
|
|
|
|
|
AlexeiXX3 wrote: I wonder what the difference is between EXE and EXECUTE??
There's no command called EXE. I assume you mean EXEC. If you had read the documentation, you would know that EXEC is the shorthand syntax for EXECUTE.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
I am using the Add Connection wizard in VS 2005.
Data Source: Oracle Database (OLE DB)
Database, username, and password are correct. Click Test Connection.
Login Timeout expired.
An error has occured while establishing a connection to the server. While connecting to SQL Server 2005  , this failure may be caused by the fact that under the settings SQL Server does not allow remote connections.
Named Pipes Provider: Could not open a connection to SQL Server (53).
"Neque porro quisquam est qui dolorem ipsum quia dolor sit amet, consectetur, adipisci velit..."
"There is no one who loves pain itself, who seeks after it and wants to have it, simply because it is pain..."
|
|
|
|
|
hi All
Am using SQL 2000,i want to Save the Diagram in word format and Print it or E-mail it. i went to Diagrams in EnterPrise Manager under my database and i see the Diagram. and i see the save button but i dont know where it saves it. How can i save the Diagram in a word format to Print it later from SQL 2000.
Thanks
(I tried to Explain)
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
You could install a PDF printer and print it to a PDF file, then you can email it or anything else
Alexei Rodriguez
|
|
|
|
|
I have a table called ACCOUNT. The ACCOUNT table contains details of Customers and Customer Head Office accounts. The columns are as follows
AccountId
HeadOfficeId
ImportedId
ImportedHeadOfficeId
What I need to do is update the HeadOfficeId with AccountId where ImportedHeadOfficeId = ImportedId.
Any help appreicated.
Thanks
Steve Jowett
|
|
|
|
|
The solution came to be in a moment of clarity :-
UPDATE ACCOUNT SET HeadOfficeId = (SELECT PsAccount.AccountId FROM ACCOUNT As PsAccount WHERE PsAccount.ImportedId= ACCOUNT.ImportedHeadOfficeId)
Steve Jowett
-------------------------
It is offen dangerous to see someone else's point of view, without proper training.
|
|
|
|
|
Update Account<br />
set HeadOfficeId = AccountId <br />
where ImportedHeadOfficeId = ImportedId
Unless I'm missing something?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks for the reply Bob. What I did not make clear in my original post is that HeadOfficeId should relate to another row within the same table.
I have actually, managed to sort it myself, but thanks again for taking the time to help.
Steve Jowett
-------------------------
It is offen dangerous to see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)
|
|
|
|
|
Thought I was missing something. No worries.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
set markup HTML on
spool index.html
SELECT * FROM pap_in_indexmaster;
spool off
set markup HTML off
I want to execute this script, as it is, so that it does the spool command as it has been scripted to do. I want to execute this script in asp.net....
How Can I do that??
or
any alternative to display the query result in HTML table view in HTML page..??
|
|
|
|
|
The spool command (and I think the set commands too) are specific to the Oracle SQL*Plus tool. If you want to replicate this in ASP.NET then you will have to create a DataReader object and convert the results into HTML within your ASP.Net code.
Regards
Andy
|
|
|
|
|
Hi, I am in the trouber of the error while the project trys to saving the data to child table. The error message
is shown below:
NHibernate.ADOException: could not insert: [ConsoleApplication1.user][SQL: INSERT INTO user (UNAME, NID) VALUES (?, ?)] --->
So confused that why it cannot get values to save. NHibernate I used is version 1.2. The code files are list below, please help me out here.
Thanks in advance.
app.config:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<section name="nhibernate" type="System.Configuration.NameValueSectionHandler, System, Version=1.2.0.4000,Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
</configSections>
<nhibernate>
<add key="hibernate.connection.provider" value="NHibernate.Connection.DriverConnectionProvider"/>
<add key="hibernate.dialect" value="NHibernate.Dialect.MsSql2005Dialect"/>
<add key="hibernate.connection.driver_class" value="NHibernate.Driver.SqlClientDriver"/>
<add key="hibernate.connection.connection_string" value="Server=;initial catalog=;Persist Security Info=True;User ID=;Password="/>
</nhibernate>
</configuration>
user.hbm.xml:
<hibernate-mapping default-cascade="none" xmlns="urn:nhibernate-mapping-2.2">
<class name="ConsoleApplication1.user, ConsoleApplication1" table="user">
<id name="UID" type="System.Int32" column="UID" unsaved-value="0">
<generator class="native" />
</id>
<property name="UNAME" type="System.String" column="UNAME" not-null="false" />
<many-to-one name="Nationality" class="ConsoleApplication1.nationality, ConsoleApplication1" fetch="select" cascade="all">
<column name="NID" not-null="false" />
</many-to-one>
</class>
</hibernate-mapping>
user.hbm.cs:
namespace ConsoleApplication1 {
[System.SerializableAttribute()]
public class Abstractuser {
private int uID;
private string uNAME;
private ConsoleApplication1.nationality nationality;
public virtual int UID {
get {
return this.uID;
}
set {
this.uID = value;
}
}
public virtual string UNAME {
get {
return this.uNAME;
}
set {
this.uNAME = value;
}
}
public virtual ConsoleApplication1.nationality Nationality {
get {
return this.nationality;
}
set {
this.nationality = value;
}
}
}
[System.SerializableAttribute()]
public partial class user : Abstractuser {
}
}
nationality.hbm.xml:
<hibernate-mapping default-cascade="none" xmlns="urn:nhibernate-mapping-2.2">
<class name="ConsoleApplication1.nationality, ConsoleApplication1" table="nationality">
<id name="NID" type="System.Int32" column="NID" unsaved-value="0">
<generator class="native" />
</id>
<property name="NATIONALITY" type="System.String" column="NATIONALITY" not-null="false" />
<bag name="User" inverse="true" lazy="true" cascade="all">
<key>
<column name="NID" not-null="false" />
</key>
<one-to-many class="ConsoleApplication1.user, ConsoleApplication1" />
</bag>
</class>
</hibernate-mapping>
nationality.hbm.cs:
namespace ConsoleApplication1 {
[System.SerializableAttribute()]
[System.Xml.Serialization.XmlIncludeAttribute(typeof(ConsoleApplication1.user))]
[System.Xml.Serialization.SoapIncludeAttribute(typeof(ConsoleApplication1.user))]
public class Abstractnationality {
private int nID;
private string nATIONALITY;
private System.Collections.IList user;
public virtual int NID {
get {
return this.nID;
}
set {
this.nID = value;
}
}
public virtual string NATIONALITY {
get {
return this.nATIONALITY;
}
set {
this.nATIONALITY = value;
}
}
public virtual System.Collections.IList User {
get {
return this.user;
}
set {
this.user = value;
}
}
}
[System.SerializableAttribute()]
public partial class nationality : Abstractnationality {
}
}
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
NHibernate.Cfg.Configuration cfg = new NHibernate.Cfg.Configuration();
cfg.AddAssembly("ConsoleApplication1");
ISessionFactory factory = cfg.BuildSessionFactory();
ISession session = factory.OpenSession();
ITransaction transaction = session.BeginTransaction();
nationality nat = (nationality)session.Get(typeof(nationality), 1);
user u = new user();
u.UNAME = "Pall";
u.Nationality = nat;
nat.User.Add(u);
try
{
if (!session.IsConnected)
{
session.Reconnect();
}
session.Save(u); // Error occured at this line!!
transaction.Commit();
session.Close();
}
catch (Exception e)
{
string s = e.ToString();
}
}
}
}
|
|
|
|
|
I answered this in the other forum. If I had seen that you cross-posted then I wouldntve.
|
|
|
|
|
Hi All
I have a small problem if any budy know please reply me
I have the following sql code
when i will execute it i will get a some data in a single column
Now i want to insert those data into a temp table.But the problem is
@Role_Idv is a column in table [admin].[cm_tb_005].Which i am passing dynamically.
If iam taking only this much
select tb001_id FROM [admin].[cm_tb_005] WHERE '+ @Role_Idv +'='1''
The above code will not work.If iam doing like this
exec('select tb001_id FROM [admin].[cm_tb_005] WHERE '+ @Role_Idv +'=''1''')
It is working.Now i want to store the resulted values in a temp table.So that i can
select the values from temp table for other purposes.
This is the code which iam trying now which is excuting but how can i store the resultant values in a temp table
SET QUOTED_IDENTIFIER ON
declare @res1 nvarchar(500)
declare @TempMenuId nvarchar(800)
declare @Role_Idv varchar(50)
set @Role_Idv='R25'
DECLARE @TempMenuIdTable TABLE(RowIndex INT IDENTITY,Menu_Id INT )
set @res1='select tb001_id FROM [admin].[cm_tb_005] WHERE '+ @Role_Idv +'=''1'''
select @res1
exec(@res1)
Regard's
Veeresh
i want to join this group
|
|
|
|
|
Assuming you are using SQL Server 2005 (probably works on 2000 but not tested) here is a simple example:
create table #temp(employeeid bigint)<br />
<br />
exec('insert into #temp select employeeid from employee')<br />
<br />
select * from #temp<br />
<br />
drop table #temp
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
