Splitting a recordset row into multiple smaller value typed rows

Question

Splitting a recordset row into multiple smaller value typed rows

Richard Cook-414448

SSC Enthusiast

Points: 134
More actions
March 14, 2007 at 12:18 pm

#116414

I have a dataset of electrical readings. These readings may come from either single phase, three phase or split phase equipment, hence my problem. I have been asked to split the readings coming from each record into a table containing one reading along with its type; i.e. ValueTypeID and MeasuredValue. Here is an example of a row in the original dataset.

DataID  InVoltA2B   InVoltB2C  InVoltC2A   OutVoltA2B       OutVoltB2C      OutVoltC2A
1234         110            112           114               109                  112                 113
1235         220            Null           Null                220                  Null                 Null

The destination records should appear as follows. Note that Null measured values are not entered as rows in the destination database.
DataID              ValueTypeID                 MeasuredValue
1234                 InputVoltage_A2B                     110
1234                 InputVoltage_B2C                     112
1234                 InputVoltage_C2A                     114
1234                 OutputVoltage_A2B                   109
1234                 OutputVoltage_B2C                  112
1234                 OutputVoltage_C2A                  113
1235                 InputVoltage_A2B                     220
1235                 OutputVoltage_A2B                   220

I have tried to use a conditional split on each not IsNull column; however, it sends all the data down one stream; i.e. the first that not IsNull path. It never looks at the second condition since the first is met. I have tried using the Multicast and then check the condition. That seems to send all records to each subsequent conditional split to test for IsNull. In that case all the data goes through to the first split condition but not to the next. See Figure Below. The destinations at the bottom are SQL Server Database one for each path. I tried a union and a merge but they were not working for me either.
I am looking for the best way to accomplish this task.

Richard

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

S. Kusen SSChampion Points: 10924 More actions · Answer 1

Have you tried looking into the Unpivot component for the data flow? I haven't had much experience with it, but from my experience it should help you with what you're doing. It's worth looking in to.

It takes a data set and then makes it more normalized, which appears to be what you're doing.

If you do get it working with that component, I would be interested in seeing how you configured it.

Richard Cook-414448 SSC Enthusiast Points: 134 More actions · Answer 2

Thanks Stephen,

I managed to resolve this issue with a multicast going to conditional splits to remove the nulls for the column in question for each path. I then messaged the data in a derived column task adding the ValueTypeID based onthe column and finally wrote each path to a separate destination SQL Server which are really setup pointing to the same table. This may not be ellegant, but it works.

S. Kusen SSChampion Points: 10924 More actions · Answer 3

S. Kusen

SSChampion

Points: 10924

March 14, 2007 at 1:28 pm

#695057

Sounds good.

Frank Bazan SSCertifiable Points: 5348 More actions · Answer 4

Stephen is right, what you're describing there is a perfect candidate for the unpivot task.

Let me know if you want help configuring it.

Kindest Regards,

Frank Bazan

t perez SSC Rookie Points: 46 More actions · Answer 5

If you are using SQL Server 2005, you may also want to consider the new UNPIVOT command. This would move the processing from SSIS to the DB engine, which may be better/worse depending on your needs.

There is an example here:

http://qa.sqlservercentral.com/columnists/sramakrishnan/2734_printversion.asp