How to pivot on unknown values
time to read 14 min | 2680 words

The problem with pivoting a table is that you need to pass hard-coded values, so you can’t just pass a runtime selection for it.

Here is the scenario:

Let’s go back to our payments table, which looks like this:

CREATE TABLE [dbo].[Payments](

      [Amount] [money] NOT NULL,

      [Date] [datetime] NOT NULL,

      [To] [nvarchar](50) NOT NULL

)

 

Now, I want to see how much I paid to each of my debtors for the last 12 days, but those dates aren’t fixed, there are days (too few, unfortunately) that I don’t have to pay anyone, so I’m in a bit of a bind there. So, how do I solve it? Write a dynamic query? That is possible, but I really hate it. I used a temporary table as a hash map for the values, and here is the result:

 

CREATE TABLE #IndexPerDistinctDate

(

      id int identity(1,1),

      date datetime

);

 

INSERT INTO #IndexPerDistinctDate

(

      date

)

SELECT DISTINCT

      date

FROM Payments

ORDER BY date ASC;

 

SELECT

      *

FROM

(

      SELECT

            p.[To],

            p.Amount,

            i.id as [index]

      FROM Payments p Left Outer Join #IndexPerDistinctDate i

      ON p.Date = i.Date

)

Source

PIVOT

(

      SUM(amount)

      FOR [index] IN

      (

            [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]

      )

) pvt;

 

DROP TABLE #IndexPerDistinctDate;

 

This also demonstrate another feature of pivoting in SQL Server 2005, it just merely ignores values that wasn’t specified. This is exactly what I want for this particular instance.

 

The source table looks like this:

Amount

Date

To

15

12.2.05

gas

27

14.4.05

food

32

17.7.06

drink

 

And the result from the query above looks like this (with 12 columns, but I cut it a bit so it would fit in the page):

To

1

2

3

4

drink

NULL

NULL

32

NULL

food

NULL

27

NULL

NULL

gas

15

NULL

NULL

NULL

 

Tweet Share Share 0 comments
Tags: