Transform/Transpose Data single line of information with Sort

[LincolnMike]

Hello,

I have the following data, and I want the output to be on a separate line.  I am almost there, but my issue is sorting the output.

I just want a single line for the VIN with the multiple paint descriptions displayed, but I want the Body color to be the first displayed (PNTCd begins with a P).  I am stuck on getting the first column listed the Body paint.  Any ideas?  I attached my working YXMD.

VIN	FeatX	PntCd	MY
123	white hood	CJA	2013
123	Blue Accent Body	CJB	2013
123	Red body	PNA	2013
789	orange Hood	CJD	2014
789	White Accent Hood	CJE	2014
789	Less Accent Trunk	CJK	2014
789	orange body	PNM	2014
001	purple Hood	CJF	2015
001	pink body	PNX	2015

CURRENT OUTPUT:

VIN	MY1	PaintX_cd1	PaintX_cd2	PaintX_cd3	PaintX_cd4
001	2015	purple Hood_C	pink body_P
123	2013	white hood_C	Blue Accent Body_C	Red body_P
789	2014	orange Hood_C	White Accent Hood_C	Less Accent Trunk_C	orange body_P

[Claje · Accepted solution]

Hi,

I've attached an example that accomplishes this.  Basically all I added to your flow was a sort order on the first character of PntCd.  I used a "SWITCH" statement, defaulting to 2, to set the value to 1 for any PntCd that starts with a P.  I added this sort as a key field to the Transpose, and included it in the sort.

The advantage of this solution is that you could swap the default to something like 999, then you could do multiple hierarchies if you had P, C, and E codes, as an example.

[LincolnMike]

thank you!  I'll apply that now.