Query-generated Column Expressions

Question

Query-generated Column Expressions

Bill Nicolich

SSCommitted

Points: 1865
More actions
May 24, 2010 at 9:14 pm

#222853

Comments posted to this topic are about the item Query-generated Column Expressions
Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Bill Nicolich SSCommitted Points: 1865 More actions · Answer 1

Hey everyone. Not quite sure what happened, but the scripts in the article need a modification so they use a token for the column name: <column>.

INSERT INTO #ExpressionToDataTypeAndNull

( DATA_TYPE ,

IS_NULLABLE ,

CustomExpression

)

VALUES

( 'varchar','YES','LTRIM(RTRIM(ISNULL("<column>",''''))) AS "<column>",')

,( 'varchar','NO','LTRIM(RTRIM("<column>")) AS "<column>",')

,( 'nvarchar','YES','LTRIM(RTRIM(ISNULL("<column>",''''))) AS "<column>",')

,( 'nvarchar','NO','LTRIM(RTRIM("<column>")) AS "<column>",')

,( 'datetime','YES','CAST("<column>" AS varchar(max)) AS "<column>",')

,( 'datetime','NO','CAST("<column>" AS varchar(max)) AS "<column>",')

;

and

DECLARE @TableName nvarchar(128)

SET @TableName = 'FictionalEmployees'

SELECT

ISNULL(REPLACE(CustomExpression,'<column>', COLUMN_NAME), COLUMN_NAME + ' AS ' + COLUMN_NAME) AS ThisColumnExpression

FROM INFORMATION_SCHEMA.COLUMNS a

FULL OUTER JOIN #ExpressionToDataTypeAndNull b

ON a.DATA_TYPE = b.DATA_TYPE

AND a.IS_NULLABLE = b.IS_NULLABLE

WHERE TABLE_NAME = @TableName

ORDER BY ordinal_position ASC;

Once you have the token <column> replaced by the column name, your output includes the column names as advertised in the results.

Sorry about that! I'm not sure what happened there.

Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow

ddriver SSC Veteran Points: 255 More actions · Answer 2

Try to specify the alises all in one place. Save yourself some typing. Also build the full select starement. Also could be used as a create view statement. Interesting idea though.

INSERT INTO #ExpressionToDataTypeAndNull

( DATA_TYPE ,

IS_NULLABLE ,

CustomExpression

)

VALUES

( 'varchar','YES','LTRIM(RTRIM(ISNULL("<column>",'''')))')

,( 'varchar','NO','LTRIM(RTRIM("<column>"))"')

,( 'nvarchar','YES','LTRIM(RTRIM(ISNULL("<column>",'''')))')

,( 'nvarchar','NO','LTRIM(RTRIM("<column>"))')

,( 'datetime','YES','CAST("<column>" AS varchar(max))')

,( 'datetime','NO','CAST("<column>" AS varchar(max))')

;

DECLARE @TableName nvarchar(128)

SET @TableName = 'FictionalEmployees'

DECLARE @SqlSelect NVARCHAR(MAX)

SELECT @SqlSelect = COALESCE(

@SqlSelect + ', ' + ISNULL(REPLACE(CustomExpression,'<column>', COLUMN_NAME), COLUMN_NAME )

, ISNULL(REPLACE(CustomExpression,'<column>', COLUMN_NAME), COLUMN_NAME )

) + ' AS "' + COLUMN_NAME + '"'

FROM INFORMATION_SCHEMA.COLUMNS a

FULL OUTER JOIN #ExpressionToDataTypeAndNull b

ON a.DATA_TYPE = b.DATA_TYPE

AND a.IS_NULLABLE = b.IS_NULLABLE

WHERE TABLE_NAME = @TableName

ORDER BY ordinal_position ASC;

SET @SqlSelect = 'SELECT ' + @SqlSelect + 'FROM ' + @TableName

PRINT @SqlSelect

-- EXECUTE sp_executesql @SqlSelect

Nick Chadwick SSC Enthusiast Points: 109 More actions · Answer 3

Hi.

An interesting article! I just thought I'd point out that:

"FULL OUTER JOIN allows the query to return columns that don't have a match in the #ExpressionToDataTypeAndNull table"

is not 100% right. What you are describing is a LEFT outer join.

A FULL outer join would return results in the opposite case too - i.e. if there were row(s) in the #ExpressionToDataTypeAndNull table for which there is no match in INFORMATION_SCHEMA.COLUMNS.

However, as you are specifying in your WHERE clause that TABLE_NAME = @TableName, you are effectively converting your full outer join into a left outer join, (perhaps unknowingly!)

Just wanted to clarify that, in case other readers find it misleading!

Keep up the good work.

Cheers,

Nick

Bill Nicolich SSCommitted Points: 1865 More actions · Answer 4

Nick Chadwick (5/25/2010)
Hi.
An interesting article! I just thought I'd point out that:

Thanks Nick! You put it nicely. It's very much the case here at SSC.COM that ones peers have enough passion and interest to find something if it's amiss or point out nuances. That's one reason why I like to contribute - for the challenge and learning experience of it.

If you don't believe me, put something in and give it a shot!

I didn't want to lengthen the article too much, but one point I'll add is that with this technique, one can build a little library of functions and keep it handy for those ETL, Ad-hoc or other situations.

Bill Nicolich: www.SQLFave.com.
Daily tweet of what's new and interesting: AppendNow

Douglas Osborne-456728 SSCommitted Points: 1569 More actions · Answer 5

Nick,

This seemed to work a little better for me:

CREATE TABLE #ExpressionToDataTypeAndNull

(

DATA_TYPEVARCHAR(15),

IS_NULLABLEVARCHAR(3) ,

CustomExpressionVARCHAR(128)

)