honey - 1 year ago 54

SQL Question

Can anyone please help me in creating the below pivot table in MS-Access.

Data table

`value | Rank | Type`

1.5 | 5 | alpha

2.4 | 4 | alpha

3.6 | 3 | alpha

4.63 | 2 | alpha

5.36 | 1 | alpha

Required Pivot is

`Type | 5 | 4 | 3 | 2 | 1`

alpha|1.5 |2.4 |3.6 |4.63 |5.36

I have tried the below query

`TRANSFORM [Value]`

SELECT [Type]

FROM tbl

GROUP BY [Type], [Value]

PIVOT [Rank];

and getting resultset as

`Type | 1 | 2 | 3 | 4 | 5`

alpha| | | | |1.5

alpha| | | |2.4|

alpha| | |3.6| |

alpha| |4.63| | |

alpha|5.36| | | |

Could anyone please help me in updating this query to get the required result.

Thanks a ton.

Honey

Answer Source

To get all "alpha" values in one row, you must remove `Value`

from the GROUP BY section.

Then you need an aggregation function for it - if you are sure to have only one value per rank, `First()`

does the job.

To get the 5-4-3-2-1 order, add an ORDER BY clause.

```
TRANSFORM First([Value])
SELECT [Type]
FROM tPivot
GROUP BY [Type]
ORDER BY [Rank] DESC
PIVOT [Rank];
```

Edit: it works for multiple `Type`

s

```
+-------+-----+-----+-----+------+------+
| Type | 5 | 4 | 3 | 2 | 1 |
+-------+-----+-----+-----+------+------+
| alpha | 1,5 | 2,4 | 3,6 | | 5,36 |
| beta | 999 | | | 4,63 | 66 |
+-------+-----+-----+-----+------+------+
```