Transposing Data Out within SQL Table not using Pivot option

Question

I'm trying to Transpose Data in a SQL Table, but this is a bit of custom transposing technique and possible concatenation of values into one table cell.

Below is a sample data set. From [master].[dbo].[OLE DB Destination 3]

_ParentKey Field |name                          |type
1                |CHEVRON MIDCONTINENT LP       |Grantor
1                |CHEVRON USA INC               |Grantor
1                |UNION OIL CO                  |Grantor
1                |XBM PRODUCTION LP             |Grantor
1                |CASILLAS PETROLEUM            |Grantee
2                |OSAGE OIL AND GAS PROPERTIES  |Grantor
2                |CASILLAS PETROLEUM            |Grantee

Below is the desired out put of the query

_ParentKey Field |Grantor                                                               |Grantee
1                |CHEVRON MIDCONTINENT LP, CHEVRON USA INC, UNION OIL, XBM PRODUCTION LP|CASILLAS PETROLEUM
2                |OSAGE OIL AND GAS PROPERTIES                                          |CASILLAS PETROLEUM

I'm assuming the _ParentKey Field field would be the "primary key" to transpose this data out. Every Name in the "name" column will either have a Grantor or Grantee Type. Disclaimer: I only know basic SQL up to Joining, sub-queries and some data manipulation.

Answer 1

I would recommend conditional aggregation and string aggregation function string_agg():

select 
    _ParentKey,
    string_agg(case when type = 'Grantor' then name end, ', ') grantor,
    string_agg(case when type = 'Grantee' then name end, ', ') grantee
from mytable
group by _ParentKey