0

I want to cast a column to the datatype which is a text value in another column. I know I can do this with exec sp_executesql, but if I can do it without this, that would be ideal.

BEGIN TRY 
    DROP TABLE #1   
END TRY 
BEGIN CATCH 
END CATCH

CREATE TABLE #1
(    
    ID INT IDENTITY,
    DataType VARCHAR(20), 
    Data VARCHAR(MAX)   
)

INSERT INTO #1  
VALUES ('DATETIME', '20150404 12:34:56.789'),
       ('DATE', '20150404'),
       ('INT', '20150404')

SELECT
    *,
    IIF(T1.DataType = 'DATETIME', CAST(T1.Data AS DATETIME), NULL) Col1,
    IIF(T1.DataType = 'DATE', CAST(T1.Data AS DATE), NULL) Col2,
    IIF(T1.DataType = 'INT', CAST(T1.Data AS INT), NULL) Col3,
    IIF(T1.DataType = 'DECIMAL(14, 6)', CAST(T1.Data AS DECIMAL(14, 6)), NULL) Col4
FROM 
    #1 T1

and have something like this:

SELECT
    *,
    IIF(T1.DataType = 'DATETIME', CAST(T1.Data AS T1.DataType), NULL) Col1,
    IIF(T1.DataType = 'DATE', CAST(T1.Data AS T1.DataType), NULL) Col2,
    IIF(T1.DataType = 'INT', CAST(T1.Data AS T1.DataType), NULL) Col3,
    IIF(T1.DataType = 'DECIMAL(14, 6)', CAST(T1.Data AS T1.DataType), NULL) Col4
FROM 
    #1 T1

Or even something that automatically creates a new column for each different datatype. Realistically this would be normalized and have a DataType table with primary key IDs to be referenced.

Thank you in advance for everyone's help.

user2921429
  • 1
  • 1
  • 2
  • Possible duplicate of [Cast datatype with variable](http://stackoverflow.com/questions/11086381/cast-datatype-with-variable) – neer Jul 21 '16 at 18:14
  • I want to know if there is a way without putting my SQL statement into a text variable and executing it. Also, I want to have a table that has 3 columns: PK-Id, FK-DataTypeId, Data. that will make it so I could store the same value in Data, but cast it as a different DataType depending on the value of fkDataTypeId – user2921429 Jul 21 '16 at 20:01
  • In your last comment, are you saying you want different datatypes in one column? – HoneyBadger Jul 22 '16 at 12:12
  • no, I want a separate column for each datatype in the final query. The table will have everything saved as VARCHAR(MAX) and each record with have a VARCHAR(20) with the name of what datatype it should be. Thanks! – user2921429 Jul 25 '16 at 15:02

0 Answers0