The mother of all dynamic SQL Crosstab View Builders.
Ok, so maybe it’s not too poetic, but it’s descriptive. As I found myself writing a dynamic crosstab-query builder in MS SQL for probably the fifth time of my career, I decided I’d post it here to save you all just a little time in your day. Make sure you use the saved time for something I’d approve of (smoking a good cigar, playing a little poker, something like that).
This proc is designed for when you have a dynamic set of row-oriented data you want to pivot and show column-wise. It takes four parameters:
@tableName – Name of the source table (or view) that will be queried by the new crosstab view.
@excludeCols – A comma separated list of columns to exclude from the crosstab. In most cases, will be the ID column of the table.
@valueColName – Name of the column in the @tableName table that contains the value in a given row.
@targetViewName – Name of the view that the stored procedure will create. It will *automatically* drop this column before re-creating it, so be warned.
This proc also assumes that the @tableName table (or view) has a column called [colName]. It uses this value to create the corresponding columns across the top of the output view.
Good luck!
ALTER PROCEDURE BuildCrosstabView(
@tableName VARCHAR(100),
@excludeCols VARCHAR(1000),
@valueColName VARCHAR(1000),
@targetViewName VARCHAR(100)
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @cols VARCHAR(4000)
SET @cols = ''
EXEC ('SELECT DISTINCT colName INTO ##colList FROM ' + @tableName)
DECLARE col_cursor CURSOR FOR
SELECT colName
FROM ##colList
OPEN col_cursor
DECLARE @colName VARCHAR(100)
FETCH NEXT FROM col_cursor INTO @colName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @cols <> ''
BEGIN
SET @cols = @cols + ', '
END
SET @cols = @cols + 'MAX(CASE colName WHEN ''' + @colName + ''' THEN ' + @valueColName + ' ELSE NULL END) AS [' + @colName + ']
'
FETCH NEXT FROM col_cursor INTO @colName
END
CLOSE col_cursor
DEALLOCATE col_cursor
DROP TABLE ##colList
DECLARE @sql VARCHAR(3000)
SET @sql = 'SELECT DISTINCT ' + @excludeCols + ', ' + @cols + ' FROM ' + @tableName + ' GROUP BY ' + @excludeCols
PRINT @sql
EXECUTE (@sql)
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[' + @targetViewName + ']'))
EXECUTE ('DROP VIEW ' + @targetViewName)
EXECUTE ('CREATE VIEW ' + @targetViewName + ' AS ' + @sql)
END
GO
The birth of BlinkStory.
As some of you know, I’ve started doing iPhone development recently. Between me and the Mikes (Old and New) we’ve thrown around a couple of hundred possible applications. We’re currently developing one called CashCurrent (in conjunction with another company, GraySail), but while I had some downtime on that project we decided to launch another one.
Yesterday at lunch, New Mike and I were watching O.J. get sentenced and it occurred to us that it would be great if there were a news site that turned everyone with an iPhone into a reporter. We would be seeing news as it actually happened, with millions of eyes out there to catch it on camera. After spending three or four hours going through various names, BlinkStory
I love you Dave!
Ok, that came out a little funny, but I just finished repairing my dryer with a part I got from the Appliance Repair site run by Dave Harnish. When my dryer stopped working last Sunday, I thought “uh-oh, Best Buy here I come”. But times are tight and I thought I’d do a little research before trashing the thing. I’m glad I did.
I found a troubleshooting guide on Dave’s site, and even though I initially mis-diagnosed the problem as a bad heating element, Dave’s prompt email diagnosis got me back on track. One $20 part later and I’m back in business. Thanks Dave!
Who does this election really hurt?
As I was driving my son to school this morning, it occurred to me who would be the most impacted by yesterday’s election: the little sign makers.
Without a pending election, who is going to buy hundreds of those little wire-frame signs? Will there be enough cheap mortgages and weight loss schemes to make up the deficit? I think not.
A picture of misery.
View Larger Map
I was reading the local paper on Thursday and came across the list of properties that will be auctioned by the Richland County treasurer for back taxes. I got curious, and found the list of properties on the county web site, but a big list of names and amounts just wasn’t very easy for me to picture. So I wrote a little script, geocoded the addresses, converted the results to KML, and uploaded to it to Google Maps. Hard to believe that behind every little dot there’s a story of someone’s home lost.
posts feed