Click here to Skip to main content
12,761,209 members (29,593 online)
Rate this:
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
Updated 11-Feb-13 11:46am
Maciej Los245.7K
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Using a technique seen on sqlservercentral[^] the following works...

 [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 @FirstName varchar(25)
DECLARE @LastName varchar(25)
DECLARE @Details varchar(25)
DECLARE @DetailList VARCHAR(1000)

OPEN myCur
FETCH NEXT from myCur INTO @FirstName, @LastName
    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
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!!)[^]
Maciej Los 11-Feb-13 16:48pm
Good answer, my 5!
ShivangDesai 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 12-Feb-13 2:09am
You're welcome ;)
ShivangDesai 12-Feb-13 9:07am
Hey CHill60... I implemented it.. Its perfectly working...
Thanks a lot.. :-) :-)
CHill60 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 15-Feb-13 2:33am
Yeah CHill60! I will definitely check it out.
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

here you go :)

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, 
        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
CHill60 11-Feb-13 9:41am
My +5 ... oh to be working with a more up to date version of SQL *sob* ;-)
Maciej Los 11-Feb-13 16:47pm
Agree, +5!
ShivangDesai 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
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web01 | 2.8.170217.1 | Last Updated 11 Feb 2013
Copyright © CodeProject, 1999-2017
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