Tricky SQL query return formatted HTML result with <br>-CodePudding

My query needs to return a formatted result

<a href="https://example.com/manager/tasks/view/{ID}">{TITLE}</a>

but adding a <br/> if multiple tasks are returned for the assignee

Expected results:

<a href="https://example.com/manager/tasks/view/1">Task 1</a><br/> //Assignee 1
<a href="https://example.com/manager/tasks/view/3">Task 3</a><br/> //Assignee 1
<a href="https://example.com/manager/tasks/view/5">Task 5</a>      //Assignee 1
<a href="https://example.com/manager/tasks/view/2">Task 2</a><br/> //Assignee 2
<a href="https://example.com/manager/tasks/view/4">Task 4</a>      //Assignee 2

I get these results:

<a href="https://example.com/manager/tasks/view/1">Task 1</a><br/> //Assignee 1
<a href="https://example.com/manager/tasks/view/2">Task 2</a><br/> //Assignee 2
<a href="https://example.com/manager/tasks/view/3">Task 3</a> //Assignee 1
<a href="https://example.com/manager/tasks/view/4">Task 4</a> //Assignee 2
<a href="https://example.com/manager/tasks/view/5">Task 5</a> //Assignee 1

Table:

CREATE TABLE [dbo].[tasks]
(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [title] [varchar](255) NOT NULL,
    [description] [varchar](max) NOT NULL,
    [assignee] [int] NOT NULL
)

Sample data:

INSERT INTO [tasks] ([title], [description], [assignee])
VALUES ('Task 1', 'Example text', '1'),
       ('Task 2', 'Example text', '2'),
       ('Task 3', 'Example text', '1'),
       ('Task 4', 'Exam,pple Text', '2'),
       ('Task 5', 'Exam,pple Text', '1')

Query:

SELECT
    CONCAT('<a href="https://example.com/manager/tasks/view/',id,'">', title, '</a>') AS link  
FROM
    tasks
WHERE 
              -- Group rows by assignee to get the same assignees then select minimum ID value to get 
    id NOT IN (SELECT MIN(id) 
               FROM tasks
               GROUP BY assignee)

UNION

SELECT
    CONCAT('<a href="https://example.com/manager/tasks/view/', id,'">', title, '</a><br/>') AS link  
FROM
    tasks
WHERE 
    id IN (SELECT MIN(id) 
           FROM tasks
           GROUP BY assignee)

CodePudding user response：

Since the HTML you want is actually valid XHTML, you can generate it from SQL Server using FOR XML

SELECT
  [a/@href] = CONCAT('https://example.com/manager/tasks/view/', id),
  [a/text()] = title,
  br = CASE WHEN LEAD(title) OVER (PARTITION BY assignee ORDER BY id) IS NOT NULL
         THEN '' END
FROM tasks t
ORDER BY
  assignee, id
FOR XML PATH('');

If you want a new XHTML value per row, you can put it into a correlated subquery

SELECT link = (
  SELECT
    [a/@href] = CONCAT('https://example.com/manager/tasks/view/', id),
    [a/text()] = title,
    br = CASE WHEN nxt IS NOT NULL
           THEN '' END
  FOR XML PATH('')
)
FROM (
    SELECT *,
      nxt = LEAD(title) OVER (PARTITION BY assignee ORDER BY id)
    FROM tasks
) t
ORDER BY
  assignee, id;

db<>fiddle

CodePudding user response：

Select CONCAT('<a href="https://example.com/manager/tasks/view/',id,'">',title,'</a>'
, case when count(*) over(partition by assignee order by id)<count(*) over(partition by assignee) then '<br/>' end) as link  
from tasks