转换列名称以用作Access中的行字段 [英] converting column names to use as row fields in Access

问题描述

我正在将sql导入到Access数据库中，并且正在努力将数据解析为正确的表和字段.我的导入创建列名并将这些值输入到列中时遇到了一个问题，但是数据库将这些列名用作行值并将这些值放在单独的列中

I am importing sql into my Access database and am working on parsing the data into the correct tables and fields. I have run into an issue as my import creates column names and enters the values into the columns but the database uses those column names as row values and the values in a separate column

当前表

 SC | DO | temp | pH | etc

 val|val | val  |val | val

我试图解析的

表

table I am attempting to parse into

 Characteristic_Name | Result_Value

 SC                  |  val
 DO                  |  val
 temp                |  val
 pH                  |  val
 etc.                |  val

我已经完成了一个联合查询，以使结果列很好地填充，但是我无法获取要解析的列名称 有没有办法做到这一点?

I have done a union query to get the results column to populate nicely but I cannot get the column names to parse Is there a way to do this?

推荐答案

由于您正在使用MS Access，因此没有UNPIVOT函数，因此您可以使用UNION ALL查询:

Since you are using MS Access, there is no UNPIVOT function so you can use a UNION ALL query:

select 'SC' as Characteristic_Name, SC as Val
from yourtable
union all
select 'DO' as Characteristic_Name, DO as Val
from yourtable
union all
select 'temp' as Characteristic_Name, temp as Val
from yourtable
union all
select 'pH' as Characteristic_Name, pH as Val
from yourtable
union all
select 'etc' as Characteristic_Name, etc as Val
from yourtable;

作为旁注，当您执行UNION ALL或UNPIVOT时，数据类型必须相同，因此可能必须转换val列中的数据，使其相同.

As a side note, when you are doing a UNION ALL or UNPIVOT, the datatypes must be the same so you might have to convert the data in the val column so it is the same.

这篇关于转换列名称以用作Access中的行字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！