unpivot with dynamic columns plus column names

by

You can reference the column name from the val for col in part of the unpivot. Col gets the column name

Example Fiddle

-- Build list of cols we want to unpivot (skip PID & UID)
declare @cols nvarchar(max) 
select @cols = coalesce(@cols+N',', N'') + quotename(c.name) from syscolumns c
inner join sysobjects o on c.id = o.id and o.xtype="u"
where o.name="MyTable" and c.name not in ('PID', 'UID') order by c.colid

declare @query nvarchar(max)  

select @query = N'
select PID, [UID], Col as ID, Val
from 
    (
    select PID, UID, ' + @cols + '
    from MyTable
    where UID <> 0
    ) as cp
    unpivot
    (
    Val for Col in (' + @cols + ')
    ) as up
'
exec sp_executesql @query

More Related Contents:

  1. Simulating group_concat MySQL function in Microsoft SQL Server 2005?
  2. Insert results of a stored procedure into a temporary table
  3. How can I do an UPDATE statement with JOIN in SQL Server?
  4. SQL Server : Columns to Rows
  5. Select top 10 records for each category
  6. The multi-part identifier could not be bound
  7. SQL transpose full table
  8. How important is the order of columns in indexes?
  9. Using the result of an expression (e.g. Function call) in a stored procedure parameter list?
  10. SQL Server – transactions roll back on error?
  11. Difference between a User and a Login in SQL Server
  12. Convert Xml to Table SQL Server
  13. Fastest way to update 120 Million records
  14. For Nvarchar(Max) I am only getting 4000 characters in TSQL?
  15. How to find current transaction level?
  16. How do I group on continuous ranges
  17. “select * from table” vs “select colA, colB, etc. from table” interesting behaviour in SQL Server 2005
  18. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  19. Extracting hours from a DateTime (SQL Server 2005)
  20. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  21. How to escape square brackets inside square brackets for field name
  22. Average of multiple columns
  23. What is the difference between a stored procedure and a view?
  24. How to change column order in a table using sql query in sql server 2005?
  25. Add a row number to result set of a SQL query
  26. Backup SQL Schema Only?
  27. Is there a quick way to check if ANY column is NULL?
  28. SQL query for parent-child chain
  29. Postgres: convert single row to multiple rows (unpivot)
  30. SQL Unpivot multiple columns Data

Leave a Comment