SQL Server Pivot

Example (thanks to http://goo.gl/oCM0q):

declare @data as table (Product varchar(10), Name varchar(20), Value varchar(10))

insert into @data values ('Beer', 'ProductCode',    'MSTF')
insert into @data values ('Beer', 'LocationScript', 'Office')
insert into @data values ('Milk', 'ProductCode',    'MSTF')
insert into @data values ('Milk', 'ProductSource',  'c.60')
insert into @data values ('Milk', 'LocationScript', 'Office')
insert into @data values ('Soda', 'ProductCode',    'APPL')
insert into @data values ('Soda', 'ProductSource',  'c.60')
insert into @data values ('Soda', 'LocationScript', 'Industry')
insert into @data values ('Wine', 'ProductSource',  'c.90')
insert into @data values ('Wine', 'Alias',  'Lorem')

select * from @data

SELECT Product, ProductCode, ProductSource, LocationScript, Alias
FROM (
 SELECT Product, Name, Value
 FROM @data
) dt
PIVOT (MAX(Value) FOR Name IN (ProductCode, ProductSource, LocationScript, Alias)) AS pv
ORDER BY Product

Result:

Product    Name                 Value
---------- -------------------- ----------
Beer       ProductCode          MSTF
Beer       LocationScript       Office
Milk       ProductCode          MSTF
Milk       ProductSource        c.60
Milk       LocationScript       Office
Soda       ProductCode          APPL
Soda       ProductSource        c.60
Soda       LocationScript       Industry
Wine       ProductSource        c.90
Wine       Alias                Lorem

(10 row(s) affected)

Product    ProductCode ProductSource LocationScript Alias
---------- ----------- ------------- -------------- ----------
Beer       MSTF        NULL          Office         NULL
Milk       MSTF        c.60          Office         NULL
Soda       APPL        c.60          Industry       NULL
Wine       NULL        c.90          NULL           Lorem

(4 row(s) affected)

Single vs SingleOrDefault

If no element is found Single throws an exception, but SingleOrDefault returns null.

Both methods throw an exception if there's more than one element in the sequence.

For example if there's more than one element SingleOrDefault() throws something like the following: System.InvalidOperationException: Sequence contains more than one element

NOTE: FirstOrDefault should be used when we have more than one element in the sequence and want only one element from it (in this case -as the name indicates- the first one) FirstOrDefault returns null when founds nothing (similar to SingleOrDefault).

public Transaction GetTransaction(Transaction transaction) {
    var transactions = GetTransactions(from: transaction.Date, to: transaction.Date);
    return transactions != null 
        ? transactions.Where(t => t.ID == transaction.ID).FirstOrDefault()
        : null;
}