Databases
StackOverflow2013
Postsdbo
POSQL Server 2008: PIVOT throwing an error 'The conversion of the varchar value 'xxx' overflowed an int column.'
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server 2008: PIVOT throwing an error 'The conversion of the varchar value 'xxx' overflowed an int column.'
    text
    copied!<p>I'm using the SQL Server 2008 <code>PIVOT</code> function after I turn a CSV field into a table using a UDF. </p> <p>For the purpose of showing this error, I'm eliminating the first step which is <code>CROSS APPLY</code>ing the CSV field to a UDF to get the table I'm manually creating in the code example below.</p> <pre><code>CREATE TABLE #TCMDRES ( SYS_NR BIGINT, DAT_TE VARCHAR(150), ID CHAR(1) ) -- ,,,,,,WIN ASDF v2,20100406.BAK,32515325772,32514331136, -- After being separated and put into columns 1-11... INSERT INTO #TCMDRES VALUES(1,'','X'),(2,'','X'),(3,'','X'),(4,'','X'),(5,'','X'),(6,'','X'),(7,'WIN ASDF v2','X'), (8,'20100406.BAK','X'),(9,'32515325772','X'),(10,'32514331136','X'),(11,'','X') SELECT * FROM #TCMDRES -- This errors out with: -- Msg 248, Level 16, State 1, Line 16 -- The conversion of the nvarchar value '32514331136' overflowed an int column. SELECT ID, [8] AS FIL_NA, [10] AS FIL_SZ_NR FROM ( SELECT DAT_TE, SYS_NR, ID FROM #TCMDRES a ) a PIVOT (MAX(DAT_TE) FOR SYS_NR IN ([8],[10])) AS pvt WHERE [10] &lt;&gt; 0 DELETE FROM #TCMDRES -- Try again but drop the last two digits from column 10... INSERT INTO #TCMDRES VALUES(1,'','X'),(2,'','X'),(3,'','X'),(4,'','X'),(5,'','X'),(6,'','X'),(7,'WIN ASDF v2','X'), (8,'20100406.BAK','X'),(9,'32515325772','X'),(10,'325143311','X'),(11,'','X') SELECT ID, [8] AS FIL_NA, [10] AS FIL_SZ_NR FROM ( SELECT DAT_TE, SYS_NR, ID FROM #TCMDRES a ) a PIVOT (MAX(DAT_TE) FOR SYS_NR IN ([8],[10])) AS pvt WHERE [10] &lt;&gt; 0 DROP TABLE #TCMDRES </code></pre> <p>To summarize, if you attempt to <code>PIVOT</code> on a large value even when it's stored as a <code>VARCHAR</code>, it fails trying to convert it to an <code>INT</code>.</p> <p>Does anyone have any ideas on how to fix this or if it's fixable? Seems to be a limitation in the <code>PIVOT</code> function...</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload