| Author |
 Topic  |
|
|
mohan123
Posting Yak Master
India
196 Posts |
 Posted - 03/06/2013 : 00:50:06
|
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
47099 Posts |
Posted - 03/06/2013 : 01:00:07
|
;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/
|
 |
|
|
mohan123
Posting Yak Master
India
196 Posts |
Posted - 03/06/2013 : 01:32:57
|
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 03/06/2013 : 01:53:06
|
what do you mean by max format?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
How to get all same rows in single line using stuf
