I have a table with ID, type, amount and hour. The input dataset is in a table and output dataset should be loaded into a fixed width file.
I have a table with ID, type, amount and hour. The input dataset is in a table and output dataset should be loaded into a fixed width file.

Input dataset:

ID Name type1 hr1 amt1 type2 hr2 amt2 type3 hr3 amt3 type4 hr4  amt4
---------------------------------------------------------------------
1  Amy    R   1   100  NULL   0   0      NULL  0   0      NULL  0     0           
2  Ben    R   1   100  NULL   0   0      S     3   300    B     4   400 
3  Sam    NULL    0   0    V      2   200    S     3   300    NULL  0   0    
4  Ron    R   1   100  V      2   200    S     3   300    B     4   400  

Output dataset:

ID Name type1 hr1 amt1 type2 hr2 amt2
---------------------------------------
1  Amy    R 1     100  NULL   0     0
2  Ben    R 1     100  S      3     300
2  Ben    B     4     400  NULL   0     0
3  Sam    V 2     200  S      3    300
4  Ron    R 1     100  V      2    200
4  Ron    S 3     300  B      4    400

STRING_AGG is not recognized and tried with XML and STUFF but still missing something, cannot achieve the desired output as below in fixed width file.

##output to file:

1AmyR1100 00
2BenR1100S3300
2BenB4400 00
3SamV2200S3300
4RonR1100V2200
4RonS3300B4400

Suggest a best approach for this…!

Suggest a best approach for this…!