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 :

BEGIN TRY  
      SET NOCOUNT ON  
      DECLARE @l_numberOfRecordsInserted INT
 -- Check if valid Application User ID is passed        
      IF ( @i_AppUserId IS NULL ) OR ( @i_AppUserId <= 0 )  
         BEGIN  
               RAISERROR ( N'Invalid Application User ID %d passed.' ,  
               17 ,  
               1 ,  
               @i_AppUserId )  
         END    
 
                 
		 SELECT @l_numberOfRecordsInserted = @@ROWCOUNT,
				@o_CareTeamId = SCOPE_IDENTITY()
		 IF @l_numberOfRecordsInserted <> 1          
		 BEGIN          
			 RAISERROR      
				 (  N'Invalid row count %d in insert CareTeam'
					 ,17      
					 ,1      
					 ,@l_numberOfRecordsInserted                 
				 )              
		 END                 
  
         RETURN 0  

END TRY        
--------------------------------------------------------         
BEGIN CATCH        
    -- Handle exception        
      DECLARE @i_ReturnedErrorID INT  
      EXECUTE @i_ReturnedErrorID = dbo.usp_HandleException @i_UserId = @i_AppUserId  
  
      RETURN @i_ReturnedErrorID  
END CATCH
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TaskBundleProcedureFrequency]') AND type in (N'U'))
DROP TABLE [dbo].[TaskBundleProcedureFrequency]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TaskBundleProcedureFrequency](
	[TaskBundleProcedureFrequencyId] [dbo].[KeyID] IDENTITY(1,1) NOT NULL,
	[TaskBundleId] [dbo].[KeyID] NOT NULL
 CONSTRAINT [PK_TaskBundleProcedureFrequency] PRIMARY KEY CLUSTERED 
(
	[TaskBundleProcedureFrequencyId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'Foreign key to the "Users" table (column "UserId"); defaults to the <User ID> of the System User that inserted the data in the table.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TaskBundleProcedureFrequency', @level2type=N'COLUMN',@level2name=N'CreatedByUserId'
GO
ALTER TABLE [dbo].[TaskBundleProcedureFrequency]  WITH CHECK ADD  CONSTRAINT [FK_TaskBundleProcedureFrequency_TaskBundle] FOREIGN KEY([TaskBundleId])
REFERENCES [dbo].[TaskBundle] ([TaskBundleId])
GO

ALTER TABLE [dbo].[TaskBundleProcedureFrequency] CHECK CONSTRAINT [FK_TaskBundleProcedureFrequency_TaskBundle]
GO
ALTER TABLE [dbo].[TaskBundleProcedureFrequency] ADD  CONSTRAINT [DF_TaskBundleProcedureFrequency_RecurrenceType]  DEFAULT ('R') FOR [RecurrenceType]
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[TaskBundleProcedureFrequency]') AND name = N'PK_TaskBundleProcedureFrequency')
ALTER TABLE [dbo].[TaskBundleProcedureFrequency] DROP CONSTRAINT [PK_TaskBundleProcedureFrequency]
GO

USE [CCMV3DEV]
GO

/****** Object:  Index [PK_TaskBundleProcedureFrequency]    Script Date: 05/15/2014 12:32:51 ******/
ALTER TABLE [dbo].[TaskBundleProcedureFrequency] ADD  CONSTRAINT [PK_TaskBundleProcedureFrequency] PRIMARY KEY CLUSTERED 
(
	[TaskBundleProcedureFrequencyId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_TaskBundleProcedureFrequency_TaskbundleID_ProcedureID] ON [dbo].[TaskBundleProcedureFrequency] 
(
	[TaskBundleId] ASC,
	[CodeGroupingId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 100) ON [FG_Transactional_NCX]
GO



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			

http://stackoverflow.com/questions/tagged/sql+sql-server?page=01&sort=newest&pagesize=50
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

Edited by - mohan123 on 05/19/2014 05:06:22

visakh16
Very Important crosS Applying yaK Herder

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