Need Help in Formulating a table output using T_Sq

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)

Need Help in Formulating a table output using T_Sq

Author

Topic

Mario Noronha
Starting Member

6 Posts

Posted - 2010-06-16 : 06:14:51

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:

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

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

now sometimes i get two processnames for the same client while i run the above query . now i want a query which would give me an output table 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

Sachin.Nand

2937 Posts

Posted - 2010-06-16 : 07:15:43

But what about the processid's?
Should they also be shown as comma seperated also as the processid for Mario Morkel are different.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Mario Noronha
Starting Member

6 Posts

Posted - 2010-06-17 : 01:19:44

quote:
Originally posted by Idera

But what about the processid's?
Should they also be shown as comma seperated also as the processid for Mario Morkel are different.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

ya the processids and the process names should show as comma seperated
so basically i would need just one row instead of two if a client is alligned to two pracesses.

Thanks
Mario Noronha

matty
Posting Yak Master

161 Posts

Posted - 2010-06-17 : 06:35:59

[code]
SELECT ClientID,c.SurveyID,c.ClientName ,c.Tenure ,c.TitleID ,c.Email ,c.Description,c.Location,
c.Username,c.Password,
STUFF(( SELECT ', ' + p.ProcessName
FROM
t_Process p
JOIN
t_Client_Process cp
ON c.ClientID = cp.ClientID
AND c.SurveyID = cp.SurveyID WHERE
p.ProcessID = cp.ProcessID
FOR XML PATH('')
),1,1,''
) AS ProcessName,
STUFF(( SELECT ', ' + CONVERT(VARCHAR(20),cp.ProcessID)
FROM
t_Client_Process cp
WHERE
c.ClientID = cp.ClientID
AND c.SurveyID = cp.SurveyID
FOR XML PATH('')
),1,1,''
) AS ProcessID
FROM
t_Client c

WHERE
c.SurveyID = 106
ORDER BY
c.clientid ASC
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources