Sql server use pivot function to restruct data

Question

I have a table of data in SQL Server 2012, shown below. What I would like to do is query the data but restructure the layout. There is one column called 'factor' that contain 7-8 different labels which have a corresponding value (value column). What I would like is to have the factors as columns going across - please see the desired output section to see what I mean. What is the best way to write a query to view my data as such?

Desired Output

pdate     abc     def   ghi
1-1-13    1       3     2
2-1-13    1       3     2

Table example

factor     pdate     value
abc        1-1-13    1
def        1-1-13    3
ghi        1-1-13    2
abc        2-1-13    1
def        2-1-13    3
ghi        2-1-13    2
abc        3-1-13    1
def        3-1-13    3
ghi        3-1-13    2
abc        4-1-13    1
def        4-1-13    3
ghi        4-1-13    2    

Answer 1

Dynamic version if you don't want to hard-code the [factor] values...

If      Object_ID('tempdb..#factorDateValues') Is Not Null Drop Table #factorDateValues
Create  Table #factorDateValues (factor Varchar(50), pdate Date, value Int)

Insert  #factorDateValues (factor, pdate, value)
Values  ('abc','2013-01-01', 1),
        ('def','2013-01-01', 3),
        ('ghi','2013-01-01', 2),
        ('abc','2013-02-01', 1),
        ('def','2013-02-01', 3),
        ('ghi','2013-02-01', 2),
        ('abc','2013-03-01', 1),
        ('def','2013-03-01', 3),
        ('ghi','2013-03-01', 2),
        ('abc','2013-04-01', 1),
        ('def','2013-04-01', 3),
        ('ghi','2013-04-01', 2)

Declare @sql Nvarchar(Max)

Select  @sql = Coalesce(@sql + ',','') + '[' + factor + ']'
From    #factorDateValues
Group   By factor
Order   By factor

Set     @sql = 'Select  pDate,' + @sql +
               'From    #factorDateValues
                Pivot  (Max([value]) For factor In (' + @sql + ')) As val'

Exec    sp_executeSQL @sql

Answer 2

as outisnihil mentioned a better approach would be to use PIVOT

Using PiVOT

  SELECT pdate
        , [abc]
        , [def]
        , [ghi]
    FROM YOUR_table
    PIVOT(SUM([value]) FOR factor IN (
                [abc]
                , [def]
                , [ghi]
                )) AS val

---

Using CASE... You can use a case for every factor value to generate a new column in your output.

SELECT pdate
, CASE 
WHEN factor = 'abc' SUM(value) END abc
, CASE 
WHEN factor = 'def' SUM(value) END def
, CASE 
WHEN factor = 'ghi' SUM(value) END ghi 
FROM YOUR_table GROUP BY pdate