SQL, VB, MicroStrategy & Other Topics

Somebody at work asked me today, "Ankeet it is hard to understand/remember how to use PIVOT tables in sql. Can you give me an example?". So here it is. I have used table name prefixes so as to make the code easier to understand and be able to identify where does each field come from. I rarely use pivot tables in sql. use dbName go SELECT myPivotTable.CustomerID, isnull(myPivotTable.[2003],0) as [2003 ActualPaid], isnull(myPivotTable.[2004],0) as [2004 ActualPaid] FROM ( select phf.CustomerID, phf.SystemYear, phf.ActualPaid from Sales as phf where phf.CustomerID between 33131 and 33250 ) AS mySourceTable PIVOT ( avg(mySourceTable.ActualPaid) for mySourceTable.SystemYear in ([2003],[2004]) ) AS myPivotTable ORDER BY myPivotTable.CustomerID

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go CREATE PROCEDURE [dbo].[sp_fixusers] AS BEGIN DECLARE @username varchar(25) DECLARE fixusers CURSOR FOR SELECT UserName = name FROM sysusers WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null ORDER BY name OPEN fixusers FETCH NEXT FROM fixusers INTO @username WHILE @@FETCH_STATUS = 0 BEGIN EXEC sp_change_users_login 'update_one', @username, @username FETCH NEXT FROM fixusers INTO @username END CLOSE fixusers DEALLOCATE fixusers END Please consider the environment before printing this blog! Go Green!

Few years ago, a friend of mine working for Ramco Systems 'taught' me a logic of incrementally and conditionally updating a table with help of a few variables. I had to use that logic this AM and it required some brush-up, so now I am going to post the logic and sample code right here for all other SQL programmers... create table #temp(num int , runningSum int ) INSERT INTO #TEMP VALUES (1, NULL) INSERT INTO #TEMP VALUES (2, NULL) INSERT INTO #TEMP VALUES (3, NULL) INSERT INTO #TEMP VALUES (4, NULL) INSERT INTO #TEMP VALUES (5, NULL) INSERT INTO #TEMP VALUES (6, NULL) declare @RunningSum int SET @RunningSum = 0 UPDATE #TEMP SET RunningSum = @RunningSum, @RunningSum = @RunningSum + num SELECT * FROM #TEMP DROP TABLE #Temp Results: num         runningSum ----------- ----------- 1           1 2           3 3 ...