MS-Access or Excel: Convert data from rows to columns, in unknown range of columns

Question

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.

I provided an answer in another question you deleted. I tested the query and it worked for me. Same answer can be applied here only instead of Balance as data you have product_id. — June7, Sep 23 '20 at 17:10

June7 · Answer 1 · 2020-09-23T17:27:28.213

0

Consider:

TRANSFORM First(Table1.product_id) AS ProdID
SELECT Table1.customer_id
FROM Table1
GROUP BY Table1.customer_id
PIVOT DCount("*","Table1","customer_id=" & [customer_id] & " AND [product_id]<" & product_id)+1;

If there is a unique record identifier field (autonumber should serve):

TRANSFORM First(Table1.product_id) AS ProdID
SELECT Table1.customer_id
FROM Table1
GROUP BY Table1.customer_id
PIVOT DCount("*","Table1","customer_id=" & [customer_id] & " AND [id]<" & id)+1;

Or use order date field instead of ID field in DCount() expression.

edited Sep 23 '20 at 17:27

answered Sep 23 '20 at 17:19

June7

19,874
8
24
34

Thanks a lot, I'll check it out again in Access – Elicon Sep 24 '20 at 09:47

score 0 · Accepted Answer · answered Sep 23 '20 at 18:57

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 or Excel: Convert data from rows to columns, in unknown range of columns

2 Answers2

Linked