I have a table that contains some data given below. It uses a tree like structure

i.e.

                Department

     SubD1,                       SubD2 .....

PreSubD1, PreSubD1...       PreSubD2, PreSubD2...       

pk_map_id            preferences          ImmediateParent      Department_Id
-------------------- -------------------- -------------------- --------------------
20                   14                   5                    1
21                   15                   5                    1
22                   16                   6                    1
23                   9                    4                    2
24                   4                    3                    2
25                   24                   20                   2
26                   25                   20                   2
27                   23                   13                   2

I want to group my records on behalf of department then immediate parent then preferences each seperated by ','

i.e.

department  Immediate Parent  preferences
1             5,6                 14,15,16
2             4,3,20,13           9,4,24,25,23

and this table also

Immediate parent  preferences
5                     14,15
6                     16
4                     9
3                     4
20                    24,25
13                    13

In actual scenario all these are my ids which are to be replaced by their string fields.

I am using sql server 2k5