Question: I have a table with ID and Dept.  

Table is

```
id  dept    salary  date
1   30      2000    8/25/2015 12:06:54.870 PM
2   20      5500    7/12/2015 12:06:54.870 PM
3   30      6700    11/21/2013 12:06:54.870 PM
4   30      8900    4/16/2009 12:06:54.870 PM
5   30      9900    6/29/2014 12:06:54.870 PM
6   10      1120    7/3/2015 12:06:54.870 PM
7   20      8900    4/13/2013 12:06:54.870 PM
8   10      2400    7/23/2015 12:06:54.870 PM
9   30      2600    8/21/2015 12:06:54.870 PM
10  10      2999    8/3/2015 12:06:54.870 PM

```

Just need the output like this   

```
Dept  ID
30     1,3,4,5,9

```

Answer: 
This is the best way I know. Please do post if anyone knows a better solution:

I have named your table `sal`

```
DECLARE @id     INT
        , @max  INT
        , @dep  INT
        , @all  VARCHAR(255)

SELECT  @id = 1
        , @max = MAX(id)
FROM    sal

SELECT * INTO #tmp FROM sal

WHILE (1=1)
BEGIN

    SELECT  @dep = dept
    FROM    #tmp
    WHERE   id = @id

    IF @dep IS NULL
    BEGIN
        SELECT  @id = @id + 1

        IF @id > @max
            BREAK
        ELSE
            CONTINUE
    END

    UPDATE  #tmp
    SET     @all = @all + ',' + CONVERT(VARCHAR, id) 
    WHERE   dept = @dep

    --remove last comma
    select  @all = RIGHT(@all, LEN(@all)-1)

    DELETE  #tmp
    WHERE   dept = @dep

    -- selecting the output. insert into table if you want
    SELECT  @dep, @all

    SELECT  @dep   = NULL
            , @all = NULL

    SELECT  @id = @id + 1

    IF @id > @max
        BREAK

    -- fail safe
    IF @id > 100
        BREAK
END

drop table #tmp

```
I had similar problem and came up with the following solution:

```
select dept,list(id,',' order by dept) from TableName group by dept

```

Assuming `TableName` is the name of your table.