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
 How to get all same rows in single line using stuf
 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 :  00:50:06  Show Profile  Reply with Quote
hello all ,

i have a query in which i need to show all rows of same id in singel row and last column should be comma seperated

my query is :

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,
MS.SectionName 
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  
where B.GenderID in (1,2) and L.IsPaidMember in (0,1) 
and  B.ProfileReviewedEmpID is not null and DS.ReviewStatusID=0 
and  E.BranchID in (7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33)  
and B.ProfileReviewedEmpID is not null order by B.Cust_ID



now output coming like this :


ID	CustID	Name    Branch	ReviewedEmpID	SectionName
26	2	 Kishore 7		3	 Describeyourself			                                         
26	2	 Kishore 7		3	 Profession Details			
26	2	 Kishore 7		3	Profile Given By			


now i need to get out put like this :

ID	CustID	Name    Branch	ReviewedEmpID	SectionName
26	2	 Kishore 7		3	 Describeyourself,Profession Details,Profile Given By

so last column section name should come as comma seperated ......suggest me

P.V.P.MOhan

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/06/2013 :  01:00:07  Show Profile  Reply with Quote

;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,
MS.SectionName 
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  
where B.GenderID in (1,2) and L.IsPaidMember in (0,1) 
and  B.ProfileReviewedEmpID is not null and DS.ReviewStatusID=0 
and  E.BranchID in (7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33)  
and B.ProfileReviewedEmpID is not null 
)

SELECT c.*,
STUFF((SELECT ',' + SectionName FROM CTE WHERE ProfileID = ProfileID
AND CustID = c.CustID
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 FROM CTE)c
order by Cust_ID


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

Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 03/06/2013 :  01:32:57  Show Profile  Reply with Quote
it worked like charm visakh...but i am triying approach in max format

max ( case when  L.ProfileID != 0 then L.ProfileID end) as ProfileID,

is this right way

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

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/06/2013 :  01:53:06  Show Profile  Reply with Quote
what do you mean by max format?

------------------------------------------------------------------------------------------------------
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.06 seconds. Powered By: Snitz Forums 2000