Query on sql server to concatenate all selects

Question

I'm trying to concatenate all rows of a query in one result... but when i do it, it shows that the result is too long...

First i tryed:

SELECT STRING_AGG(LINE, ' - ') FROM (SELECT TOP(8)
                CONCAT('<td class="text-center">',ID,'</td><td class="text-center">',
                        CODIGO_META,'</td><td class="text-center">', 
                        ANO,'</td>') AS LINE
            FROM METAS ORDER BY CODIGO_META) as ALLINONE

Then this error appears:

Msg 9829, Level 16, State 0, Line 1 STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation.

Well, i tryed then create a local var of other type (TEXT) to receive it all. It becames like it:

DECLARE @final TEXT;

SET @final = (SELECT STRING_AGG(LINE, ' - ') FROM (SELECT TOP(8)
                CONCAT('<td class="text-center">',ID,'</td><td class="text-center">',
                CODIGO_META,'</td><td class="text-center">', 
                ANO'</td>') AS LINE
            FROM METAS ORDER BY CODIGO_META) as ALLINONE)

But i received this error:

Msg 2739, Level 16, State 1, Line 1 The text, ntext, and image data types are invalid for local variables.

How can i do it? "/

You're trying to generate HTML, not concatenate results. It's a LOT easier and far cheaper to use a client-side HTML generator for this. — Panagiotis Kanavos, Jan 11 '23 at 11:53
@Antharaz it only covered it up, it didn't solve it. The query itself is a problem. There are fare better ways to do this, by any measure. ASP.NET and T4 templates in .NET can easily generate HTML from data. In Python, Jinja and countless other template engines can do the same. All supported SQL Server versions can call Python scripts which means you can generate HTML with a proper engine with 5-6 lines — Panagiotis Kanavos, Jan 11 '23 at 11:56

Query on sql server to concatenate all selects

0 Answers0