Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL SQL-Server
Hello Experts,
 
I am stuck with something and need your help to create an SQL query.
 
I would first try to explain the dummy Database Table design and then state my question.
========================================================
Sr.No || FirstName || LastName || Details
------------------------------------------
  1   || Jack      || Sean     || is a male
  2   || Jack      || Sean     || is  married
  3   || Jack      || Sean     || has a car
  4   || Jack      || Sparrow  || is a filmstar
  6   || Jimmy     || Sean     || is a male
  7   || Hritik    || Roshan   || is  married
  8   || Tom       || Cruize   || is launching a movie
  9   || Tom       || Cruize   || is a hollywood actor
==========================================================
My Question is as mentioned below.
I want to make a query which selects each row with unique name and displays its details in one single row.
 
Let me explain in more detail.
According to that SQL query , I should get the result as shown below.
==========================================================
Sr.No || FirstName || LastName || Details
------------------------------------------
  1   || Jack      || Sean     || is a male , is married , has a car
  2   || Jack      || Sparrow  || is a filmstar
  3   || Jimmy     || Sean     || is a male
  4   || Hritik    || Roshan   || is  married
  5   || Tom       || Cruize   || is launching a movie , is a hollywood actor
==========================================================
The FirstName, LastName and Details are merged in one single row seperated by comma.
 
Can anyone help me in achieving this ??
 
Thanks in advance.
Posted 11-Feb-13 3:55am
Edited 11-Feb-13 11:46am
Maciej Los158.8K
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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 = ''
    -- Build up the comma separated list of details
    SELECT @DetailList = ISNULL(@DetailList,'') + [Details] + ',' FROM #MyStatus
    WHERE FirstName = @FirstName and LastName = @LastName
    -- take the last comma off the end
    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/[^]
  Permalink  
v2
Comments
Maciej Los at 11-Feb-13 16:48pm
   
Good answer, my 5!
ShivangDesai at 12-Feb-13 0:40am
   
Thanks CHill60 for such a nice solution with perfect explanation. I am going to try this now. :-)
 
@Maciej Los : thanks for updating the question ;-)
Maciej Los at 12-Feb-13 2:09am
   
You're welcome ;)
ShivangDesai at 12-Feb-13 9:07am
   
Hey CHill60... I implemented it.. Its perfectly working...
Thanks a lot.. :-) :-)
CHill60 at 12-Feb-13 9:11am
   
No problem! Don't forget to come back here and find Volodymyr's solution if you upgrade your version of SQL - I know I will :-)
ShivangDesai at 15-Feb-13 2:33am
   
Yeah CHill60! I will definitely check it out.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

here you go Smile | :)
 
declare @table table
(
    SrNo int,
    FirstName nvarchar(20),
    LastName nvarchar(20),
    Details nvarchar(200)    
)
 
insert into @table values(1, 'Jack', 'Sean', 'is a male')
insert into @table values(2, 'Jack', 'Sean', 'is married')
insert into @table values(3, 'Jack', 'Sean', 'has a car')
insert into @table values(4, 'Jack', 'Sparrow', 'is a filmstar')
insert into @table values(5, 'Jimmy', 'Sean', 'is a male')
insert into @table values(6, 'Hritik', 'Roshan', 'is married')
insert into @table values(7, 'Tom', 'Cruize', 'is launching a movie')
insert into @table values(8, 'Tom', 'Cruize', 'is a hollywood actor')
 
select FirstName, LastName, 
	STUFF((
        select ',' + second_table.Details
        from @table second_table
        WHERE first_table.FirstName = second_table.FirstName and first_table.LastName = second_table.LastName
        for xml path('')),1,1,'') AS Details
from @table first_table
group by FirstName, LastName
  Permalink  
Comments
CHill60 at 11-Feb-13 9:41am
   
My +5 ... oh to be working with a more up to date version of SQL *sob* ;-)
Maciej Los at 11-Feb-13 16:47pm
   
Agree, +5!
ShivangDesai at 12-Feb-13 0:40am
   
Hey Bobko , thanks for the solution.
I had a glance at it and now am going to implement it. Hope all goes fine. :-)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 400
1 Jochen Arndt 190
2 Richard MacCutchan 135
3 DamithSL 95
4 Garth J Lancaster 90
0 OriginalGriff 6,045
1 DamithSL 4,601
2 Maciej Los 4,032
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,220


Advertise | Privacy | Mobile
Web04 | 2.8.141220.1 | Last Updated 11 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100