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 : 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 : 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 CATCHIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TaskBundleProcedureFrequency]') AND type in (N'U'))DROP TABLE [dbo].[TaskBundleProcedureFrequency]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE 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]GOSET ANSI_PADDING OFFGOEXEC 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'GOALTER TABLE [dbo].[TaskBundleProcedureFrequency] WITH CHECK ADD CONSTRAINT [FK_TaskBundleProcedureFrequency_TaskBundle] FOREIGN KEY([TaskBundleId])REFERENCES [dbo].[TaskBundle] ([TaskBundleId])GOALTER TABLE [dbo].[TaskBundleProcedureFrequency] CHECK CONSTRAINT [FK_TaskBundleProcedureFrequency_TaskBundle]GOALTER TABLE [dbo].[TaskBundleProcedureFrequency] ADD CONSTRAINT [DF_TaskBundleProcedureFrequency_RecurrenceType] DEFAULT ('R') FOR [RecurrenceType]GOIF 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]GOUSE [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]GOCREATE 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 SectionName26 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=50now i need to get out put like this : ID CustID Name Branch ReviewedEmpID SectionName26 2 Kishore 7 3 Describeyourself,Profession Details,Profile Given By so last column section name should come as comma seperated ......suggest meP.V.P.MOhan |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-06 : 01:00:07
|
[code];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,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 = ProfileIDAND CustID = c.CustIDAND 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 FROM CTE)corder by Cust_ID[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
mohan123
Constraint Violating Yak Guru
252 Posts |
Posted - 2013-03-06 : 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 wayP.V.P.MOhan |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-06 : 01:53:06
|
what do you mean by max format?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|