Pivot columns in SQL Server

There are several ways that you can get the result that you want, but in order to successfully return multiple carid values for each name and period, I would use a windowing function like row_number() to generate a unique sequence for each partition of name/period.

Your query will start with using something like:

select name, carid, period,
  'col'+
    cast(row_number() over(partition by name, period
                            order by carid) as varchar(10)) seq
from yourtable;

See SQL Fiddle with Demo. This is going to give you the following data that you can then PIVOT into columns.

|  NAME | CARID | PERIOD |  SEQ |
|-------|-------|--------|------|
|   Bob |   121 | Feb 08 | col1 |
|   Bob |   121 | Jan 08 | col1 |
|   Bob |   123 | Jan 08 | col2 |
|  Ruth |   139 | Feb 08 | col1 |
| Steve |   121 | Jan 08 | col1 |

You can then convert this data into columns, using an aggregate function and a CASE expression similar to:

select 
  name, 
  period,
  max(case when seq = 'col1' then carid end) col1,
  max(case when seq = 'col2' then carid end) col2,
  max(case when seq = 'col3' then carid end) col3
from
(
  select name, carid, period,
    'col'+
      cast(row_number() over(partition by name, period
                              order by carid) as varchar(10)) seq
  from yourtable
) d
group by name, period;

See SQL Fiddle with Demo. This can also be converted into columns using the PIVOT function:

select name, period, col1, col2, col3
from
(
  select name, carid, period,
    'col'+
      cast(row_number() over(partition by name, period
                              order by carid) as varchar(10)) seq
  from yourtable
) d
pivot
(
  max(carid)
  for seq in (col1, col2, col3)
) p;

See SQL Fiddle with Demo. The above two queries will work great if you have a limited number of values, but if you have unknown values, then you will have to use dynamic SQL to generate the result:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(seq) 
                    from
                    (
                      select 'col'+
                          cast(row_number() over(partition by name, period
                                                  order by carid) as varchar(10)) seq
                      from yourtable
                    ) d
                    group by seq
                    order by seq
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT name, period,' + @cols + ' 
            from 
            (
              select name, carid, period,
                ''col''+
                  cast(row_number() over(partition by name, period
                                          order by carid) as varchar(10)) seq
              from yourtable
            ) x
            pivot 
            (
                max(carid)
                for seq in (' + @cols + ')
            ) p '

execute sp_executesql @query;

See SQL Fiddle with Demo. All versions will give you a result similar to:

|  NAME | PERIOD | COL1 |   COL2 |
|-------|--------|------|--------|
|   Bob | Feb 08 |  121 | (null) |
|  Ruth | Feb 08 |  139 | (null) |
|   Bob | Jan 08 |  121 |    123 |
| Steve | Jan 08 |  121 | (null) |