February 14, 2015 at 8:32 am
Hi,
Below is my sample data and query
declare @Sample table (ID int, message varchar(1000))
insert into @Sample(ID,message)
select 1,'Testing 1' union all
select 1,'Testing 2' union all
select 1,'Testing 3' union all
select 1,'Testing 4' union all
select 1,'Testing 5' union all
select 2,'Testing 6' union all
select 2,'Testing 7' union all
select 2,'Testing 8' union all
select 2,'Testing 9' union all
select 2,'Testing 10' union all
select 3,'Testing 11' union all
select 3,'Testing 12' union all
select 3,'Testing 13' ;
SELECT ID
,message = STUFF((
SELECT CASE
WHEN LEN(message) > 0
THEN N', ' + message
ELSE ''
END
FROM @Sample AS S1
WHERE S1.ID = S2.Id
FOR XML PATH(N'')
), 1, 2, N'')
FROM @Sample AS S2
GROUP BY ID;
I need to get top three values has to be comma separated. for example id 1 has 5 rows message which comma separated. Instead i need to consider top three message group by Id
expected result :
ID message
1 Testing 1, Testing 2, Testing 3
2 Testing 6, Testing 7, Testing 8
3 Testing 11, Testing 12, Testing 13
any help please
February 14, 2015 at 9:15 am
KGJ-Dev (2/14/2015)
Hi,expected result :
ID message
1 Testing 1, Testing 2, Testing 3
2 Testing 6, Testing 7, Testing 8
3 Testing 11, Testing 12, Testing 13
any help please
I just added a TOP 3 to the select and it returned your expected results!
SELECT ID
,message = STUFF((
SELECT TOP 3 CASE
WHEN LEN(message) > 0
THEN N', ' + message
ELSE ''
END
FROM @Sample AS S1
WHERE S1.ID = S2.Id
FOR XML PATH(N'')
), 1, 2, N'')
FROM @Sample AS S2
GROUP BY ID;
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 14, 2015 at 11:34 am
Thank you dude. Mind didn't work on this. appreciate your time
February 14, 2015 at 2:26 pm
If you are going to use top, you should probably be including an order by clause as well, to ensure that it really is the 'top 3' as you define them. Otherwise you could possibly get unexpected results.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply