There is no easy way to do this, as the pivot will need to be aggregated by column. Given that adding columns to the input table would cause a maintenance issue where these values will not be presented to the output until the code is changed wherever it is used, I'd say you're probably best doing it once with a stored procedure, which will dynamically generate the output you're looking for based on the schema of the input table.
I have demonstrated how this can be done, using the data you have supplied. This data is stored in a temp table (not #temp, because the stored proc won't work with temporary tables), populated thus:
CREATE TABLE temp (
_key int,
package_name varchar(50),
cost float,
included bit
)
INSERT INTO temp VALUES(1,'Package1', 10.00, 1)
INSERT INTO temp VALUES(2,'Package2', 20.00, 0)
INSERT INTO temp VALUES(3,'Package3', 20.00, 1)
The stored procedure retrieves a list of values based on the @pivot_field parameter, and uses these values as a column list to be inserted after the "Type" field. It then unions the pivot field and all other fields together to generate the rows, pivoting one column at a time. The procedure is as follows:
CREATE PROCEDURE usp_get_pivot (@table_name nvarchar(255), @pivot_field nvarchar(255)) AS
BEGIN
CREATE TABLE #temp (val nvarchar(max))
DECLARE @sql NVARCHAR(MAX), @cols NVARCHAR(MAX), @col NVARCHAR(255)
SET @sql = 'SELECT DISTINCT ' + @pivot_field + ' FROM ' + @table_name
INSERT INTO #temp EXEC sp_executesql @sql;
SET @cols = (SELECT '[' + val + '],' FROM #temp FOR XML PATH(''))
SET @cols = SUBSTRING(@cols, 1, LEN(@cols)-1)
SET @SQL = N'SELECT ''' + @pivot_field + ''' as [type], *
FROM (SELECT ' + @pivot_field + ', ' + @pivot_field + ' as ' + @pivot_field + '1 FROM ' + @table_name + ') AS source_table
PIVOT (max(' + @pivot_field + '1) FOR ' + @pivot_field + ' IN (' + @cols + ')) AS pivot_table'
DECLARE csr CURSOR FOR
SELECT c.name FROM sys.columns c, sys.objects o
WHERE c.object_id = o.object_id AND o.name = @table_name
AND c.name <> @pivot_field
ORDER BY column_id
OPEN csr
FETCH NEXT FROM csr INTO @col
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + ' UNION ALL
SELECT ''' + @col + ''' as [type], *
FROM (SELECT ' + @pivot_field + ', CAST(' + @col + ' AS VARCHAR) AS ' + @col + ' FROM ' + @table_name + ') AS source_table
PIVOT (max(' + @col + ') FOR ' + @pivot_field + ' IN (' + @cols + ')) AS pivot_table'
FETCH NEXT FROM csr INTO @col
END
CLOSE csr
DEALLOCATE csr
DROP TABLE #temp
EXEC sp_executesql @sql
END
You should be able to simply copy and paste the procedure into management studio, create the data is shown above and execute the procedure with:
EXEC usp_get_pivot 'temp', 'package_name'
