Using UNPIVOT in SQL Server

Today I had to write a procedure to unpivot a table with an unknown number of columns / column names, I’ve used a combination of user defined function and dynamic sql to build a sql unpivot statement and then execute it…

First create function…

CREATE FUNCTION [dbo].[Udfgetcolumnnames] (@Table VARCHAR(100))
RETURNS VARCHAR(MAX)
AS
  BEGIN
      DECLARE @str VARCHAR(MAX)

      SET @str =”

      SELECT @str = @str + column_name + ‘,’ frominformation_schema.columns
      WHERE  table_name = @Table
             AND column_name NOT IN( ‘ShiftStart’, ‘AgentName’, ‘Date’ )

      SET @str =Substring(@str, 1, Len(@str) – 1)

      RETURN @str
  END  

Then use it to help define the SQL…

DECLARE @sql NVARCHAR(MAX)

SET @sql =”
SET @sql =
‘SELECT AgentName,  Date, pvt.Interval, pvt.ActivityCode FROM ActualAgentActivity UNPIVOT ( ActivityCode for Interval in (‘ +
dbo.Udfgetcolumnnames(‘ActualAgentActivity’) + ‘)  ) as pvt WHERE pvt.ActivityCode <> ”.”’

EXEC Sp_executesql @sql 

You will notice I hardcoded some values in my function – Trying to add this functionality as an extra parameter/s was a complete nightmare and I ran out of time.  If anyone has a suggestion on how this could be made more generic I would love to hear it (given that there could be any number of excluded columns)