-- Create Tables and Insert Values

Create Table tmpDis (ID int Identity(1,1), Disposition VARCHAR(50),CDATE Datetime )

insert into tmpDis (Disposition,CDATE)

Select 'Busy','7/1/2010'

union all Select 'Ringing','7/1/2010'

union all Select 'Callback','7/1/2010'

union all Select 'Callback','7/1/2010'

union all Select 'Busy', '7/2/2010'

union all Select 'Callback','7/3/2010'

union all Select 'Callback','7/3/2010'

union all Select 'Callback','7/3/2010'

union all Select 'Busy', '7/4/2010'

union all Select 'Busy', '7/4/2010'

union all Select 'Busy', '7/4/2010'

union all Select 'Busy', '7/4/2010'

union all Select 'Busy', '7/4/2010'

union all Select 'Ringing','7/4/2010'

union all Select 'Ringing','7/4/2010'

union all Select 'Ringing','7/4/2010'

union all Select 'Ringing','7/4/2010'

union all Select 'Ringing','7/4/2010'

-- Create a procedure

Create PROCEDURE exec uspTmpDynamicTableTry

as

Begin

CREATE TABLE MasterTbl -- for columns and rows

(

RowText VARCHAR(50),

ColumnText VARCHAR(20)

)

INSERT INTO MasterTbl

(

RowText,ColumnText

)

SELECT Distinct tmpDis.Disposition ,convert(varchar, tmpDis.CDATE, 112)

FROM tmpDis

CREATE UNIQUE INDEX IX_MasterRef ON MasterTbl (RowText,ColumnText)

CREATE TABLE ColumnsTbl --For column Header

(

ColumnIndex INT IDENTITY (0, 1),

ColumnText VARCHAR(20)

)

INSERT INTO ColumnsTbl

(

ColumnText

)

SELECT DISTINCT convert(varchar, tmpDis.CDATE, 112)

FROM tmpDis

ORDER BY convert(varchar, tmpDis.CDATE, 112)

CREATE UNIQUE INDEX IX_Columns ON ColumnsTbl (ColumnIndex, ColumnText)

CREATE TABLE RowsTbl -- Output Table

(

Disposition VARCHAR(50)

)

INSERT INTO RowsTbl

(

Disposition

)

SELECT Distinct tmpDis.Disposition

FROM tmpDis

CREATE UNIQUE INDEX IX_Rows ON RowsTbl (Disposition)

DECLARE @ColumnIndex INT,

@MaxColumnIndex INT,

@ColumnText VARCHAR(50),

@SQLstr VARCHAR(1000)

SELECT @ColumnIndex = 0, @MaxColumnIndex = MAX(ColumnIndex) FROM ColumnsTbl

WHILE @ColumnIndex <= @MaxColumnIndex

BEGIN

SELECT @ColumnText = ColumnText

FROM ColumnsTbl

WHERE ColumnIndex = @ColumnIndex

SELECT @SQLstr = 'ALTER TABLE RowsTbl ADD ' + QUOTENAME(@ColumnText) + ' VARCHAR(100) NULL DEFAULT ''''' -- Add Rows from ColumnsTbl

EXEC (@SQLstr)

SELECT @SQLstr = 'UPDATE RowsTbl SET ' + QUOTENAME(@ColumnText) + ' = (Select Count(*) from tmpDis A Where convert(Decimal(8),convert(varchar, A.CDATE, 112)) = convert(Decimal(8),ColumnsTbl.ColumnText) and A.Disposition = MasterTbl.RowText Group by A.Disposition ) FROM MasterTbl , ColumnsTbl WHERE ColumnsTbl.ColumnText = MasterTbl.ColumnText and Disposition = MasterTbl.RowText AND ColumnsTbl.ColumnIndex = ' + CAST(@ColumnIndex AS VARCHAR(12))

EXEC (@SQLstr)

SELECT @ColumnIndex = @ColumnIndex + 1

END

DROP TABLE ColumnsTbl

DROP TABLE MasterTbl

SELECT RowsTbl.* FROM RowsTbl

DROP TABLE RowsTbl

End

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

Exec uspTmpDynamicTableTry

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

Output

-------

Disposition 20100701 20100702 20100703 20100704

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

Busy 1 1 NULL 5

Callback 2 NULL 3 NULL

Ringing 1 NULL NULL 5

(3 row(s) affected)

I hope this Helps