Simple (as possible) TSQL Pivot / Unpivot Samples

Pivoting in TSQL is difficult to understand.  It seems most samples are complex.  Every time I need to pivot I end up going back to one of these samples.  They start with the simplest possible example and build from there keeping it as simple as possible along the way.

------------  simplest pivot  --------------------
declare @bobo table (itemId int, quantity int)

                -- itemId qty
insert @bobo values (1, 1)
insert @bobo values (2, 2)
insert @bobo values (2, 2)
insert @bobo values (3, 3)

-- regular aggregate query (sum - only item 2 has more than one row to sum)
select itemID, sum(quantity) as 'total count' from @bobo group by itemID

-- pivoted view of same data.  Notice how the column headers become (really) a row to be read
-- left to right.  The items in the first column are really the column headers pivoted.
select [1],[2],[3] from
    -- source set
    (select itemID, quantity from @bobo) as alias_required
                            pivot
    -- pivoted set (aggregates: must use aggregate function)
    ( sum(quantity) for itemID in ([1],[2],[3]) ) as pivot_alias_required


------------------- nuther pivot ------------------------
declare @ExamAnswers Table
(
   StudentID varchar(12) NOT NULL,
   QuestionID int NOT NULL,
   Answer int
)

insert @ExamAnswers values('1', 1, 10) --Numbers in brackets below correspond to middle column here
insert @ExamAnswers values('1', 2, 2)  -- (thus these values are static - known)
insert @ExamAnswers values('1', 3, 3)
insert @ExamAnswers values('1', 4, 4)

-- normal view
select max(StudentID) as 'StudentId', max(QuestionID) as 'QuestionId', SUM(Answer) as 'Answer_sum'
from @ExamAnswers group by QuestionID

-- pivoted view
SELECT StudentID, [1] as Q1_sum, [2] as Q2_sum, [3] as Q3_sum, [4] as Q4_sum
FROM
(
    SELECT StudentID, QuestionID, Answer FROM @ExamAnswers
) AS piv
PIVOT
(
    -- aggregate function required (note: char columns can use min/max)
    sum(Answer) FOR QuestionID IN ([1], [2], [3], [4])
) AS chld
------------------------------------------------------------------------
--------------------------- same as above with double pivot --------------------
declare @ExamAnswers Table
(
   StudentID varchar(12) NOT NULL,
   QuestionID int NOT NULL,
   Answer int,
   SomethingElse int
)

insert @ExamAnswers values('1', 1, 10, 11) -- Numbers in brackets below correspond to middle column here
insert @ExamAnswers values('1', 2, 2, 2)   -- (thus these values are static - known)
insert @ExamAnswers values('1', 3, 3, 3)
insert @ExamAnswers values('1', 4, 4, 4)

-- normal view
select max(StudentID) as 'StudentId', max(QuestionID) as 'QuestionId', SUM(Answer) as 'Answer_sum', SUM(SomethingElse) as 'SomethingElse_sum'
from @ExamAnswers group by QuestionID;

with pivoted_view as
(
    SELECT
        StudentID, [1] as Q1_sum, [2] as Q2_sum, [3] as Q3_sum, [4] as Q4_sum,
        [S1] as S1_sum, [S2] as S2_sum, [S3] as S3_sum, [S4] as S4_sum
    FROM
    (
        SELECT
            StudentID,
            Answer as 'Answer',
            SomethingElse as 'SomethingElse',
            QuestionID as 'QuestionId',  -- used as number (hence column names) for first pivot
            'S' + convert(char(1), row_number() over (order by StudentId)) as 'SomethingElseNumber'  -- used as number for second pivot
        FROM @ExamAnswers
    ) main
    PIVOT
    (
        -- aggregate function required (note: char columns can use min/max)
        sum(Answer) FOR QuestionID IN ([1], [2], [3], [4])
    ) pivot_answer
    pivot
    (
        sum(SomethingElse) FOR SomethingElseNumber IN ([S1], [S2], [S3], [S4])
    ) pivot_something_else
)

-- Final result: studentID, Answer_sum pivoted, SomethingElse_sum pivoted
select StudentID,
                SUM(Q1_sum) A1_sum, SUM(Q2_sum) A2_sum, SUM(Q3_sum) A3_sum, SUM(Q4_sum) A4_sum,
             SUM(S1_sum) S1_sum, SUM(S2_sum) S2_sum, SUM(S3_sum) S3_sum, SUM(S4_sum) S4_sum
from pivoted_view
group by StudentID
-----------------------------------------------------------------------------
------------  unpivot  --------------------
declare @orig table ( fips bit, awd bit, inst bit )
insert @orig values (1,0,1)
select * from @orig
select IsEmpty, ColumnName from
    @orig
    unpivot -- cols must be same type
    (IsEmpty for ColumnName in (fips,awd,inst)) as alias_required;

-- Unpivoting multiple columns: http://mangalpardeshi.blogspot.com/2009/04/unpivot-multiple-columns.html    
-------------------- understanding the grouping that takes place ------------------------
declare @bobo2 table (id int, itemId int, quantity varchar(15))

                    --id, itemId qty
insert @bobo2 values (1, 1, 'a')

insert @bobo2 values (2, 1, 'c')
insert @bobo2 values (2, 2, 'd')

insert @bobo2 values (3, 1, 'e')
insert @bobo2 values (3, 2, 'f')
insert @bobo2 values (3, 2, 'g')

--select * from @bobo2

-- source set doesn't include id (one group out of all)
select [1],[2] from
    (select itemID, quantity from @bobo2) as alias_required
                            pivot
    ( min(quantity) for itemID in ([1],[2]) ) as pivot_alias_required

-- source set includes id (aggregate works on implicit group by the column that's not in the pivoted set)
select [1],[2] from
    (select id, itemID, quantity from @bobo2) as alias_required
                            pivot
    ( min(quantity) for itemID in ([1],[2]) ) as pivot_alias_required

--============================== Using row number to generate proper values to pivot on ==========================================
-- Parent / Child tables
declare @poppy table( id int primary key, name varchar(25) not null )
declare @child table( poppyId int not null, name varchar(25) not null )

insert @poppy values( 1, 'Hank' )
insert @child values( 1, 'HankChild1' )
insert @child values( 1, 'HankChild2' )

insert @poppy values( 2, 'Fred' )
insert @child values( 2, 'FredChild1' )
insert @child values( 2, 'FredChild2' )
insert @child values( 2, 'FredChild3' );
insert @child values( 2, 'FredChild4' );

with peep_data as
(
    select id, poppyId, p.name 'Poppy', c.name 'Child'
    from @poppy p
        inner join @child c on p.id = c.poppyId
),

-- We want to show parent with flattened children (up to a max of 4)
pivoted_data as
(
    select Poppy, Child1, Child2, Child3, Child4
    from
    (
        select Poppy, Child,
        -- Generate the values for each parent's (potential) children that are statically referenced in the pivot clause below
        'Child' + convert(char(1), ROW_NUMBER() over (partition by Id order by Id)) 'child_num'
        from peep_data
    ) src
            pivot
    ( max(Child) for child_num in (Child1, Child2, Child3, Child4) ) as useless_required_alias
)

select * from pivoted_data

Dynamic Linq Expressions; Conditionally Checking Nullable Type Example

While searching for an example of this, I came up short.  Most of the examples of building Linq expressions I ran across were very complicated and confusing.
This simple example builds an expression that compares 2 DateTime values, one of which is a nullable date.  In the process it demonstrates conditionally accessing different property values.

class Peep
{
    public string FirstName { get; set; }
    public DateTime? DOB { get; set; }
}

class Program
{

    static void Main( string[] args )
    {
        var peeps = new List<Peep>
        {
            new Peep { FirstName = "brad", DOB = DateTime.Now.AddDays( -1 ) },
            new Peep { FirstName = "fred", DOB = DateTime.Today },
            new Peep { FirstName = "Mr. null date" } };
        
        var constantComparisonExpression = Expression.Constant( DateTime.Today );

        var BirthDateParameter = Expression.Parameter( typeof( Peep ), "DOB" );
        var birthDateProperty = Expression.Property( BirthDateParameter, "DOB" );
        var hasValue = MemberExpression.Property( birthDateProperty, "HasValue" );
        var actualValue = MemberExpression.Property( birthDateProperty, "Value" );
        var bogusValue = Expression.Constant( DateTime.MinValue );
        var condition = Expression.Condition( hasValue, actualValue, bogusValue );

        var isEqual = Expression.Equal( condition, constantComparisonExpression );
        var finalExpr = Expression.Lambda<Func<Peep, bool>>( isEqual, BirthDateParameter );

        // Equivalent of .Where( peep => (peep.DOB.HasValue ? peep.DOB.Value : DateTime.MinValue) == DateTime.Today )
        var wrist = peeps.AsQueryable().Where( finalExpr ).ToList();
        if ( wrist.Count == 1 )
        {
            /// Should find fred
            Console.WriteLine( wrist[0].FirstName );
        }
        else
        {
            Console.WriteLine( "no match" );
        }
        Console.ReadLine();
    }

}

If you need to combine multiple expressions, see this.  Don't bother searching further, that works.

Getting the database name from an Entity Framework Context

This seems like it should be so easy, but it took awhile to figure out.  Both methods below take an ObjectContext.  You can get that from a DbContext using ((IObjectContextAdapter)myDbContext).ObjectContext.

public static string ADOConnectionString( ObjectContext context )
{
    return ( (EntityConnection)context.Connection ).StoreConnection.ConnectionString;
}

/// This works regardless of how the connection string names the database ("initial catalog", "database", etc.).
public static string DatabaseName( ObjectContext context )
{
    return new SqlConnectionStringBuilder( ADOConnectionString( context ) ).InitialCatalog;
}

Querying an Entity Framework Model for Column Type Information

In the process of writing an import system for importing data into a database modeled by EF, I needed to be able to grab table/column information from the model.  Following is a method I wrote for doing that (it's more difficult than it seems).

First, here is the class I'm populating in the main method.  Of course, you could add properties to this as necessary:

Update:  There is a bug in the code below.  It's impossible to edit the (pasted in) HTML now.  the select after "var foreignKeyNames" below should be:
select ( p.FromEndMember.RelationshipMultiplicity == RelationshipMultiplicity.One ) ? 
( (AssociationType)p.RelationshipType ).ReferentialConstraints[0].FromProperties[0].Name :
( (AssociationType)p.RelationshipType ).ReferentialConstraints[0].ToProperties[0].Name );

public class EntityTypeInfo
{
    public string EntityName { get; set; }
    public string PropertyName { get; set; }
    public Type EntityType { get; set; }

    ///
    /// Identity, Computed columns
    ///
    public bool StoreGenerated { get; set; }
    public bool IsForeignKey { get; set; }
}

Now the main method along with a helper method (see update above):

44. /// <summary>
45. /// For the given table/entity list, build a list of type info.
46. /// </summary>
47. public static List<EntityTypeInfo> SimplePropertiesFrom( ObjectContext context, List<string> tables )
48. {
49.     var result = new List<EntityTypeInfo>();
50.  
51.     /// We have to draw from two different spaces to get all the info we need.
52.     var conceptualMetadata = context.MetadataWorkspace.GetItems( DataSpace.CSpace );
53.     /// context.MetadataWorkspace.GetItems() for storage space only works if queries have already run which we can't ensure here.
54.     var storageMetadata = ( (EntityConnection)context.Connection ).GetMetadataWorkspace().GetItems( DataSpace.SSpace );
55.  
56.     var query = from c in conceptualMetadata
57.                 join s in storageMetadata on
58.                     c.BuiltInTypeKind == BuiltInTypeKind.EntityType ? ( (EntityType)c ).Name : "X" // If the item isn't an EntityType, we don't want a join, so just make these 2 values different
59.                     equals
60.                     s.BuiltInTypeKind == BuiltInTypeKind.EntityType ? ( (EntityType)s ).Name : ""
61.                 where c.BuiltInTypeKind == BuiltInTypeKind.EntityType
62.                 select new { conceptual = c as EntityType, storage = s as EntityType };
63.     query.ToList().ForEach( field =>
64.     {
65.         if ( tables.Contains( field.conceptual.Name ) )
66.         {
67.             var foreignKeyProps = ( from p in field.conceptual.NavigationProperties
68.                                     where p.RelationshipType is AssociationType && ((AssociationType)p.RelationshipType).IsForeignKey
69.                                     select ( ( AssociationType )p.RelationshipType ).ReferentialConstraints[0].FromProperties[0].Name );
70.             foreach ( var p in field.conceptual.Properties )
71.             {
72.                 result.Add( new EntityTypeInfo
73.                 {
74.                     EntityName = field.conceptual.Name,
75.                     PropertyName = p.Name,
76.                     EntityType = ( (PrimitiveType)p.TypeUsage.EdmType ).ClrEquivalentType,
77.                     StoreGenerated = IsStoreGenerated( p, field.storage ),
78.                     IsForeignKey = foreignKeyProps.Contains( p.Name )
79.                 } );
80.             }
81.         }
82.     } );
83.     return result;
84. }
85.  
86. private static bool IsStoreGenerated( EdmProperty conceptualEntityProperty, EntityType storageEntityType )
87. {
88.     EdmMember storageProperty;
89.     storageEntityType.Members.TryGetValue( conceptualEntityProperty.Name, true, out storageProperty );
90.     if ( storageProperty == null )
91.     {
92.         return false;
93.     }
94.     else
95.     {
96.         Facet f;
97.         if ( storageProperty.TypeUsage.Facets.TryGetValue( "StoreGeneratedPattern", false, out f) )
98.         {
99.             return ( ( (StoreGeneratedPattern)f.Value ) == StoreGeneratedPattern.Identity ) || ( ( (StoreGeneratedPattern)f.Value ) == StoreGeneratedPattern.Computed );
100.         }
101.         else
102.         {
103.             /// If it's not store generated, the above property won't be there (StoreGeneratedPattern.None is never referenced).
104.             return false;
105.         }
106.     }
107. }