SQL Query for field name, field type, and field value

Question

Let's say I have a table like the one below

pp_refno   pp_rectype   _pp_account  pp_empid   pp_dept
---------------------------------------------------------
90221      contractor    061509864   C00001121  1111000139

Is there a query that could be written such that the data is returned in this format?

field_name          field_type     field_value
---------------------------------------------------------
pp_refno            int             90221
pp_rectype          char            contractor
pp_account          char            061509864
pp_empid            char            C00001121 
pp_dept             char            1111000139

Not in "standard SQL", however it's probably possible if you narrow it down to a specific database system dialect. — Joachim Isaksson, Apr 05 '16 at 19:10
the query varies depending on the database you are using. Please specify your database. — atomCode, Apr 05 '16 at 19:14

score 0 · Answer 1 · answered Apr 05 '16 at 19:10

Yes, that is possible by using the information schema of the database you are using. You do however have to add 1 column to be able to use this for multiple rows:

field_name          field_type     field_value        pp_refno
--------------------------------------------------------------
pp_refno            int             90221             90221
pp_rectype          char            contractor        90221
pp_account          char            061509864         90221
pp_empid            char            C00001121         90221
pp_dept             char            1111000139        90221

This way you can identify multiple records.

Also you will have to write or generate a specific query for each table, which can be large and slow due to the way the data works.

If you are just retrieving single rows, you could also use a stored procedure to list your data in the given fashion.

score 0 · Answer 2 · edited May 23 '17 at 12:24

You could use UNPIVOT function of SQL query to achieve this behavior. Below link gives you more idea with the similar example.

Example Stackoverflow Link

To read the column type, you need to join the sys tables and get the column types

SELECT DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
 TABLE_NAME = 'yourTableName' AND 
 COLUMN_NAME = 'yourColumnName'

Once you have the unpivot table alias, you should be able to join the same with SCHEMA.columns table and get your logic implemented

SQL Query for field name, field type, and field value

2 Answers2