SQL converting rows data in to column using single table

I am trying to convert one of DB table row in to column and using PIVOT function with cursors, here is the Sql:

DECLARE Cur CURSOR FOR
  SELECT DISTINCT CounterId
  FROM AllCounterIds

DECLARE @Temp NVARCHAR(MAX),
  @AllCounterIds NVARCHAR(MAX),
  @CounterIdQuery NVARCHAR(MAX)

SET @AllCounterIds = ''

OPEN Cur
-- Getting all the movies
FETCH NEXT FROM Cur INTO @Temp
WHILE @@FETCH_STATUS = 0
BEGIN
 SET @AllCounterIds = @AllCounterIds + '[' + @Temp + '],'
 FETCH NEXT FROM Cur INTO @Temp
END

CLOSE Cur
DEALLOCATE Cur

SET @AllCounterIds = SUBSTRING(@AllCounterIds, 0, LEN(@AllCounterIds))

SET @CounterIdQuery = 'SELECT DateTime, ' + @AllCounterIds + '
FROM 
(SELECT Type, DateTime, Value, AllCounterIds.CounterId
FROM AllCounterIds
 INNER JOIN AllCounters
  ON AllCounterIds.CounterId = AllCounters.CounterId) S
PIVOT
(
SUM (Value)
FOR CounterId IN
(' + @AllCounterIds + ')) AS pvt'

EXEC sp_executesql @CounterIdQuery

where AllCounterIds is a View that I created using this:

GO
CREATE VIEW AllCounterIds AS 
SELECT DISTINCT CounterId FROM AllCounters
GO

So problem is I have around 27993600 rows in table now by far, and when I execute the SQL it takes arount 4min and some 15 sec to give me the output and as per performance requirement its bad ... so my question is that, is there anyway where I can achieve my desired result but getting better performance?

And just to let you know cluster indexes are also defined on the table ...

I'm not sure it's actually faster for your case, but perhaps try something like this

declare
    @allcounterids varchar(max),
    @counteridquery varchar(max)
;

select
    @allcounterids = stuff((
    select 
            '],[' + cast([CounterId] as varchar(32))
        from
            allcounterids
        for xml path('')), 1, 2, '') + ']'
;

set @counteridquery = 'select
        s.datetime, pvt.*
    from
        (SELECT Type, DateTime, Value, AllCounterIds.CounterId
            FROM AllCounterIds
            INNER JOIN AllCounters
                ON AllCounterIds.CounterId = AllCounters.CounterId
        ) S
        PIVOT
        (
            SUM (Value)
            FOR CounterId IN
            (' + @AllCounterIds + ')
        ) AS pvt;'
;
execute(@counteridquery);

The use of varchar instead of nvarchar is deliberate. It's not likely that you need unicode for a list of integers and it will save some memory, thus possibly time.