SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 getting all row values in comma seperated ??
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 03/06/2013 :  23:45:15  Show Profile  Reply with Quote
hello all ,

yesterday i posted a query for which i got a solution but i am unable to stop the comma seperated for stuff select statement. Initially the modified query for user ID giving exact results.
;With CTE
AS
(
Select distinct L.ProfileID as ProfileID,
B.Cust_ID as CustID,B.FirstName+' '+B.LastName as Name,
replace(convert(char(11),L.RegistrationDate,113),' ','-') as DOR,
E.FirstName+' '+E.LastName as OwnerOftheProfile,
E.EmpID as OwnerEmpID,E.BranchID as Branch,
replace(convert(char(11),B.EmpAssignedDate,113),' ','-') as AssignedDate,
EE.FirstName+' '+EE.LastName as ReviewedBy,EE.EmpID as ReviewedEmpID,
replace(convert(char(11),B.ProfileReviewedEmpDate,113),' ','-') as AssignedFromDate,
P.PageName 
from Cust_BasicInfo B join Cust_Login L on B.Cust_ID=L.Cust_ID 
join Cust_Details D on D.Cust_ID=L.Cust_ID left join Emp_Details E on E.EmpID=B.ProfileOwnerEmpID 
left join Emp_Details EE on EE.EmpID=B.ProfileReviewedEmpID  
left join Cust_DataStaging DS on DS.Cust_ID=B.Cust_ID 
join Mst_Section_Type MS on MS.SectionID=DS.SectionID  
join Mst_Page_Type P on P.PageID=MS.PageID
where L.IsReviewed=1 and DS.ReviewStatusID=0  
AND L.Cust_ID=2
)
SELECT c.*,
STUFF((SELECT ',' + PageName FROM CTE WHERE ProfileID = ProfileID
AND CustID = c.CustID
AND Name = c.Name
AND DOR = c.DOR
AND OwnerOftheProfile = c.OwnerOftheProfile
AND AssignedDate = c.AssignedDate
AND AssignedFromDate = c.AssignedFromDate
AND Name = c.Name 
AND Branch = c.Branch 
AND ReviewedEmpID = c.ReviewedEmpID 
FOR XML PATH('')),1,1,'') AS ScetionName
FROM (SELECT DISTINCT ProfileID,CustID,Name,Branch,ReviewedEmpID,AssignedDate,DOR,OwnerOftheProfile,AssignedFromDate FROM CTE)c


if i given user id it is giving accurate result if i remove L.Cust_ID and execute the query it is showing all the values comma sepearted.If the L.Cust_ID has 3 values it need show like

Education and Profession details,Photo details

but here it showing all
Education and Profession details,Photo details,jdjds,sjksjs,jsjsjs etc;

suggest me

P.V.P.MOhan

Edited by - mohan123 on 03/06/2013 23:46:46

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/07/2013 :  00:02:56  Show Profile  Reply with Quote
can you show some sample data and explain? without which we cant understand the issue

are the values jdjds,sjksjs,jsjsjs existing for another cust_ID?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 03/07/2013 :  00:13:23  Show Profile  Reply with Quote
for the above query i am getting result if i mention L.Cust_ID=2

then

ProfileID	CustID	Name	Branch	ReviewedEmpID	AssignedDate	DOR	OwnerOftheProfile	AssignedFromDate	ScetionName
010000026	2	alla Kishore	7	3	13-Dec-2012	29-Nov-2012	Chaitanya Teja	01-Mar-2013	Education and Profession details,Photo details


if i am not mentioning any L.Cust_ID and executing the query

it is giving result set like this please note L.Cust_ID are aroung 80 count so i am posting 3 id for reference




ProfileID	CustID	Name	Branch	ReviewedEmpID	AssignedDate	DOR	OwnerOftheProfile	AssignedFromDate	ScetionName
010000026	2	alla Kishore	7	3	13-Dec-2012	29-Nov-2012	Chaitanya Teja	01-Mar-2013	Education and Profession details,Photo details,Profile details,Education and Profession details,Profile details,Property details,Horoscope details,Partner preference details
010000123	12	fgfd gdfg	7	NULL	27-Feb-2013	06-Dec-2012	VBhaskaraReddy Kovvuri	NULL	Education and Profession details,Photo details,Profile details,Education and Profession details,Profile details,Property details,Horoscope details,Partner preference details

P.V.P.MOhan

Edited by - mohan123 on 03/07/2013 00:18:32
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/07/2013 :  00:27:44  Show Profile  Reply with Quote
With 100 + posts you still dont know how to post proper data?

Post data as per below guideline . Also show us rows for which Property details,Horoscope details,Partner preference details values are existing

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 03/07/2013 :  00:39:02  Show Profile  Reply with Quote
nmo visakha it got huge data thats the reason alignment has gotten wrong. have you understood the problem in the above scenario

P.V.P.MOhan
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 03/07/2013 :  00:43:18  Show Profile  Reply with Quote
for section name column these are all values coming in comma seperated but for suppose L.Cust_ID = 2 or 3 or 7 the section name column values should come like


for 2 = Profile details,Education and Profession details,Photo details
for 3 = Relative details,My References details,Life style details,Spouce details,Property details


like that it have to show but it is showing entire section name column values

P.V.P.MOhan
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/07/2013 :  00:47:07  Show Profile  Reply with Quote
not at all... how can we guess from where you got those additional values in sectionname column?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000