Transforming Data

[cowannbell]

trI need to transform data from rows to columns.

So the group field is called PIN but then I have Lic #, Lic Type, Lic Date.

I need to include all lic fields on the same row as the pin.  How can I accomplish this?

So like this:

Pin	Lic #	Lic type	Lic Date	Lic #	Lic type	Lic Date
100	1	Small	1/1/2023	4	Medium	4/1/2023
200	2	Small	2/1/2023	5	Medium	5/1/2023
300	3	Small	3/1/2023	6	Medium	6/1/2023

[jrgo]

I'm thinking the Crosstab tool, but column headers are required to be unique so they won't look like the way you pictured in your table.

If you can also share what your source/start data looks like, we may be able to provide a more detailed solution.

[ed_hayter]

We can use a combo of transpose and crosstab to do this once we make an identifier for new rows.

[cowannbell]

I want all lice on the same row as the pin.  Your example has it on different rows. 

[ed_hayter]

Apologies - I see now that is your desired output. Can you quickly demonstrate what it would look like coming in

[cowannbell]

Sure, actually exactly what you have in our output, without the record id. So I need the reverse.

[ed_hayter]

You can group by the pin and concatenate the other fields. This does not get exactly to your desired output of having the information in separate fields. To get that I used some brute force with text-to-columns splitting on the ",".

This works fine when there are only ever two of each but I'd need to spend more time thinking up a dynamic solution to split based on the number of records per pin. Suppose you could increase the split to a larger number than the maximum and then remove null columns. Potential problem is that you are making the data very wide and not as performant.

I'll leave the brute force one here and then give a smarter solution some thought.

The second workflow is one that expands the table and removes null columns

[cowannbell]

I think I might have confused you.

Maybe this will help.

How data is
Pin	Lic #	Lic Type	Lic Date
100	1	Small	1/1/2023
100	2	Medium	1/1/2023
100	3	Large	3/1/2023
200	5	Small	4/1/2023
200	7	Medium	5/1/2023
200	8	Large	6/1/2023
300	10	Small	2/1/2023
300	14	Medium	7/1/2023
300	23	Large	8/1/2023
How I want it
Pin	Lic #	Lic Type	Lic Date	Lic #	Lic Type	Lic Date	Lic #	Lic Type	Lic Date
100	1	Small	1/1/2023	2	Medium	1/1/2023	3	Large	3/1/2023
200	5	Small	4/1/2023	7	Medium	5/1/2023	8	Large	6/1/2023
300	10	Small	2/1/2023	14	Medium	7/1/2023	23	Large	8/1/2023

[cowannbell]

I think I might have confused you.

Hopefully the attached will help.

[ed_hayter]

The above Workflow would achieve that output - although you cannot have column headers with identical names - hence the suffix (LIC_1, LIC_2 ETC.). But the logic of bringing all that information onto the same row for each pin and then splitting on the list delimiter is likely the best way to achieve this.

The second part of my comment was more rambling about thinking of more dynamic ways of getting to the desired output (when pins have varying amount of LICs in them.)