Query Related to fetching different rows in one row.

Question

0.00/5 (No votes)

See more:

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 2:55am

ShivangDesai

Updated 11-Feb-13 10:46am

Maciej Los

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2013-02-11T03:35:00

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

SQL

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/[^]

Volodymyr Bobko · Accepted Answer · 2013-02-11T03:35:00

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

Query Related to fetching different rows in one row.

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0