i want an output for a query using t-sql

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

i want an output for a query using t-sql

Author

Topic

Mario Noronha
Starting Member

6 Posts

Posted - 2010-06-16 : 02:08:34

Hi i have two tables with the schema

t_Client Table :
ClientID int Unchecked
ClientName varchar(100) Unchecked
TitleID int Unchecked
Email varchar(100)
Description varchar(500)
Location varchar(100)
Tenure int
username varchar(200)
password varchar(200)
SurveyID int

t_Client_Process Tbale

Client_ProcessID int
ClientID int
ProcessID int
SurveyID int
Responded bit

and my qurey and the table that i generate is as folows:

now sometimes i get two processnames for the same client while i run the above query . now i want to a query which would give me an out put in such a way that if a client is alligned to two processes only one row should show up with the processnames seprated by a comma in the same row

eg:
ClientName ProcessName
Mario Morkel TestProcess,TestProcess1

Thanks
Mario Noronha

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-16 : 02:27:18

We can't see the picture.

No, you're never too old to Yak'n'Roll if you're too young to die.

Mario Noronha
Starting Member

6 Posts

Posted - 2010-06-16 : 06:06:52

sorry guys i dint know that the image was not available

this would be my query :

Sql Query :
SELECT c.ClientID,c.SurveyID,c.ClientName ,c.Tenure ,c.TitleID ,c.Email ,c.Description,c.Location,
c.Username,c.Password,(Select p.ProcessName from t_Process p where p.ProcessID = cp.ProcessID)
as ProcessName,cp.ProcessID,cp.Responded FROM t_Client c left outer join
t_Client_Process cp on c.ClientID = cp.ClientID and c.SurveyID = cp.SurveyID
where c.SurveyID = 106 order by clientid asc

this is my table :

ClientID SurveyID ClientName Tenure TitelID Email Description Location username password ProcessName ProcessID Responded
473 106 Mario Noronha 14 1 mario.naronha@ubs.com TestProcess 385 0
473 106 Mario Noronha 14 1 mario.naronha@ubs.com TestProcess1 386 0
474 106 Mario Morkel 14 1 marionoronha.s@gmail.com TestProcess1 386 0
474 106 Mario Morkel 14 1 marionoronha.s@gmail.com TestProcess 385 0
505 106 Kiran Tuti 12 3 kiran.tuti@ubs.com TestProcess1 386 0
512 106 Manish Rathore 14 3 manish.rathore@ubs.com TestProcess 385 0
513 106 Margert 14 4 margaret.tacher@ubs.com TestProcess1 386 0
514 106 Mak 10 4 mak@ubs.com TestProcess 385 0

Thanks
Mario Noronha

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources