How to change rows to columns
Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
RBoyd
6 - Meteoroid
‎08-01-2022
06:57 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I have data coming from a database that essentially has a row for each item in warehouse. If it is in multiple warehouses it has multiple rows. I would like to transform this data into each item only having one row, but with each warehouses information changed into columns.
Item code | DC | Status | Cost |
3556 | 1 | 5 | 22 |
3556 | 2 | 5 | 22 |
3556 | 3 | 2 | 24 |
4225 | 1 | 1 | 21 |
4225 | 2 | 1 | 21 |
4225 | 3 | 3 | 26 |
5332 | 1 | 2 | 20 |
5332 | 2 | 1 | 20 |
5332 | 3 | 4 | 12 |
Above is sample of what the pull from the database looks like. I have 3 separate warehouses (DC) and a status and cost associated with each. Below is how i would like the data to look.
Item code | DC 1 - Status | DC 2- Status | DC 3 - Status | DC 1 - Cost | DC 2 - Cost | DC 3 - Cost |
3556 | 5 | 5 | 2 | 22 | 22 | 24 |
4225 | 1 | 1 | 3 | 21 | 21 | 26 |
5332 | 2 | 1 | 4 | 20 | 20 | 12 |
Labels:
- Labels:
- Transformation
2 REPLIES 2
17 - Castor
‎08-01-2022
07:02 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @RBoyd
You can use the transformation tools to do this:
- Transpose Cost & Status
- Generate the header by concatenating DC & Name (Status or Cost)
- Crosstab the data back
- Rename the fields (crosstab adds underscores in place of spaces and special characters).
15 - Aurora
‎08-01-2022
07:11 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
