| Author |
 Topic  |
|
|
mohan123
Posting Yak Master
India
196 Posts |
 Posted - 03/06/2013 : 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 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
47099 Posts |
Posted - 03/07/2013 : 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 MVP
http://visakhm.blogspot.com/
|
 |
|
|
mohan123
Posting Yak Master
India
196 Posts |
Posted - 03/07/2013 : 00:13:23
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
|
|
mohan123
Posting Yak Master
India
196 Posts |
Posted - 03/07/2013 : 00:39:02
|
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 |
|
|
|
mohan123
Posting Yak Master
India
196 Posts |
Posted - 03/07/2013 : 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 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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 03/07/2013 : 00:47:07
|
not at all... how can we guess from where you got those additional values in sectionname column?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
getting all row values in comma seperated ??
