PIVOT sql_variant into underlying dataypes

Jim Bennett

Does anyone know of a way to PIVOT an sql_variant column so that the resulting columns will be their proper underlying datatypes I wish this to be done in a View.
I currently do this by hard coding the conversion as follows:

SELECT A, B, C
MAX(CASE Letters WHEN 'D' THEN CONVERT(int, LetterValue) ELSE Null END AS D,
MAX(CASE Letters WHEN 'E' THEN CONVERT(datetime, LetterValue) ELSE Null END AS E,
MAX(CASE Letters WHEN 'F' THEN CONVERT(varchar, LetterValue) ELSE Null END AS F
FROM Alphabet
GROUP BY A, B, C

I would like to take advantage of the SQL_VARINIANT_PROPERTY(LetterValue, 'BaseType') function so I do away with the hard coding.
Any ideas

Waldrop

Jim:

Is this the transformation you are looking for:

create table dbo.alphabet
( A tinyint, -- Needs to be changed
B tinyint, -- Needs to be changed
C tinyint, -- Needs to be changed
Letters char(1),
LetterValue sql_variant
)
go

insert into dbo.alphabet values (1, 2, 3, 'D', 29)
insert into dbo.alphabet values (2, 2, 2, 'E', convert (datetime, '2/20/7'))
insert into dbo.alphabet values (3, 2, 1, 'F', 'This is a test.')
insert into dbo.alphabet values (4, 5, 6, 'F', 'Just another test.')
insert into dbo.alphabet values (5, 5, 5, 'D', 30)
go
--select * from alphabet

select A,
B,
C,
cast ( as integer) as ,
cast ( as datetime) as ,
cast ([F] as varchar) as [F]
from dbo.alphabet
pivot ( max(LetterValue)
for Letters in (,,[F])
) alphabetPivot

-- A B C D E F
-- ---- ---- ---- ----------- ----------------------- ------------------
-- 1 2 3 29 NULL NULL
-- 2 2 2 NULL 2007-02-20 00:00:00.000 NULL
-- 3 2 1 NULL NULL This is a test.
-- 4 5 6 NULL NULL Just another test.
-- 5 5 5 30 NULL NULL

Jim Bennett

That is the idea but I do not want to hard code the CAST. Somehow I would like to use the SQL_VARIANT_PROPERTY() function to determine the datatype during the PIVOT.
I was hoping for something like
PIVOT( max(CONVERT(SQL_VARIANT_PROPERTY(LetterValue, 'BaseType'), LetterValue)))
FOR Letters in ( Drinks

,

, [F])
Probably not possible unless the SQL is dynamic.

Waldrop

Jim:

You are correct that SQL_VARIANT_PROPERTY will not behave as you need it for this type of abstraction.

Jim Bennett

Thanks for your input.

Louis Davidson

You could probably do this with dynamic SQL, but why do you need this anyhow The data in the sql_variant will be of the proper type, so really wouldn't only the data user be the only one that needs to be concerned with the type of the data

PIVOT sql_variant into underlying dataypes

Re: PIVOT sql_variant into underlying dataypes

Re: PIVOT sql_variant into underlying dataypes

Re: PIVOT sql_variant into underlying dataypes

Re: PIVOT sql_variant into underlying dataypes

Re: PIVOT sql_variant into underlying dataypes