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
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.
