Posted By

pauliehaha on 02/09/09


Tagged


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

Eloi


Rows to Columns Pivot example


 / Published in: SQL
 

  1. /****** Object: StoredProcedure [dbo].[msp_Costing_Data_Select] Script Date: 01/03/2008 20:09:01 ******/
  2. IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[msp_Costing_Data_Select]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
  3. DROP PROCEDURE [dbo].[msp_Costing_Data_Select]
  4.  
  5. /****** Object: StoredProcedure [dbo].[msp_Costing_Data_Select] Script Date: 01/03/2008 20:09:01 ******/
  6. SET ANSI_NULLS ON
  7. GO
  8. SET QUOTED_IDENTIFIER ON
  9. GO
  10. CREATE PROCEDURE [dbo].[msp_Costing_Data_Select]
  11. @costingid [int]
  12. AS
  13. DECLARE @cols [nvarchar](MAX)
  14. DECLARE @SQL [nvarchar](MAX)
  15.  
  16. SELECT @cols = COALESCE(@cols + ',[' + [KEY] + ']','[' + [KEY] + ']')
  17. FROM [dbo].[Costing_Schema]
  18. ORDER BY [position]
  19.  
  20. --PRINT @cols
  21.  
  22. SET @SQL = N'SELECT ' + @cols + '
  23. FROM (
  24. SELECT cd.[uid]
  25. ,cs.[key]
  26. ,cd.[value]
  27. FROM [dbo].[Costing_Schema] AS cs
  28. JOIN [dbo].[Costing_Data] As cd
  29. ON cs.[key] = cd.[key]
  30. WHERE cd.[costingid] = ' + CAST(@costingid AS nvarchar) + '
  31. ) p PIVOT (MAX([value])
  32. FOR [key] IN (' + @cols + ')
  33. ) AS pvt
  34. ORDER BY [uid]'
  35. --PRINT @SQL
  36.  
  37. EXEC sp_executesql @SQL
  38. GO
  39.  
  40. GRANT EXECUTE ON [dbo].[msp_Costing_Data_Select] TO [costingWeb]
  41. GO

Report this snippet  

You need to login to post a comment.