How to join my table with information_schema.columns

i need to join my table with information_schema.columns to 开发者_开发技巧get data type for all column.

my sql is following.

select stock_code,automatic,semiautomatic,manual from VehicleInfoForParts

i want to join my VehicleInfoForParts with information_schema.columns to get a output like

Column_Name       Value               datatype
-------------     -------            --------------             
stock_code           A112                varchar
automatic            1                   bit
semiautomatic        0                   bit
manual               1                   bit

please advise. thanks .

You can't achieve that with a simple join (you basically want to "join" columns with rows...). As I said in your earlier question, solve your problem with the bit columns in the application, not in the database. That has multiple advantages, including

• It works for every table holding bit columns if you keep your data model consistent
• You don't store language specific texts in your SQL code, with makes translating your application easier