Have you ever wanted to do a Pivot in SQL, without listing the column names e.t.c.?
Here is the way to do it:


First, declare "demo" table:

CREATE TABLE #t
	(
	UserName	varchar(100),
	Period		varchar(100),
	Price		numeric(10,2)
	)

INSERT INTO #t SELECT 'Billy', '01/01/2011', 123
INSERT INTO #t SELECT 'Billy', '02/01/2011', 12
INSERT INTO #t SELECT 'Billy', '03/01/2011', 2
INSERT INTO #t SELECT 'Billy', '04/01/2011', 3
INSERT INTO #t SELECT 'Zebra', '01/01/2011', 1
INSERT INTO #t SELECT 'Zebra', '02/01/2011', 2
INSERT INTO #t SELECT 'Zebra', '03/01/2011', 3
INSERT INTO #t SELECT 'Zebra', '04/01/2011', 4

Then, create a table for pivoting named "#pivot".
You have to name the column on which pivot will pivot with a name "pivot.
You have to name the columns you wish to "sum" with a name which have word "cost" in it's name.

SELECT 
	UserName,
	Period AS [Pivot],
	Price AS [Price cost] 
INTO #pivot
FROM #t

And then:

DECLARE @SQL varchar(max)
DECLARE @Fields varchar(max)
SET @SQL=''  
SET @Fields=''

SELECT @Fields = @Fields + char(10) + '[' + rtrim(Name) + '],'
	FROM tempdb.dbo.syscolumns WITH (NOLOCK)
	WHERE id=OBJECT_ID('tempdb..#pivot')
	AND Name<>'pivot'
	AND Name NOT LIKE '%cost%'
SET @Fields=substring(@Fields,1,len(@Fields)-1)


SELECT @SQL = @SQL + char(10) 
	+ 'SUM(CASE WHEN (rtrim(replace(convert(varchar(20),[Pivot],101),'''''''',''''))=''' + rtrim(replace(convert(varchar(20),[Pivot],101),'''','')) + ''') '
	+ 'THEN [' + tempdb.dbo.syscolumns.name + '] ELSE 0 END) ' 
	+ 'AS [' 
		+ rtrim(ltrim(convert(varchar(20),[Pivot],101))) 
		+ ( CASE WHEN rtrim(ltrim(replace(tempdb.dbo.syscolumns.name,'cost','')))='' THEN '' ELSE ' - ' +  rtrim(ltrim(replace(tempdb.dbo.syscolumns.name,'cost',''))) END )
	+  '],'
	FROM #pivot
	JOIN tempdb.dbo.syscolumns WITH (NOLOCK) ON Name LIKE '%cost%' AND id=OBJECT_ID('tempdb..#pivot') AND [Pivot] is not null
	group by tempdb.dbo.syscolumns.name,[Pivot]
	order by [Pivot],tempdb.dbo.syscolumns.name
SET @SQL=substring(@SQL,1,len(@SQL)-1)

SELECT @SQL = 'SELECT ' + @Fields + ', ' + @SQL + char(10) + 'FROM #pivot ' + char(10) + 'GROUP BY ' + @Fields
EXEC(@SQL)

@SQL will contain the following prior to execution:

SELECT 
[UserName], 
SUM(CASE WHEN (rtrim(replace(convert(varchar(20),[Pivot],101),'''',''))='01/01/2011') THEN [Price cost] ELSE 0 END) AS [01/01/2011 - Price],
SUM(CASE WHEN (rtrim(replace(convert(varchar(20),[Pivot],101),'''',''))='02/01/2011') THEN [Price cost] ELSE 0 END) AS [02/01/2011 - Price],
SUM(CASE WHEN (rtrim(replace(convert(varchar(20),[Pivot],101),'''',''))='03/01/2011') THEN [Price cost] ELSE 0 END) AS [03/01/2011 - Price],
SUM(CASE WHEN (rtrim(replace(convert(varchar(20),[Pivot],101),'''',''))='04/01/2011') THEN [Price cost] ELSE 0 END) AS [04/01/2011 - Price]
FROM #pivot 
GROUP BY 
[UserName]