MS-Access或Excel:将数据从行转换为列的未知范围内的列 [英] MS-Access or Excel: Convert data from rows to columns, in unknown range of columns

问题描述

我有一个这样的表:

  ** customer_id product_id **1 12341 11251 65282 56452 55283 4565 

当我不知道需要多少列时，我想进行一次查询以提供相同的信息，其中每个使用者都有一行，并且每个product_id都位于单独的列中，因为每个使用者都有进行了不同的购买，这是我想要的结果:

  ** customer_id第一产品第二产品第三产品**1 1234 1125 65282 5645 55283 4565 

注意:

I have a table like this:

 **customer_id        product_id**        
  1                  1234        
  1                  1125        
  1                  6528    
  2                  5645       
  2                  5528       
  3                  4565        

And I want to make a query that will give the same information, where each consumer will have one row, and each product_id will be in a separate column, when I do not know how many columns are needed, because each consumer has made a different amount of purchases, This is the result I want:

**customer_id        First_product              Second_product              Third_product      **
  1                  1234                         1125                         6528
  2                  5645                         5528 
  3                  4565

Note this: How to concatenate text from multiple rows into a single text string in SQL server?

There he does a similar thing, but all the values are in the same column with a comma between them, I want each value to have a separate column.

解决方案

In Excel, you can do this using Power Query (available in Excel 2010+)

M-Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"customer_id"}, {{"Grouped", each _, type table [customer_id=nullable number, product_id=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Product", each Table.Column([Grouped],"product_id")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Product", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    
    #"Split Column by Delimiter" = Table.SplitColumn(
            #"Extracted Values", "Product", 
            Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv)),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Grouped"})
in
    #"Removed Columns"

这篇关于MS-Access或Excel:将数据从行转换为列的未知范围内的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！