Using a technique seen on
sqlservercentral[
^] the following works...
CREATE TABLE #MyResults(
[SrNo] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[FirstName] [varchar](25) NULL,
[LastName] [varchar] (25) NULL,
[Details] [varchar] (1000)
)
DECLARE myCur CURSOR FOR SELECT FirstName, LastName from #MyStatus Group by LastName,FirstName
DECLARE @SrNo int
DECLARE @FirstName varchar(25)
DECLARE @LastName varchar(25)
DECLARE @Details varchar(25)
DECLARE @DetailList VARCHAR(1000)
OPEN myCur
FETCH NEXT from myCur INTO @FirstName, @LastName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @DetailList = ''
SELECT @DetailList = ISNULL(@DetailList,'') + [Details] + ',' FROM #MyStatus
WHERE FirstName = @FirstName and LastName = @LastName
SET @DetailList = SUBSTRING(@DetailList, 1, LEN(@DetailList)-1)
INSERT INTO #MyResults VALUES(@FirstName, @LastName, @DetailList)
FETCH NEXT from myCur INTO @FirstName, @LastName
END
CLOSE myCur
DEALLOCATE myCur
select * from #MyResults
I use a temporary table for the results simply because your expected results were numbered and I haven't put the || delimiters in. Note that you should also do some checks for the existence of, and possible deallocation of
myCur
before declaring it, and similarly for the existence and possible dropping of the temporary table.
Results from the sql above is
SrNo FirstName LastName Details
1 Tom Cruize is launching a movie,is a hollywood actor
2 Hritik Roshan is married
3 Jack Sean is a male,is married,has a car
4 Jimmy Sean is a male
5 Jack Sparrow is a filmstar
[Edit] I should have mentioned that I used SQL Server 2008 for this.
Here is some more reading on CURSOR in T-SQL (they're not always a good thing!!)
http://www.sqlservercentral.com/articles/cursors/65136/[
^]