Rows to Columns Pivot example

/****** Object:  StoredProcedure [dbo].[msp_Costing_Data_Select]    Script Date: 01/03/2008 20:09:01 ******/
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[msp_Costing_Data_Select]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[msp_Costing_Data_Select]
 
/****** Object:  StoredProcedure [dbo].[msp_Costing_Data_Select]    Script Date: 01/03/2008 20:09:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[msp_Costing_Data_Select]
	@costingid [int]
AS
	DECLARE @cols		[nvarchar](MAX)
	DECLARE @SQL		[nvarchar](MAX)
 
	SELECT		@cols = COALESCE(@cols + ',[' + [KEY] + ']','[' + [KEY] + ']')
	FROM		[dbo].[Costing_Schema]
	ORDER BY	[position]
 
	--PRINT @cols
 
	SET @SQL =	N'SELECT ' + @cols + '
					FROM	( 
								SELECT  cd.[uid]
										,cs.[key]
										,cd.[value]
								FROM    [dbo].[Costing_Schema] AS cs
								JOIN	[dbo].[Costing_Data] As cd 
								ON		cs.[key] = cd.[key] 
								WHERE	cd.[costingid] = ' + CAST(@costingid AS nvarchar) + '
							) p PIVOT	(MAX([value])
										FOR [key] IN (' + @cols + ')
										) AS pvt
							ORDER BY [uid]'
	--PRINT @SQL
 
	EXEC sp_executesql @SQL
GO
 
GRANT EXECUTE ON [dbo].[msp_Costing_Data_Select] TO [costingWeb]
GO