Splitting Single Row into Multiple Rows Based on Columns
Moderators: chulett, rschirm, roy
Splitting Single Row into Multiple Rows Based on Columns
I've browsed around the forums and haven't found a situation like this one. We have each row from an input SQL server table that needs to be split into two based on two columns.
For example, the input row looks like this (code with budgets for a year):
Code.....Year12-13.....Year13-14
303.......60000...........15000
I need to turn that into:
Code.....Year.....Budget
303.......2012.....60000
303.......2013.....15000
Has anyone solved a situation like this?
Thanks!
For example, the input row looks like this (code with budgets for a year):
Code.....Year12-13.....Year13-14
303.......60000...........15000
I need to turn that into:
Code.....Year.....Budget
303.......2012.....60000
303.......2013.....15000
Has anyone solved a situation like this?
Thanks!
-
- Participant
- Posts: 246
- Joined: Mon Jun 30, 2008 3:22 am
- Location: New York
- Contact:
-
- Participant
- Posts: 246
- Joined: Mon Jun 30, 2008 3:22 am
- Location: New York
- Contact:
You mean to say only the value of the years will change and the number of columns will remain the same? What is the indication you will have w.r.t the year?
I'm not sure if pivot can handle this, though the first thought comes into the mind is pivot after reading the requirement
As you are in 9.1, you can play around the loop variables and try to get it done.
I'm not sure if pivot can handle this, though the first thought comes into the mind is pivot after reading the requirement
![Laughing :lol:](./images/smilies/icon_lol.gif)
As you are in 9.1, you can play around the loop variables and try to get it done.
Arun
-
- Participant
- Posts: 246
- Joined: Mon Jun 30, 2008 3:22 am
- Location: New York
- Contact:
Okay.. Sounds like a kinda dynamic pivot :D
If you not gonna be dealing with a lot of rows, write your records to a file as csv. Read the entire record as one single column. Your ground zero year is 2012(You can even hardcode this value) and add one to it for every iteration to get the year and put a field function to extract the amount from the csv string and keep adding one to it for every iteration to extract the next value.
Your loop can be set to a value of count of ',' in your string (You dont have to add one to the count if you include your CODE column in the source as well)
There might be better ways, but If I was you, I will do this.
If you not gonna be dealing with a lot of rows, write your records to a file as csv. Read the entire record as one single column. Your ground zero year is 2012(You can even hardcode this value) and add one to it for every iteration to get the year and put a field function to extract the amount from the csv string and keep adding one to it for every iteration to extract the next value.
Your loop can be set to a value of count of ',' in your string (You dont have to add one to the count if you include your CODE column in the source as well)
There might be better ways, but If I was you, I will do this.
Arun
Between our responses, I went with your first suggestion and have splitted them out at the transformer. Using Peek stages, the rows look correct. Should I use Join or Merge after this? Also, is it a good practice to have two links go from a Transformer straight to a Join/Merge or should there be some sort of dataset/file between those stages to hold the rows?
Also, I'm going to look at doing this with a loop as you later suggest as that will better suit long term growth. However, I'd like to get these initial two years loaded as quick as possible.
Also, I'm going to look at doing this with a loop as you later suggest as that will better suit long term growth. However, I'd like to get these initial two years loaded as quick as possible.
-
- Participant
- Posts: 246
- Joined: Mon Jun 30, 2008 3:22 am
- Location: New York
- Contact: