Using Cross Tab to convert columns to rows

[robertcox]

I have a dataset where each record has an ID and 4 columns for income.  I am trying to use the Cross Tab tool with a summarize to select the highest value and return the column name for the new value.

So, basically, I want to turn this: 

ID	High_Income	Low_Income	Mid_High_Income	Mid_Low_Income
5170668	2	1	8	3
5170669	41	2	14	15
5174294	2	7	22	5
5174295	5	11	3	9

Into this:

ID	Value
5170668	Mid_Income
5170669	High_Income
5174294	Mid_High_Income
5174295	Low_Income

I have tried to use the cross tab with the ID as the Key and my 4 income columns as Data Columns, then summarized and grouped by ID, then Group By Name, then Max Value, but I am getting 4 records for each ID.

[patrick_digan · Accepted solution]

@robertcox I would do it like this (transpose, sort and then sample).