honey honey - 4 months ago 12
SQL Question

How to Create Pivot in MS Access to avoid null values for one type of record?

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

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 Types

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