Pivot against a SQL stored procedure (or LINQ)

Question

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

Answer 1

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.

Answer 2

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))
  ));