Get data and using subselect on the same table

Question

I have a table like this:

+--------------+--------------+--------------+
| userid       | username     | proxy        |
+--------------+--------------+--------------+
| 1            | j.doe        |              |
| 2            | lechnerio    | 1,4          |
| 3            | janedoe      | 1            |
| 4            | mustermann   | 2            |
+--------------+--------------+--------------+

The proxy can either be NULL, one or more IDs from other users.

I'd like to build a view that helps to visualize the user. I thought of a similar result like this:

+--------------+--------------+--------------+-----------------------------+
| userid       | username     | proxy        | proxy_info                  |
+--------------+--------------+--------------+-----------------------------+
| 1            | j.doe        |              |                             |
| 2            | lechnerio    | 1,4          | j.doe (1), mustermann (4)   |
| 3            | janedoe      | 1            | j.doe (1)                   |
| 4            | mustermann   | 2            | lechnerio (2)               |
+--------------+--------------+--------------+-----------------------------+

I can't wrap my head around the sub-select I need for the proxy_info. The table itself has more than these three columns, but that shouldn't matter in this example. combining the proxy_info with a username and the id in brackets isn't the issue either. Yet I'm unable to subselect the values where the userid matches.

I'd be happy about any tips and hints to achieve the result listed above.

I thought about either joining the table with itself or using a union. But both options seem over-complicated for the desired result. I'm working with SQL Server here.

as of an idea:

SELECT a.agentcode
    ,a.username
    ,a.proxy
    ,(
        SELECT b.agentcode
        FROM app_users b
        WHERE a.agentcode = b.proxy
        ) AS proxy_info
FROM app_users a

What version of sql server are you on? – P.Salmon Apr 07 '20 at 09:57 — P.Salmon, Apr 07 '20 at 09:57
@P.Salmon I'm working on SQL Server 2017 – lechnerio Apr 07 '20 at 09:59 — lechnerio, Apr 07 '20 at 09:59

score 3 · Accepted Answer · answered Apr 07 '20 at 10:28

Try this:

DROP TABLE IF EXISTS [dbo].[test_Users];
GO

CREATE TABLE [dbo].[test_Users]
(
    [userid] INT
   ,[username] VARCHAR(18)
   ,[proxy] VARCHAR(12)
);

GO

INSERT INTO [dbo].[test_Users] ([userid], [username], [proxy])
VALUES (1, 'j.doe',  NULL)
      ,(2, 'janedoe',  '1,4')
      ,(3, 'janedoe',  '1')
      ,(4, 'mustermann',  '2');

GO

SELECT U.[userid]
      ,U.[username]
      ,U.[proxy]
      ,NULLIF(STRING_AGG(CONCAT(P.[username], '(', p.[userid] ,')'), ', '), '()')
FROM [dbo].[test_Users] U
OUTER APPLY STRING_SPLIT (U.[proxy], ',') S
LEFT JOIN [dbo].[test_Users] P
    ON S.[value] = P.[userid]
GROUP BY U.[userid]
        ,U.[username]
        ,U.[proxy];

superb solution , including NULLIF is very beautiful. – Venkataraman R Apr 07 '20 at 11:37 — Venkataraman R, Apr 07 '20 at 11:37

Jsowa · Answer 2 · 2020-04-07T10:28:53.277

1

Edit: that is solution for MySQL. In SQL Server there is not equivalent function FIND_IN_SET, but there are workarounds, i.e. FIND_IN_SET() equivalent in SQL Server .

You can play with functions FIND_IN_SET to join the table and GROUP_CONCAT to concatenate values in the form you want to get them.

SELECT a.id, a.username, a.proxy, GROUP_CONCAT(CONCAT(b.username,'(',b.id,')')) as `proxy_name`
FROM users a
INNER JOIN users b ON(FIND_IN_SET(b.id,a.proxy))
GROUP BY a.id
ORDER BY a.id;

Output:

| id |   username | proxy |             proxy_name |
|----|------------|-------|------------------------|
|  2 |  lechnerio |   1,4 | j.ode(1),mustermann(4) |
|  3 |    janedoe |     1 |               j.ode(1) |
|  4 | mustermann |     2 |           lechnerio(2) |

edited Apr 07 '20 at 10:28

answered Apr 07 '20 at 10:25

Jsowa

9,104
5
56
60

Shouldn't it be a left join? YOur output is lacking the ID 1... – Tyron78 Apr 07 '20 at 10:28
1

It depends what author wants to retrieve. Second example that was removed from question excluded empty proxy values. – Jsowa Apr 07 '20 at 10:30

score 1 · Answer 3 · answered Apr 07 '20 at 10:31

Below query will work in SQL Server 2017

Test Setup. I have taken one record lesser for easy setup

CREATE TABLE Tablename(userid int, username varchar(20), proxy varchar(10))

INSERT INTO Tablename
VALUES (1, 'j.doe',null), (2,'lechnerio','1,4'),(4, 'mustermann','2');

Query to Execute

;WITH CTE_ExpandedTable AS
(SELECT userid, username, proxy, t.val as proxyid
FROM Tablename
OUTER APPLY (SELECT value from string_split(proxy, ',')) as t(val)
)
SELECT c.userid, c.username,c.proxy, CASE WHEN c.proxy IS NULL THEN NULL
ELSE
STRING_AGG(CONCAT(t1.username,' (',t1.userid,')'),',') END AS proxies
FROM CTE_ExpandedTable AS c
LEFT OUTER JOIN TableName as t1
ON t1.userid = c.proxyid
GROUP BY c.userid, c.username,c.proxy

Resultset

+--------+------------+--------+--------------------------+
| userid |  username  | proxy  |         proxies          |
+--------+------------+--------+--------------------------+
|      1 | j.doe      | (null) | (null)                   |
|      2 | lechnerio  | 1,4    | j.doe (1),mustermann (4) |
|      4 | mustermann | 2      | lechnerio (2)            |
+--------+------------+--------+--------------------------+

score 1 · Answer 4 · answered Apr 07 '20 at 10:53

You can use a mixtrue of XMLPath and recursive ctes. First, split the proxy. Then join the names per proxy id. Last but not least concat the string again. Here an example - I used several ctes in order to follow the process step by step:

DECLARE @T1 TABLE(
  userid int,
  username nvarchar(100),
  proxy nvarchar(100)
)

INSERT INTO @T1 VALUES(1, 'j.doe', NULL)
INSERT INTO @T1 VALUES(2, 'lechnerio', '1,4')
INSERT INTO @T1 VALUES(3, 'janedoe', '1')
INSERT INTO @T1 VALUES(4, 'mustermann', '2')

;WITH cte1 AS(
  SELECT *, username + ' (' + CAST(userid AS nvarchar(2)) + ')' AS DisplayUserName
    FROM @T1
),
cte2 AS(
    SELECT
        userid,
        username,
        DisplayUserName,
        proxy,
        CAST(LEFT(proxy, CHARINDEX(',', proxy + ',') - 1) AS NVARCHAR(100)) d1,
        STUFF(proxy, 1, CHARINDEX(',', proxy + ','), '') pString
    FROM cte1
    UNION all
    SELECT
        userid,
        username,
        DisplayUserName,
        proxy,
        CAST(LEFT(pString, CHARINDEX(',', pString + ',') - 1) AS NVARCHAR(100)) d1,
        STUFF(pString, 1, CHARINDEX(',', pString + ','), '')
    FROM cte2
    WHERE
        pString > ''
),
cte3 AS(
SELECT c.*, t.DisplayUserName ProxyUserName
  FROM cte2 c
  LEFT JOIN cte1 t ON t.userid = c.d1
)
SELECT DISTINCT x2.userid, x2.username, x2.proxy
      ,SUBSTRING(
        (
          SELECT ',' + x1.ProxyUserName AS [text()]
            FROM cte3 x1
            WHERE x1.userid = x2.userid
            ORDER BY x1.userid, x1.ProxyUserName, x1.proxy
            FOR XML PATH ('')
        ),2,4000) DisplayUserName
  FROM cte3 x2
  LEFT JOIN cte1 t ON t.userid = x2.d1

score 1 · Answer 5 · answered Apr 07 '20 at 12:23

Here is another solution for you using STRING_SPLIT() and STUFF() function of SQL Server.

create table MyTable(userid int
 , username varchar(50)
 , proxy varchar(20))

 insert into MyTable values
(1, 'j.doe', null),
(2, 'lechnerio', '1,4'),
(3, 'janedoe', '1'),
(4, 'mustermann', '2')

; with cte as (SELECT 
 t1.userid
 , t1.username     
 , t1.proxy
 , t2.username  + '(' + value + ')' as proxyinfo
FROM MyTable as t1 
outer apply STRING_SPLIT(t1.[proxy], ',') p
left join dbo.MyTable as t2 on t2.userid = p.value
)
SELECT a.userid
    , username
    , proxy
    , STUFF((
    select ', '+ cast(proxyinfo as nvarchar(150)) 
    from cte b
    WHERE a.userid = b.userid
    FOR XML PATH(''), TYPE
    ).value('.', 'varchar(max)')
    ,1,1,'') AS proxyinfo
FROM cte a
GROUP BY a.userid
   , username
   , proxy

Here is the working db<>fiddle demo.

Get data and using subselect on the same table

5 Answers5