问题描述
让我开始说，我知道可以使用TRANSFORM命令将行转换为列.实际上，我已经在这个很棒的网站上了解到，有足够的理由让我注册:)
Let me start to say that I know that transforming rows to columns can be done with the TRANSFORM command. Actually, I've learned that on this great site, reason enough for me to register :)
我遇到的问题是，我想将行转换为列，而行中的每个值都不是唯一的列.我将尝试通过我的Access表示例对其进行解释，请参见链接中的图片:
The problem I have is that I would like to transform rows into columns without every different value in the row is going to be a unique column.I'll try to explain it by an example of my Access table, see the picture in the link:
此查询显示产品(第一列loar_aid)，并带有描述产品的文字片段.每个产品都有一个值tekst1，tekst2和tekst3.但是tregel1列最多可以为1个产品包含3个不同的文本.如果您知道我的意思，我想将此tregel1转换为3列:tregel1_firstValue，tregel1_secondValue，tregel1_thirdValue.
This query shows products (first column loar_aid), with pieces of text to describe the product. Every product has one value tekst1, tekst2 and tekst3.But column tregel1 can contain up to 3 different pieces of text for 1 product. I would like to transform this tregel1 to 3 columns: tregel1_firstValue, tregel1_secondValue, tregel1_thirdValue, if you know what I mean.
最终，我希望使用此表作为源标签(每列都是标签上的规则).因此，理想情况下，这些值应该彼此相邻.
Eventually I would like to make labels with this table as a source (every column a rule on the label). Therefore the values should ideally be next to each other.
有人可以帮助我解决这个问题吗?帮助将不胜感激！
Could someone help me with this problem?Help will be greatly appreciated!
推荐答案
您需要每个唯一组的记录序列ID.以下内容依靠自动编号ID字段为原始表中的每个记录提供唯一的标识符，以定义DCount()表达式中的排序顺序.
You need a record sequence ID for each unique group. The following relies on an autonumber ID field to provide a unique identifier for each record in the original table to define sort order in DCount() expression.
TRANSFORM First(Table3.tregel1) AS FirstOftregel1
SELECT Table3.loar_aid, Table3.tekst2, Table3.tekst3
FROM Table3
GROUP BY Table3.loar_aid, Table3.tekst2, Table3.tekst3
PIVOT DCount("*","Table3","loar_aid='" & [loar_aid] & "' AND tekst1='" & [tekst1] & "' AND tekst2='" & [tekst2] & "' AND Nz(tekst3,'None')='" & Nz([tekst3],"None") & "' AND ID<" & [ID])+1;
请注意，使用DCount()或嵌套子查询的这种类型的查询在大型数据集上的执行速度会很慢.
Be aware this type of query using DCount() or a nested subquery can perform slowly on very large dataset.
这篇关于访问:将行转换为具有许多不同行值的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！