SQLTeam.com | Weblogs | Forums

Building dynamic columns not get last columns complete why?

I work on sql server 2012 i build rows as columns dynamically

but my issue not all rows display as columns on final result separated comma

there are remaining rows and last columns not complete why and how to solve it

to more clear last column must be [Competitor Total Harmonic Distortion]

it display [Competitor To

are this issue of length or what I don't know

can any one help me please

this is my statement generate issue

DECLARE @result NVARCHAR(MAX) =  
      stuff((( Select  ',['+FeatureName +']' 
     FROM extractreports.dbo.ctegroupfeatur with(nolock) 
      GROUP BY FeatureName,displayorder
      ORDER BY  (CASE WHEN displayorder IS NULL THEN 1 ELSE 0 END) asc,displayorder,FeatureName asc
                            FOR XML PATH(''), TYPE).value('.','NVARCHAR(max)'))
                      ,1,1,'') 
                      print @result

and this image show issue exist on last column not surounded with []
and not complete and another columns following not come
so How to solve issue


ddl and schema insert and table

1 Like

instead of

print @result

try

select @result

what do you see. Not sure what you are attempting to do with this comma delimited column. What process is consuming this comma delimited column?

The problem can be column width settings and the fact that PRINT is always limited. Here's the fix for all of that., Details on usage are in the flower box.

CREATE FUNCTION [dbo].[ShowLongString]
/**********************************************************************************************************************
 Purpose:
 Display a string of more than 8000 characters.  The string can be Dynamic SQL, XML, or just about anything else.

 Usage:
--===== Example with Dynamic SQL
DECLARE @SQL VARCHAR(MAX);
 SELECT @SQL = '
 SELECT somecolumnlist
   FROM some table with joins
;'
;
 SELECT LongString 
   FROM dbo.ShowLongString(@SQL)
;
--===== Example with a call to a table or view
 SELECT sm.Object_ID, Definition = ls.LongString 
   FROM sys.SQL_Modules sm
  CROSS APPLY dbo.ShowLongString(sm.Definition) ls
;
Revision History:
Rev 00 - 20 Sep 2013 - Jeff Moden - Initial creation and test.
**********************************************************************************************************************/
--===== Declare the I/O for this function
        (@pLongString VARCHAR(MAX))
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
 SELECT LongString =
        (
         SELECT REPLACE(
                    CAST(
                        '--' + CHAR(10) + @pLongString + CHAR(10)
                    AS VARCHAR(MAX))
                ,CHAR(0),'') --CHAR(0) (Null) cannot be converted to XML.
             AS [processing-instruction(LongString)] 
            FOR XML PATH(''), TYPE
        )
;
1 Like

thanks

What I was trying to say in my previous post is that you probably just have a problem displaying the content of your dynamic SQL because of its length. The function I posted above easily works around that.