Pivot against a SQL stored procedure (or LINQ)

I am trying to create a Stored procedure (or query expression) that Pivots on a grouping ID. After looking at the examples here and elsewhere I have failed to get my pivot statements to work in a stored procedure, and I am looking my help.

Also, if this could be done with LINQ on a LIST that would be a solution for me also.

theID     theGroup   theValue
1          2          Red
2          2          Blue
3          2          Green
1          3          10
2          3          24
3          3          30
1          4          1
2          4          2
3          4          3

the Group #2 means a CHOICE, the group # 3 means COUNT, the Group #4 means SORT so I want to name those columns (I realize this is a shortcoming of PIVOT but that's OK).

ID        CHOICE     COUNT      SORT
1         Red        10  1
2         Blue       24  2
3         Green      30  3

From stackoverflow

• This worked for me and should work in an SP:

  SELECT  theID AS ID
         ,[2] AS CHOICE
         ,[3] AS COUNT
         ,[4] AS SORT
  FROM    so_666934 PIVOT ( MAX(theValue) FOR theGroup IN ([2], [3], [4]) ) AS pvt
  

  There are tricks you can do with dynamic SQL to handle varying groups over time and you can also pivot on the names by effectively replacing theGroup with the name before the PIVOT.

  Ash Machine : Thank you, that worked beautifully!

• Here's a couple of ways to do this in-memory with LINQ.

  List<SomeClass> source = new List<SomeClass>()
  {
    new SomeClass(){ theID = 1, theGroup = 2, theValue="Red"},
    new SomeClass(){ theID = 2, theGroup = 2, theValue="Blue"},
    new SomeClass(){ theID = 3, theGroup = 2, theValue="Green"},
    new SomeClass(){ theID = 1, theGroup = 3, theValue=10},
    new SomeClass(){ theID = 2, theGroup = 3, theValue=24},
    new SomeClass(){ theID = 3, theGroup = 3, theValue=30},
    new SomeClass(){ theID = 1, theGroup = 4, theValue=1},
    new SomeClass(){ theID = 2, theGroup = 4, theValue=2},
    new SomeClass(){ theID = 3, theGroup = 4, theValue=3}
  };
  
  //hierarchical structure
  var result1 = source.GroupBy(item => item.theID)
    .Select(g => new {
      theID = g.Key,
      theValues = g
        .OrderBy(item => item.theGroup)
        .Select(item => item.theValue)
        .ToList()
    }).ToList();
  
  
  //holds some names for the next step.
  Dictionary<int, string> attributeNames = new Dictionary<int,string>();
  attributeNames.Add(2, "CHOICE");
  attributeNames.Add(3, "COUNT");
  attributeNames.Add(4, "SORT");
  //xml structure
  var result2 = source
    .GroupBy(item => item.theID)
    .Select(g => new XElement("Row",
      new XAttribute("ID", g.Key),
      g.Select(item => new XAttribute(attributeNames[item.theGroup], item.theValue))
    ));