ACCESS/SQL将多行与一列合并为一行并创建多列 [英] ACCESS/SQL Combining multiple rows with one column into one row and creating multiple columns

问题描述

我看了很多例子，但并没有完全适合我的需要.我有一个表，其中一列中有项目编号，而另一列中有图像链接.我遇到的问题是，我需要合并具有相同项目编号的行，但需要将HTML_LINK列中的数据移动到名为imagelink1，imagelink2，imagelink3的多个列中.我最多需要5个imagelink列.我尝试了一个数据透视表，该表可以合并行，但是会创建一个以图像链接名称命名的列，并且不会将它们移到5个imagelink列中的1个.

I've looked at quite a few examples and nothing fits quite like I need it to. I have a table that has item numbers in one column and image links in another column. The issue I have is I need to combine rows that have the same item number but need to move the data in the HTML_LINK column to multiple columns called imagelink1, imagelink2, imagelink3. The max amount of imagelink columns I will need is 5. I tried a pivot table which worked to combine the rows, but it creates a column the name of the image link and does not move them to 1 of 5 imagelink columns.

我现在所拥有的:

我需要什么:

推荐答案

一个 crosstab 似乎是遵循的路线，但是我无法按照 Parfait -必须使用 DCount :

A crosstab seems the route to follow, but I couldn't get the subquery to work as suggested by Parfait - had to use DCount:

TRANSFORM 
    First(q1.[html_link]) AS html_link
SELECT 
    [item]
FROM 

    (SELECT 
        item, 
        html_link, 
        DCount("*", "mytable", "item = '" & item & "' and html_link <= '" & html_link & "'") AS Index
    FROM 
        mytable) AS q1  

GROUP BY 
    [item] 
PIVOT 
    "ImageLink" & q1.[index];

清理"链接:

Left(Mid([html_link], InStrRev([html_link], "/") + 1), InStr(Mid([html_link], InStrRev([html_link], "/") + 1), ".") - 1) As Link

这篇关于ACCESS/SQL将多行与一列合并为一行并创建多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！