2

I have an order table like below where each order record for each group is placed on a row. Now I'd like to make this compact and aggregate the data by group by puting all the orders for one group in one cell and separated by "," (The resulted table is below the original table).

Could you please tell me how to realize this in SQL?

Thanks a lot!

Here is the original table:

Group    Order
1        a
1        b
1        c
2        d
2        e
3        f

Here is the converted and compact table:

Group    Orders
1        a,b,c
2        d,e
3        f
Vikdor
  • 23,934
  • 10
  • 61
  • 84
Wayne
  • 91
  • 1
  • 7
  • You know that that type of database design is generally frowned upon? You will find a multitude of posts in this forum which are about getting OUT of the kind of mess you are proposing. – Nick.Mc Nov 28 '12 at 05:11
  • You could use a stored procedure to generate the comma separated records. For more information have a look at: http://stackoverflow.com/questions/887628/convert-multiple-rows-into-one-with-comma-as-separator http://stackoverflow.com/questions/2046037/sql-server-can-i-comma-delimit-multiple-rows-into-one-column – Yvo Nov 28 '12 at 02:15

1 Answers1

3

This will help

T-Sql (Sql Server 2005 +)

Declare @t Table([Group] Int, [Order] Varchar(20))
Insert Into @t Values(1,'a'),(1,'b'),(1,'c'),(2,'d'),(2,'e'),(3,'f')

Select [Group],
    Orders = Stuff((Select ',' + Cast([Order] As Varchar(100))
     From @t t2 Where t2.[Group] = t1.[Group]
     For Xml Path('')),1,1,'')
From @t t1
Group By t1.[Group]

Pl/Sql Oracle 11g R2 + 

SELECT [Group],ListAgg([Order],',') Within Group (Order By [Group]) Orders
FROM ORDER
GROUP BY [Group];

Pl/Sql Oracle 10g +

SELECT [Group],WM_Concat([Order],',') Orders
FROM ORDER
GROUP BY [Group];

enter image description here

Niladri Biswas
  • 4,153
  • 2
  • 17
  • 24
  • Hi Niladri, do you know how we can do this procedure reversely? I mean we firstly have all the orders in a cell for each group, we then want to expand them to multiple records for each group. Or we firstly have the bottom/second table, we want to change it to the form of the top/first table. Thanks! – Wayne Dec 03 '12 at 22:03
  • @Lin Liu,in that case please open a new thread and I will post the answer – Niladri Biswas Dec 04 '12 at 02:41