Columns into row in custom format

Question

I have a sql table called UsersInAuthentication like

-----------------------
| AuthUserId | UserId |
|    1       |    4   |
|    1       |    5   |
|    1       |    8   |
-----------------------

I wish to get all users in format "(4,5,8)" as string with a single stored proc and If possible I also wish to insert,update,delete operations with this format.

Edit: I'm using SQL Server 2005

Answer 1

Beats the purpose of a table a bit. Also UPDATING this can and will get tricky.

Seems like you should either rethink the design of your table, either rethink the way you are going to use it.

Answer 2

SELECT CONCAT('(', GROUP_CONCAT(UserId SEPARATOR ','), ')')
FROM UsersInAuthentication;

Answer 3

The actual queries really depend on your DBMS. Assuming Mysql:

SELECT GROUP_CONCAT(UserId) FROM UsersInAuthentication;
UPDATE UsersInAuthentication SET ... WHERE UserId IN (4,5,8);
DELETE FROM UsersInAuthentication WHERE UserId IN (4,5,8);
INSERT INTO UsersInAuthentication(UserId) VALUES (4), (5), (8);

But note that working with comma-delimited values in a database somehow misses the point.

Answer 4

Since you're using SQL Server, you can do this:

declare @list nvarchar(max)
select @list = ''

select
    @list = @list + 
        case when @list = '' then userid
            else ',' + userid end
from
    UsersInAuthentication  

set @list = '(' + substring(@list, 1, 999) + ')'
print @list

This is a nifty trick that builds the variable by appending each row's value to the current value of the variable. It's rather handy with table operations, and the like.

For posterity:

In MySQL, you can uses group_concat:

select 
    concat('(' + group_concat(userid order by userid separator ',') + ')') 
from 
    UserInAuthentication

In Oracle, you can use a cursor:

cursor c_users is
   select userid from usersinauthentication;
    out_users varchar2(4000);
begin
    for r_user in c_users loop
        if out_users is null then
            out_users:= r_user.first_Name;
        else
            out_users:= out_users ||', '|| r_user.first_Name;
        end if;
    end loop;
return out_users;

Answer 5

Assuming SQLServer:

--//testdata
WITH UsersInAuthentication (AuthUserId, UserId) AS (
                SELECT 1, 4
    UNION ALL   SELECT 1, 5
    UNION ALL   SELECT 1, 8
)
--// real query
SELECT  AuthUserId,
    (   SELECT      cast(UserId as varchar) + ','
        FROM        UsersInAuthentication
        ORDER BY    UserID
        FOR XML PATH('')
    ) AS UserIds
FROM    UsersInAuthentication
GROUP BY AuthUserId

to get:

AuthUserId  UserIds
----------- --------
1           4,5,8,