IF object_id('tempdb..#testEnvironment') IS NOT NULL

BEGIN

DROP TABLE #testEnvironment;

END;

SELECT CAST(dtDate AS DATETIME) AS dtDate, sDocIdPrefix, CNT

INTO #testEnvironment

FROM (SELECT '2013-02-11', 653, 3683 UNION ALL

SELECT '2013-02-11', 649, 1334 UNION ALL

SELECT '2013-02-12', 647, 1422 UNION ALL

SELECT '2013-02-12', 649, 6602 UNION ALL

SELECT '2013-02-12', 653, 4781

)a(dtDate,sDocIdPrefix,CNT);

Sample data is: -

dtDate sDocIdPrefix CNT

----------------------- ------------ -----------

2013-02-11 00:00:00.000 653 3683

2013-02-11 00:00:00.000 649 1334

2013-02-12 00:00:00.000 647 1422

2013-02-12 00:00:00.000 649 6602

2013-02-12 00:00:00.000 653 4781

Using the sample data above, then I think that this is what you're after: -

DECLARE @SQL NVARCHAR(MAX);

SELECT @SQL = 'SELECT dtDate,' + sqlCode + CHAR(13) + CHAR(10) + 'FROM #testEnvironment'+CHAR(13)+CHAR(10)+'GROUP BY dtDate;'

FROM (SELECT STUFF((SELECT ',' + CHAR(13)+CHAR(10)+

'MAX(CASE WHEN sDocIdPrefix = ' + CAST(sDocIdPrefix AS VARCHAR(20)) + ' THEN CNT ELSE 0 END) AS '+

QUOTENAME(CAST(sDocIdPrefix AS VARCHAR(20)))

FROM (SELECT DISTINCT sDocIdPrefix

FROM #testEnvironment

)a

ORDER BY sDocIdPrefix DESC

FOR XML PATH(''),TYPE

).value('.','VARCHAR(MAX)'),1,1,'')

)dynSql(sqlCode)

EXECUTE sp_executesql @SQL;

Produces: -

dtDate 653 649 647

----------------------- ----------- ----------- -----------

2013-02-11 00:00:00.000 3683 1334 0

2013-02-12 00:00:00.000 4781 6602 1422

Look at how I laid out the sample data, it makes it considerably easier for people to help when it looks like that