CODEWITHSUNDEEP
sqlsql-server
nick
nick

Reputation: 333

More than VARCHAR(MAX) values in SQL Server

I have some tables with more than 700 columns/1000 columns.

Now I want to display all columns from this table to ISNULL(col1,0) format because when I use PIVOT/UNPIVOT and if there are some NULL values then they wont be converted to NULL and becomes empty strings. So I am trying to replace those NULLs with 0.

In this example I used sysobjects table so that you can try it in your ssms. The result of this is incomplete as neither VARCHAR(MAX) nor NVARCHAR(MAX) is enough. How do I get all rows rather than few rows here?

DECLARE @colsUnpivot VARCHAR(MAX)

SET @colsUnPivot = STUFF((
                       SELECT ',' +  'ISNULL(' + QUOTENAME(name) + ', 0) AS ' 
                                  + QUOTENAME(name) 
                       FROM  sysobjects t  
                       FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') 
PRINT @colsUnPivot

 set @query = 'SELECT id,code_name,lkp_value
 FROM  
 (
 SELECT unitid,institution,city,state,zip, '+ @colsUnpivot+'
  FROM sysobjects) AS cp
  UNPIVOT (lkp_value for code_name  IN ('+@colsUnPivot+')
  ) AS up' 
 --PRINT @Query
 --PRINT @Query1
   exec(@query)

I mean the code above does not make sense but I can not produce same thing that I have as i have to use sysobjects here.

But above code is throwing an error:

Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '('.

And that's because there is so much data and it's being truncated.

Here is what my PRINT says,

    ,ISNULL([opd_

So I still think its truncating.

Upvotes: 2

Views: 1705

Answers (2)

WayTooSerious
WayTooSerious

Reputation: 66

Use SELECT instead of print in your SQL.

SELECT @colsUnPivot

Also, make sure that these values are maxed out in Results to Grid: Results to Grid Options

Upvotes: 1

jlewis
jlewis

Reputation: 131

Your problem is that the PRINT command will truncate the data for display in SSMS.

I would suggest leaving it as XML and doing a SELECT, if you just need to see the data in SSMS without truncating:

   DECLARE @colsUnpivot xml

   SET @colsUnPivot = (SELECT ',' +  'ISNULL(' + QUOTENAME(name) + ', 0) AS ' 
                                  + QUOTENAME(name) 
                       FROM  sysobjects t  
                       FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)')

   SELECT @colsUnPivot

SSMS treats XML output differently and has a higher threshold for truncating the data.

Upvotes: 2

Related Questions