A colleague was interested in solving a reporting problem for our business users.  And as many times go, I ask him what he wants the end result to be, so that I can figure out how I would tackle the problem.  He explains that he wants the following:

This table represents a Personal Identifier (Drivers License ID, Business ID, etc), followed by fine amounts (the lower number being the most recent fine).  Other examples will use column headers of other known values such as product lines (Apples, Oranges, etc), or dates (Jan 2011, Feb 2011, etc) as summation values.

Let’s use the following Table Variables as our data model:

DECLARE @Person TABLE (
	PersonID INT IDENTITY,
	LicenseNumber VARCHAR(10)
)
DECLARE @Ticket TABLE (
	TicketID INT IDENTITY,
	PersonID INT,
	TicketDate DATETIME,
	TicketAmount DECIMAL(19,2)
)

To me, it seemed that a “Pivot” table was in order.  Based on past experience, I knew that I needed to get the data in the following format, before doing the pivot…

To get the data like this, we would use the following SQL:

SELECT	P.LicenseNumber,
		ROW_NUMBER() OVER(PARTITION BY T.PersonID
			ORDER BY T.TicketDate DESC)
			TicketRowNumber,
		T.TicketAmount
FROM	@Person P
	LEFT JOIN @Ticket T ON P.PersonID = T.PersonID
ORDER BY 1, 2

Simple enough, right? We use the ROW_NUMBER with PARTITION BY to segment we want to reset the count each time a new PersonID is encountered. This will get us our column headers for the Pivot table.

Now, we just add in the Pivot call, and we can get our data as we want it. All together, that SQL will look like:

SELECT	*
FROM	(
	SELECT	P.LicenseNumber,
			ROW_NUMBER() OVER(PARTITION BY T.PersonID
				ORDER BY T.TicketDate DESC)
				TicketRowNumber,
			T.TicketAmount
	FROM	@Person P
		LEFT JOIN @Ticket T ON P.PersonID = T.PersonID
	) IV
PIVOT
(
	MAX(TicketAmount)
	FOR TicketRowNumber IN ([1], [2], [3], [4], [5])
) AS PivotTable

Some things to note: This does not handle dynamic columns. There are other examples out there that can cover this, so I will wait until I have a need to create another post for it.

And here is all of the SQL that will also generate some test data:

DECLARE @Person TABLE (
	PersonID INT IDENTITY,
	LicenseNumber VARCHAR(10)
)
DECLARE @Ticket TABLE (
	TicketID INT IDENTITY,
	PersonID INT,
	TicketDate DATETIME,
	TicketAmount DECIMAL(19,2)
)

INSERT INTO @Person VALUES ('0001')
INSERT INTO @Person VALUES ('0002')
INSERT INTO @Person VALUES ('0003')
INSERT INTO @Person VALUES ('0004')
INSERT INTO @Person VALUES ('0005')

INSERT INTO @Ticket VALUES (1, '1/1/2011', 500)
INSERT INTO @Ticket VALUES (3, '1/4/2011', 300)
INSERT INTO @Ticket VALUES (2, '1/5/2011', 200)
INSERT INTO @Ticket VALUES (1, '1/10/2011', 600)
INSERT INTO @Ticket VALUES (1, '2/1/2011', 2000)
INSERT INTO @Ticket VALUES (5, '3/1/2011', 250)

SELECT	*
FROM	(
	SELECT	P.LicenseNumber,
			ROW_NUMBER() OVER(PARTITION BY T.PersonID
				ORDER BY T.TicketDate DESC)
				TicketRowNumber,
			T.TicketAmount
	FROM	@Person P
		LEFT JOIN @Ticket T ON P.PersonID = T.PersonID
		) IV
PIVOT
(
	MAX(TicketAmount)
	FOR TicketRowNumber IN ([1], [2], [3], [4], [5])
) AS PivotTable

Enjoy!

RJ Hatch

RJ writes custom Windows and Web applications using the Microsoft .NET framework, and enjoys solving problems with code, often while listening to a techno soundcloud stream. He hopes to one day write an application that many businesses will use.