Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
I Have a table as below
id   suborder
==================
1    a
2    b
1    c
1    d
2    e
3    f
and i need a resultset as below
id   suborder    OtherSuborders
===============================
1     a         c,d,
2     b         e
1     c         a,d
1     d         a,c
2     e         b
3     f         null (0r) empty
 
Can Any one help me out?
 
Thanks in advance.
Posted 1-May-13 7:13am
Comments
CHill60 at 1-May-13 12:18pm
   
What version of SQL are your running with (as some solutions won't work on earlier versions)
HariPrasad katakam at 1-May-13 12:19pm
   
Sql Server 2008 R2
gvprabu at 3-May-13 6:35am
   
hi, suborder Column have unigue value right
HariPrasad katakam at 3-May-13 6:37am
   
Yes
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You can concatenate rows in a table using FOR XML PATH() function. Check this out: http://sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path/
  Permalink  
Comments
HariPrasad katakam at 1-May-13 13:21pm
   
Hi Balimusi, Thanks for your reply. I have gone through the link earlier, it is use full, but it is not giving me 100% info what exactly i am looking for.
 
Thanks again for your time.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hi,
Try like as follows,
SELECT ROW_NUMBER() OVER(PARTITION BY M.id, ORDER BY M.suborder) 'RowNo',
M.id, M.suborder, STUFF((SELECT ','+suborder AS text()
FROM table_name
WHERE ROW_NUMBER() OVER(PARTITION BY id, ORDER BY suborder) <> 1
AND id=M.ID FOR XML PATH(''))),1,1,'') 'OtherSuborders'
FROM table_name M
WHERE ROW_NUMBER() OVER(PARTITION BY M.id, ORDER BY M.suborder) = 1
-- In my PC , I don't have SQL Server, So check and tel me the give Query is fine or not.
Regards,
GVPrabu
  Permalink  
v2
Comments
HariPrasad katakam at 3-May-13 11:38am
   
Hello Prabu, Thanks for your time. The query is throwing syntactical errors.
gvprabu at 3-May-13 11:43am
   
yes yar, Bcos in my office machine I don't have SQL server. So check and fix that Issue or else once I reach my home. I will update the Post. :-)
gvprabu at 3-May-13 11:43am
   
This is the logic for your requirement...
HariPrasad katakam at 3-May-13 13:32pm
   
Ok thanks.will try to fix from my end with the same logic.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

What you need is a theoretical concatenation aggregate. Well, the problem is, that there is no such thing in SQL Server 2008R2. But the good new is, that you can make one really easily with the CLR integration (see: http://msdn.microsoft.com/en-us/library/91e6taax(v=vs.80).aspx[^] and http://msdn.microsoft.com/en-us/library/ms182741(v=sql.105).aspx[^]).
It is a really clear and simple solution but you need two things as above: write the code (really simple), deploy and declare the aggregate in the database (for this you need administrative privileges on the server).
Look here: http://www.mssqltips.com/sqlservertip/2022/concat-aggregates-sql-server-clr-function/[^]
An other interesting reading in this topic: https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/[^]
  Permalink  
Comments
HariPrasad katakam at 7-May-13 12:03pm
   
Thanks Zoltán Zörgő, Let me try with your solutions. I will let you know once done.

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

  Print Answers RSS
0 OriginalGriff 277
1 Maciej Los 220
2 DamithSL 190
3 Raul Iloc 185
4 Richard MacCutchan 175
0 OriginalGriff 5,130
1 DamithSL 4,197
2 Maciej Los 3,670
3 Kornfeld Eliyahu Peter 3,470
4 Sergey Alexandrovich Kryukov 2,821


Advertise | Privacy | Mobile
Web02 | 2.8.141216.1 | Last Updated 3 May 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