CODEWITHSUNDEEP
sqlsql-server
user389391
user389391

Reputation: 97

How does one create a SQL query to return a formatted HTML result containing breaks <br>

My mysql query needs to return the following formatted result:

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

If multiple tasks are returned then each result should have a html break
appended as in the example below:

<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

My current query is this:

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)

It produces this res which is not what i want.

<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

Here is some additional information to assist with this:

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')

Upvotes: 0

Views: 90

Answers (2)

lptr
lptr

Reputation: 6788

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;

Upvotes: 0

Charlieface
Charlieface

Reputation: 71544

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

Upvotes: 2

Related Questions