SQL Server PIVOT perhaps?

by

Since you are using SQL Server 2005, here is the code:

DECLARE @cols VARCHAR(1000)
DECLARE @sqlquery VARCHAR(2000)

SELECT  @cols = STUFF(( SELECT distinct  ',' + QuoteName([Name1])
                        FROM myTable FOR XML PATH('') ), 1, 1, '') 


SET @sqlquery = 'SELECT * FROM
      (SELECT Name2, Name1, Value
       FROM myTable ) base
       PIVOT (Sum(Value) FOR [Name1]
       IN (' + @cols + ')) AS finalpivot'

EXECUTE ( @sqlquery )

This will work no matter how many different status you have. It dynamically assembles a query with PIVOT. The only way you can do PIVOT with dynamic columns is by assembling the the query dynamically, which can be done in SQL Server.

Other examples:

More Related Contents:

  1. Insert an image in a column of a table that already has 5 columns
  2. Get top 1 row of each group
  3. Referring to a Column Alias in a WHERE Clause
  4. How to SELECT FROM stored procedure
  5. Convert Month Number to Month Name Function in SQL
  6. How to avoid the “divide by zero” error in SQL?
  7. SET versus SELECT when assigning variables?
  8. T-SQL dynamic pivot
  9. Is there any difference between IS NULL and =NULL
  10. Generate a resultset of incrementing dates in TSQL
  11. Real life example, when to use OUTER / CROSS APPLY in SQL
  12. Getting result of dynamic SQL into a variable for sql-server
  13. How to get difference between two rows for a column field?
  14. Better techniques for trimming leading zeros in SQL Server?
  15. How to print VARCHAR(MAX) using Print Statement?
  16. SQL statement to select all rows from previous day
  17. What’s the best way to select the minimum value from several columns?
  18. Concatenate values based on ID
  19. SQL : in clause in stored procedure:how to pass values
  20. Dynamic Pivot Table in SQL Server
  21. How to insert a blob into a database using sql server management studio
  22. SQL GROUP BY CASE statement with aggregate function
  23. Best way to do nested case statement logic in SQL Server
  24. Can an INNER JOIN offer better performance than EXISTS
  25. SQL server identity column values start at 0 instead of 1
  26. How to compare dates in SQL Server
  27. SQL Server 2005 – Export table programmatically (run a .sql file to rebuild it)
  28. SQL Sort Order with Null values last
  29. Insert all values of a table into another table in SQL
  30. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?

Leave a Comment