SQL Tips and Tricks

Write the first paragraph of your article here.

Dynamic Pivot
SET NOCOUNT ON;

CREATE TABLE [dbo].[Time](	[empID] [int] NOT NULL,	[projectID] [int] NOT NULL,	[date] [smalldatetime] NOT NULL,	[hours] [decimal](9, 2) NOT NULL );

insert into [Time] values (1,1,'2010-01-01', 7); insert into [Time] values (1,1,'2010-01-02', 6.5); insert into [Time] values (1,2,'2010-01-03', 7.5); insert into [Time] values (1,2,'2010-01-04', 7.5); insert into [Time] values (1,2,'2010-01-05', 7.5); insert into [Time] values (2,1,'2010-01-01', 8); insert into [Time] values (2,2,'2010-01-02', 8); insert into [Time] values (2,1,'2010-01-05', 9);

-- Dynamic PIVOT DECLARE @T AS TABLE(y int);

DECLARE @cols AS NVARCHAR(MAX), @y   AS INT, @sql AS NVARCHAR(MAX);

-- Construct the column list for the IN clause SET @cols = STUFF ( ( SELECT N',' + QUOTENAME(y) AS [text] FROM ( SELECT DISTINCT convert(varchar(10),[date],102) AS y 		  FROM [time] ) AS y	ORDER BY y	FOR XML PATH() ), 1, 1, N);

-- Construct the full T-SQL statement -- and execute dynamically SET @sql = N' SELECT * FROM ( SELECT empid, convert(varchar(10),[date],102) as [date], hours      FROM dbo.time ) AS D PIVOT ( SUM(hours) FOR [date]     IN ( ' + @cols + N' ) ) AS P;';

select @sql;

EXEC sp_executesql @sql;

drop table [Time];

SET NOCOUNT OFF;

Section heading
Write the second section of your article here.