How to convert rows to columns in LINQ

Question

I have first output in the image and I want to convert it to second output in image using LINQ Core. Is there and direct unpivot option in LINQ.

I am able to write the code for PIVOT and UNPIVOT in SQL. But not able to find a way to do the same in LINQ.

I have below SQL query for the same :

SELECT ResourceName,
            max(ENText)as ENText,
            max(FRText)as FRText,
            max(ZHText)as ZHText,
            max(DEText)as DEText,
            max(ITText)as ITText,
            max(JAText)as JAText,
            max(PTText)as PTText,
            max([PT-BRText]) as [PT-BRText],
            max(RUText) as RUText,
            max(ESText) as ESText,
            max(SVText) as SVText  into #temp 
            FROM   
GenericLanguageTranslation 
PIVOT  
(  
max(Translation) FOR LanguageID IN (
            ENText,
            ZHText,
            FRText,
            DEText,
            ITText,
            JAText,
            PTText,
            [PT-BRText],
            RUText,
            ESText,
            SVText)
) AS Tab2  
group by ResourceName
order by 1

select * from #temp
SELECT NEWID() as Id,ResourceName, [LanguageID],[Translation]-- into #GenericLanguageTranslation
FROM #temp
UNPIVOT
(
       [Translation]
       FOR [LanguageID] IN 
       (
            ENText,
            ZHText,
            FRText,
            DEText,
            ITText,
            JAText,
            PTText,
            [PT-BRText],
            RUText,
            ESText,
            SVText

       )
) AS UnpivotTranslation

see link below : https://stackoverflow.com/questions/10188774/is-unpivot-not-pivot-functionality-available-in-linq-to-sql-how — El andaloussi Ishrak, May 09 '19 at 09:32
@ElandaloussiIshrak I have referred the link and implemented the same and its working but I am searching for more generic solution or atleast a solution with less redundant code. — Shardul, May 09 '19 at 12:10
See my [new answer](https://stackoverflow.com/a/56065350/2557128) to that question. — NetMage, May 09 '19 at 18:17

score 0 · Answer 1 · answered May 09 '19 at 20:10

If you are willing to use reflection, and willing to do the unpivot on the client side, here is how to do it.

The return class includes the pivot data and unpivot fields:

public class UnpivotData {
    public Guid Id { get; set; }
    public string ResourceName { get; set; }
    public string LanguageID { get; set; }
    public string Translation { get; set; }

    public UnpivotData() => Id = Guid.NewGuid();

    public UnpivotData ShallowCopy() {
        var ans = (UnpivotData)this.MemberwiseClone();
        ans.Id = Guid.NewGuid();
        return ans;
    }
}

Then an extension method for doing the unpivot:

public static class IEnumerableExt {
    public static IEnumerable<UnpivotData> Unpivot<T>(this IEnumerable<T> src, string[] pivotFieldNames, string unPivotName, string unPivotValue, Func<string, bool> unpivotFieldNameFn) {
        var srcPIs = typeof(T).GetProperties();
        var srcPivotPIs = srcPIs.Where(pi => pivotFieldNames.Contains(pi.Name));
        var ansPIs = typeof(UnpivotData).GetProperties();
        var ansPivotPIs = ansPIs.Where(pi => pivotFieldNames.Contains(pi.Name));
        var srcAnsPivotPIs = srcPivotPIs.Zip(ansPivotPIs, (spi, api) => new { spi, api }).ToList();
        var srcUnpivotPIs = srcPIs.Where(pi => unpivotFieldNameFn(pi.Name)).ToList();
        var unPivotNamePI = ansPIs.First(pi => pi.Name == unPivotName);
        var unPivotValuePI = ansPIs.First(pi => pi.Name == unPivotValue);

        foreach (var d in src) {
            var ansbase = new UnpivotData();
            foreach (var sapi in srcAnsPivotPIs)
                sapi.api.SetValue(ansbase, sapi.spi.GetValue(d));

            foreach (var spi in srcUnpivotPIs) {
                var ans = ansbase.ShallowCopy();
                unPivotNamePI.SetValue(ans, spi.Name);
                unPivotValuePI.SetValue(ans, spi.GetValue(d));

                yield return ans;
            }
        }
    }
}

And now you can unpivot your data:

var result = genericLanguageTranslation.AsEnumerable().Unpivot(new[] { "ResourceName" }, "LanguageID", "Translation", fn => fn.EndsWith("Text"));

How to convert rows to columns in LINQ

1 Answers1