I have a set of data that has multiple different data types including one field that is a nvarchar(max) field. I need to unpivot the data but of course I am getting the pesky error "The type of column "myColumn" conflicts with the type of other columns specified in the UNPIVOT list.
I am wondering if it is possible in the dynamic unpivot I have written below to CAST all of the columns as nvarchar(max) to allow for the unpivot.
Here is what I have written:
declare @cols nvarchar(max)
select @cols = coalesce(@cols+N',', N'') + quotename(c.name) from syscolumns c
inner join sysobjects o on c.id = o.id and o.xtype = 'u'
where o.name = 'myTable' and c.name not in ('ID' , 'Email','Name') order by c.colid
declare @query nvarchar(max)
select @query = N'
select ID, email,Name, Col as ID, Val
from
(
select ID , Email,Name, ' + @cols + '
from myTable
) as cp
unpivot
(
Val for Col in (' + @cols + ')
) as up
'
exec sp_executesql @query
Any help is greatly appreciated.
bwp
Something like this i think should work:
USE [Admin];
GO
SELECT
*
FROM[myTable];
DECLARE @CastedCols NVARCHAR(MAX);
SELECT
@CastedCols = COALESCE( @CastedCols + N','
, N''
) + N'CAST(' + QUOTENAME([c].[name]) + N' AS NVARCHAR(MAX)) AS ' + QUOTENAME([c].[name])
FROM[sys].[syscolumns] AS [c]
INNER JOIN [sys].[sysobjects] AS [o]
ON [c].[id]= [o].[id]
AND [o].[xtype] = 'u'
WHERE[o].[name] = 'myTable'
AND [c].[name] NOT IN (
'ID'
, 'Email'
, 'Name'
)
ORDER BY[c].[colid];
DECLARE @cols NVARCHAR(MAX);
SELECT
@cols = COALESCE(@cols + N','
, N''
) + QUOTENAME([c].[name])
FROM[sys].[syscolumns] AS [c]
INNER JOIN [sys].[sysobjects] AS [o]
ON [c].[id]= [o].[id]
AND [o].[xtype] = 'u'
WHERE[o].[name] = 'myTable'
AND [c].[name] NOT IN (
'ID'
, 'Email'
, 'Name'
)
ORDER BY[c].[colid];
DECLARE @query NVARCHAR(MAX);
SELECT
@query = N'
select [GE - Person ID], [Deloitte Email Address],[Employee Name], Col as ID, Val
from
(
select ID , Email,Name, ' + @CastedCols + N'
from myTable
) as cp
unpivot
(
Val for Col in (' + @cols + N')
) as up
';
EXEC [sys].[sp_executesql]
@query;
Basically, I make a second variable to store the "casted" version of @cols when building that string. I ran SQL Format on it so the code is easier to debug too.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 26, 2020 at 10:15 pm
Hi Brian, Thank you for this above, I can see what you have done but I tried to run it and it is not unpivoting the data for me, any idea what I could be doing wrong? Thanks again so much for the help above.
August 26, 2020 at 10:26 pm
Since you're importing from a spreadsheet, do you really need a VARCHAR(MAX) column? This question also has pertinence to your other post on the subject.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 26, 2020 at 10:33 pm
Hi Jeff, yes it will since I am sure it will, at some point of the data owners is going to add a comments field which will end up being nvarchar(max) field, I just reused the file I had for the other one adding a comments field since I already had it.
August 26, 2020 at 10:38 pm
Brian, I figured out what I did wrong, sorry for the confusion on this and thank you so much for the help, I will remember from now on if I need to cast the columns to add the extra variable. This has helped my education with SQL so much.
bwp
August 27, 2020 at 2:54 am
I prefer to use SQL_VARIANT instead of NVARCHAR(MAX) for generic columns.
It keeps numbers as numbers, datetimes as datetimes, avoiding potentially dangerous conversions to character strings and back.
N'CAST(' + QUOTENAME([c].[name]) + N' AS NVARCHAR(MAX)) AS ' + QUOTENAME([c].[name])
-- replace with
N'CONVERT(SQL_VARIANT, ' + QUOTENAME([c].[name]) + N') AS ' + QUOTENAME([c].[name])
And the script does not account for different schemas which might be used.
Instead of
[o].[name] = 'myTable'
it's better to use
[o].id = object_Id('[dbo].[myTable]')
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply