Transpose rows and columns with no aggregate

If you are going to apply the PIVOT function, you will need to use an aggregate function to get the result but you will also want to use a windowing function like row_number() to generate a unique sequence for each contact in the account.

First, you will query your data similar to:

select account, contact,
  'contact'
    + cast(row_number() over(partition by account
                              order by contact) as varchar(10)) seq
from yourtable

See SQL Fiddle with Demo. This will create a new column with the unique sequence:

| ACCOUNT |   CONTACT |      SEQ |
|---------|-----------|----------|
|       1 | 324324324 | contact1 |
|       1 | 674323234 | contact2 |

If you have a limited number of columns, then you could hard-code your query:

select account,
  contact1, contact2, contact3, contact4
from 
(
  select account, contact,
    'contact'
      + cast(row_number() over(partition by account
                                order by contact) as varchar(10)) seq
  from yourtable
) d
pivot
(
  max(contact)
  for seq in (contact1, contact2, contact3, contact4)
) piv;

See SQL Fiddle with Demo

If you have an unknown number of columns, then you will have to use dynamic SQL:

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

select @cols = STUFF((SELECT ',' + QUOTENAME(seq) 
                    from
                    (
                      select 'contact'
                              + cast(row_number() over(partition by account
                                                        order by contact) 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 account, ' + @cols + ' 
            from 
            (
                select account, contact,
                  ''contact''
                    + cast(row_number() over(partition by account
                                              order by contact) as varchar(10)) seq
                from yourtable
            ) x
            pivot 
            (
                max(contact)
                for seq in (' + @cols + ')
            ) p '

execute sp_executesql @query;

See SQL Fiddle with Demo. Both will give you a result of:

| ACCOUNT |  CONTACT1 |  CONTACT2 |  CONTACT3 |  CONTACT4 |
|---------|-----------|-----------|-----------|-----------|
|       1 | 324324324 | 674323234 |    (null) |    (null) |
|       2 | 433243443 | 833343432 |    (null) |    (null) |
|       3 | 787655455 |    (null) |    (null) |    (null) |
|       4 | 455435435 | 754327545 |    (null) |    (null) |
|       5 | 432432432 | 432433242 | 432455553 | 543544355 |

Just a slightly different way to generate the dynamic PIVOT:

DECLARE @c INT;

SELECT TOP 1 @c = COUNT(*) 
  FROM dbo.YourTable
  GROUP BY Account 
  ORDER BY COUNT(*) DESC;

DECLARE @dc1 NVARCHAR(MAX) = N'', @dc2 NVARCHAR(MAX) = N'', @sql NVARCHAR(MAX);

SELECT @dc1 += ',Contact' + RTRIM(i), @dc2 += ',[Contact' + RTRIM(i) + ']'
  FROM (SELECT TOP (@c) i = number + 1 
  FROM master.dbo.spt_values WHERE type = N'P' ORDER BY number) AS x;

SET @sql = N'SELECT Account ' + @dc1 + 
  ' FROM (SELECT Account, Contact, rn = ''Contact'' 
      + RTRIM(ROW_NUMBER() OVER (PARTITION BY Account ORDER BY Contact))
    FROM dbo.YourTable) AS src PIVOT (MAX(Contact) FOR rn IN (' 
      + STUFF(@dc2, 1, 1, '') + ')) AS p;';

EXEC sp_executesql @sql;

SQLiddle demo