Geek asks and answers

In sql server how to get column values ​​with `` '' separate

In sql server, how can I get column c values ​​by <br>separating them?

Here I get the comma separation, but how can I get <br/>in html in sql server?

SELECT
  STUFF(
    (
    SELECT
      ',' + cast(Citation_Id as nvarchar(500))
    FROM tollplus.violated_trips
    FOR XML PATH('')
    ), 1, 1, ''
  ) As CitationId
+3
source share
2 answers

try the following:

SELECT
    STUFF(
             (SELECT
                  '<br/>' + cast(Citation_Id as nvarchar(500))
                  FROM tollplus.violated_trips
                  FOR XML PATH(''), TYPE
             ).value('.','varchar(max)')
             ,1,5, ''
         ) AS Citation_Id
+1
source

To avoid <br />appearing like&lt;br /&gt;

SELECT  (     
        SELECT CASE
                 WHEN ROW_NUMBER() OVER (ORDER BY (SELECT 0)) = 1 THEN ''
                 ELSE '<br />'
               END + cast(Citation_Id as nvarchar(500))
        FROM   tollplus.violated_trips
        FOR XML PATH(''), TYPE  
        ).value('.','nvarchar(max)')  As CitationId

Although in fact, if it CitationIditself does not contain any problematic characters, the following may be more effective if you can consider the estimated costs in terms of implementation.

SELECT
  REPLACE(
    (
        SELECT CASE
                 WHEN ROW_NUMBER() OVER (ORDER BY (SELECT 0)) = 1 THEN ''
                 ELSE ','
               END + CAST(Citation_Id AS NVARCHAR(500))
        FROM   violated_trips
        FOR XML PATH('')  
    ), ',', '<br />'
  ) AS CitationId

Execution plan

+1
source

More articles:


All Articles