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.
Author |
Topic |
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-03-06 : 23:45:15
|
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 CTEAS(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.PageIDwhere L.IsReviewed=1 and DS.ReviewStatusID=0 AND L.Cust_ID=2)SELECT c.*,STUFF((SELECT ',' + PageName FROM CTE WHERE ProfileID = ProfileIDAND CustID = c.CustIDAND Name = c.NameAND DOR = c.DORAND OwnerOftheProfile = c.OwnerOftheProfileAND AssignedDate = c.AssignedDateAND AssignedFromDate = c.AssignedFromDateAND Name = c.Name AND Branch = c.Branch AND ReviewedEmpID = c.ReviewedEmpID FOR XML PATH('')),1,1,'') AS ScetionNameFROM (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 detailsbut here it showing all Education and Profession details,Photo details,jdjds,sjksjs,jsjsjs etc;suggest meP.V.P.MOhan |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-07 : 00:02:56
|
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 MVPhttp://visakhm.blogspot.com/ |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-03-07 : 00:13:23
|
for the above query i am getting result if i mention L.Cust_ID=2then [CODE]ProfileID CustID Name Branch ReviewedEmpID AssignedDate DOR OwnerOftheProfile AssignedFromDate ScetionName010000026 2 alla Kishore 7 3 13-Dec-2012 29-Nov-2012 Chaitanya Teja 01-Mar-2013 Education and Profession details,Photo details[/CODE]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 ScetionName010000026 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 details010000123 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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-03-07 : 00:39:02
|
nmo visakha it got huge data thats the reason alignment has gotten wrong. have you understood the problem in the above scenarioP.V.P.MOhan |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-03-07 : 00:43:18
|
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 detailsfor 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 valuesP.V.P.MOhan |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-07 : 00:47:07
|
not at all... how can we guess from where you got those additional values in sectionname column?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|