ms-access - 访问 2016 交叉表/转换

Question

我有桌子，我需要在水平模式下转换它

| id   | Number     | Name   |
|------|------------|--------|
| 3695 | 0445458225 | Name1  |
| 3695 | 0445458228 | Name2  |
| 3695 | 0445458553 | Name3  |
| 3695 | 0445458560 | Name4  |
| 3695 | 0445458551 | Name5  |
| 3695 | 0445458561 | Name7  |
| 3695 | 0445458522 | Name8  |
| 3695 | 0445458543 | Name9  |
| 3696 | 0445458226 | Name10 |
| 3696 | 0445458540 | Name11 |
| 3696 | 0445458543 | Name12 |
| 3696 | 0445923962 | Name13 |
| 3696 | 0500266382 | Name14 |
| 3697 | 0445923962 | Name15 |
| 3697 | 0445458226 | Name16 |
| 3697 | 0500266382 | Name17 |
| 3697 | 0445458564 | Name18 |

我得到它如下：

| id    | Name1     | Name2     | Name3 | Name4    | Name5      | Name6 | Name7 | Name8 | Name9    | Name10 | Name11    | Name12   | Name13   |  
|------ |-----------|---------- |-------|----------|----------- |-------|-------|-------|----------|--------|---------- |----------|----------|  
| 3695  | 445458553 | 44518551  |       | 44548560 |            |       |       |       | 44548228 |        | 44548543  |          | 44548225 |  
| 3696  |           |           |       |          | 445923962  |       |       |       |          |        | 44548543  | 44548226 |          |  
| 3694  |           |           |       |          | 445923962  |       |       |       |          |        | 44548543  | 44548226 |          |

但我需要的是这个：

| id   | Member1   | Member2  | Member3  | Member4  | Member5  | Member6  | Member7 | Member8 | Member9 | Member10 | member11 | Name12 | Name13 |  
|------|-----------|----------|----------|----------|----------|----------|---------|---------|---------|----------|----------|--------|--------|  
| 3695 | 445458553 | 44518551 | 44548560 | 44548228 | 44548543 | 44548225 |         |         |         |          |          |        |        |  
| 3696 | 445923962 | 44548543 | 44548226 |          |          |          |         |         |         |          |          |        |        |  
| 3694 | 445923962 | 44548543 | 44548226 |          |          |          |         |         |         |          |          |        |        |

所以没有空单元格，列应该命名为最后一个表，原始ID，每次查询运行时数字和名称都不同（不同的组ID）

这是我的 SQL 代码：

TRANSFORM Avg(Nick_agents.Number) AS FirstOfNumber
SELECT Nick_agents.id
FROM Nick_agents
GROUP BY Nick_agents.id
PIVOT Nick_agents.Name;

标签： ms-accesstransformcrosstab

Answer 1

Access 有一个内置的交叉表查询向导可以为您执行此操作。

On the Create tab, in the Queries group, click Query Wizard.
The Queries group in the Access ribbon displays two options: Query Wizard and Query Design

In the New Query dialog box, click Crosstab Query Wizard, and then click OK.

The Crosstab Query Wizard starts.

On the first page of the wizard, choose the table or query that you want to use to create a crosstab query. For this example, select the Products table and then click Next.

[![enter image description here][2]][2]

On the next page, choose the field that contains the values that you want to use as row headings. You can select up to three fields to use as row headings sources, but the fewer row headings you use, the easier your crosstab datasheet will be to read. If you choose more than one field to supply row headings, the order in which you choose the fields determines the default order in which your results are sorted.

For this example, select Supplier IDs.Value and then click the button labeled with a > symbol. Notice that Access displays the field name along the left side of the sample query preview at the bottom of the dialog box. Click Next to continue.
Select a field to display as row headings on the Crosstab query wizard.

On the next page, choose the field that contains the values that you want to use as column headings. In general, you should choose a field that contains few values, to help keep your results easy to read. For example, using a field that has only a few possible values (such as gender) might be preferable to using a field that can contain many different values (such as age).

If the field that you choose to use for column headings has the Date/Time data type, the wizard adds a step that lets you specify how to group the dates into intervals, such as months or quarters.

For this example, select Category and notice that Access displays category sample names along the top of the sample query preview at the bottom of the dialog box. Click Next to continue.
Select a field to display as row headings on the Crosstab query wizard.

If you choose a Date/Time field for column headings, the next page of the wizard asks you to specify the interval to use to group the dates. You can specify Year, Quarter, Month, Date, or Date/Time. If you do not choose a Date/Time field for column headings, the wizard skips this page.

On the next page, choose a field and a function to use to calculate summary values. The data type of the field that you select determines which functions are available.

On the same page, select or clear the Yes, include row sums check box to include or exclude row sums.

If you include row sums, the crosstab query has an additional row heading that uses the same field and function as the field value. Including a row sum inserts an additional column that summarizes the remaining columns. For example, if your crosstab query calculates average age by location and gender (with gender column headings), the additional column calculates the average age by location, across all genders.

For this example, select ID in the Fields box and Count in the Functions box in order to have Access count the number of products in each intersection of supplier and category. Leave the Yes, include row sums check box selected. Access will create a column that totals the number of products from each supplier. Click Next to continue.
Select a field and function to calculate on the Crosstab query wizard.

On the last page of the wizard, type a name for your query and then specify whether you want to view the results or modify the query design.

You can change the function that is used to produce row sums by editing the crosstab query in Design view.

If you've walked through this example using the Products table from the Northwind database, the crosstab query displays the list of supplier names as rows, the product category names as columns, and a count of the number of products in each intersection.

在您的示例中，您的数据完全不同。在第一个示例中，您将 Name1、Name2、Name3 作为字段名称，在第二个示例中，您将 Member1、Member2、Member3 作为字段名称。假设这是您的实际数据。

id  Number  Name
3697    445923962   Member1
3696    445923962   Member1
3695    445458553   Member1
3696    445458543   Member2
3697    445458543   Member2
3695    445458543   Member2
3695    445458551   Member2
3695    445458560   Member3
3696    445458226   Member3
3697    445458226   Member3

然后，这就是你的 SQL。

TRANSFORM First(Table1.[Number]) AS FirstOfNumber
SELECT Table1.[id], First(Table1.[Number]) AS [Total Of Number]
FROM Table1
GROUP BY Table1.[id]
PIVOT Table1.[Name];

这是设计视图。

当你运行 SQL 时，你会得到这个。

说得通？？？