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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to get all same rows in single line using stuf

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 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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-06 : 01:00:07
[code]
;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
[/code]

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

Go to Top of Page

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 way

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

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -