asked on

Pivot / Unpivot Brain Teaser

I'm looking to group records based on their values.
Below is an example of my data nd the output I am looking to achieve.
Any assistance would be greatly appreciated.

ID  LineID  F1     F2        F3
1   1       Joe    Thomas    Smith
1   1       David  Harry     Jones
1   2       Joe    Thomas    Smith
1   2       David  Hary      Jones
1   3       Joe    Thomas    Smith
1   4       Joe    Thomas    Smith
1   4       David  Harry     Jones
1   4       Carlos Jay       Smith
2   1       Jane   Janet     Garcia


Results I am trying to get.

ID  LineID  F1     F2        F3      R
1   1       Joe    Thomas    Smith   Joe~Thomas~Smith|David~Harry~Jones
1   1       David  Harry     Jones   Joe~Thomas~Smith|David~Harry~Jones
1   2       Joe    Thomas    Smith   Joe~Thomas~Smith|David~Harry~Jones
1   2       David  Hary      Jones   Joe~Thomas~Smith|David~Harry~Jones
1   3       Joe    Thomas    Smith   Joe~Thomas~Smith
1   4       Joe    Thomas    Smith   Joe~Thomas~Smith|David~Harry~Jones
1   4       David  Harry     Jones   Joe~Thomas~Smith|David~Harry~Jones
1   4       Carlos Jay       Smith   Joe~Thomas~Smith|David~Harry~Jones|Carlos~Jay~Smith
2   1       Jane   Janet     Garcia  Jane~Janet_Garcia

Open in new window

ASKER CERTIFIED SOLUTION