asked on

Creating a pivot of some data.

Hi.

Using an SQL Server 2005 database.

I've got a result set which is customer/period/payment.

There is a LOT of data.

I want a result set whose columns are Customer, Period, Period-1, Period-2, etc.

And the values for the period columns is the payment amount.

I only need the last 2 years (24 periods).

So, I start with ...

SELECT
      PB.CustomerCode,
      dbo.CcYyMmmmFromPeriod(PB.Period) AS Period,
      SterlingPayments
FROM
      Customers CU
      INNER JOIN
      PeriodBanks PB
            ON
                  CU.CustomerCode = PB.CustomerCode
WHERE
      ISNULL(CU.ShortName, '') <> 'CLOSED'
      AND
      PB.Period >= ((DATEPART(year, GetDate()) - 1900) * 12 + DATEPART(month, GetDate())) - 24
ORDER BY
      PB.CustomerCode,
      PB.Period DESC

And this returns the correct results (just under 18,000 rows).

OOI. The PB.Period is the number of months since 1900 and the CcYyMmmm function returns the period in LongMonth LongYear format - May 2009 (for example).

I tried to add the pivot, but I'm sort of stuck (see snippet)

It seems as if I have to physically name each and every column.

Which seems wrong. Introducing a new period (something that happens every month) should entail a new query.

It looks like I have to use an SP to do this, but that seems overkill.

Any ideas?

SELECT
	*
FROM
	(
	SELECT
		PB.CustomerCode,
		dbo.CcYyMmmmFromPeriod(PB.Period) AS Period,
		SterlingPayments
	FROM
		Customers CU
		INNER JOIN
		PeriodBanks PB
			ON
				CU.CustomerCode = PB.CustomerCode
	WHERE
		ISNULL(CU.ShortName, '') <> 'CLOSED'
		AND
		PB.Period >= ((DATEPART(year, GetDate()) - 1900) * 12 + DATEPART(month, GetDate())) - 24
	) Data
PIVOT
	(
	SUM(Data.SterlingPayments)
	FOR Data.Period
	IN ([May 2009], [April 2009], [March 2009])
	) Pivotted
ORDER BY
	Pivotted.CustomerCode ASC

Open in new window

ASKER CERTIFIED SOLUTION

EugeneZ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

RiteshShah

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Richard Quadling

ASKER

Yah. The ugly way.

Testing...

Mark Wills

Yeah, have to mention each period. Not up the top so much (can use select * ) but the pivot needs explicit [column names]

So, need a stored procedure or script....

If you give me a moment, will type up the real thing, in the meantime, I normally extract the column names with something like :


declare @sql varchar(8000)
declare @columns varchar(8000)
 
set @columns = substring((select ', [' [period] ']' from #table group by [period]  for xml path('')),2,8000)
 
set @sql = 'SELECT * FROM 
    (SELECT [key], [value], [period] as new_columns from #table) src
     PIVOT
    (MAX ([value]) FOR new_columns IN (' @columns ') ) AS pvt'
 
exec (@sql)

Open in new window

ralmada

Please give this a try:

DECLARE @Cols VARCHAR(2000)
DECLARE @strQry VARCHAR(2000)
 
SELECT @Cols=@Cols+ '['+s.Passyear +']'+ ', ' FROM
 
(SELECT DISTINCT PassYear FROM StudDetail) AS s
 
 
;WITH CTE as (
	SELECT DISTINCT
		dbo.CcYyMmmmFromPeriod(PB.Period) AS Period,
	FROM
		Customers CU
		INNER JOIN
		PeriodBanks PB
			ON
				CU.CustomerCode = PB.CustomerCode
	WHERE
		ISNULL(CU.ShortName, '') <> 'CLOSED'
		AND
		PB.Period >= ((DATEPART(year, GetDate()) - 1900) * 12 + DATEPART(month, GetDate())) - 24
)
SELECT @cols = COALESCE('[' + @cols+'],' ,'') + Period FROM CTE
 
 
SET @strQry = 'SELECT
*
FROM
	(
	SELECT
		PB.CustomerCode,
		dbo.CcYyMmmmFromPeriod(PB.Period) AS Period,
		SterlingPayments
	FROM
		Customers CU
		INNER JOIN
		PeriodBanks PB
			ON
				CU.CustomerCode = PB.CustomerCode
	WHERE
		ISNULL(CU.ShortName, '') <> 'CLOSED'
		AND
		PB.Period >= ((DATEPART(year, GetDate()) - 1900) * 12 + DATEPART(month, GetDate())) - 24
	) Data
PIVOT
	(
	SUM(Data.SterlingPayments)
	FOR Data.Period
	IN (' + @Cols +)
	) Pivotted
ORDER BY
	Pivotted.CustomerCode ASC'
 
exec(@strQry)

Open in new window

SOLUTION

ralmada

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RiteshShah

Ralmada,

I guess you have taken my example from my blog but forget to remove StudDetails table ;)

RiteshShah

Ralmada,

In that case you have to remove last "," like

SET @Cols=LEFT(@Cols,LEN(@Cols)-1)

Richard Quadling

ASKER

And I've just been using the blog as the source, so Ralmada has just produced the same code I've just used.

DECLARE
	@Periods VARCHAR(8000),
	@Query VARCHAR(8000)
 
SELECT
	@Periods = ISNULL
		(
		@Periods + ', [' + dbo.CcYyMmmmFromPeriod(Period) + ']',
		'[' + dbo.CcYyMmmmFromPeriod(Period) + ']'
        )
FROM
	(
	SELECT DISTINCT TOP 24
		PB.Period
	FROM
		Customers CU
		INNER JOIN
		PeriodBanks PB
			ON
				ISNULL(CU.ShortName, '') <> 'CLOSED'
				AND
				CU.CustomerCode = PB.CustomerCode
	ORDER BY
		PB.Period DESC
	) Periods
ORDER BY
	Period DESC
 
SET @Query = 'SELECT
    CustomerCode, ' + @Periods + '
FROM
        (
        SELECT
                PB.CustomerCode,
                dbo.CcYyMmmmFromPeriod(PB.Period) AS Period,
                PB.SterlingPayments
        FROM
                Customers CU
                INNER JOIN
                PeriodBanks PB
                        ON
                                CU.CustomerCode = PB.CustomerCode
        ) Data
PIVOT
        (
        SUM(Data.SterlingPayments)
        FOR Data.Period
        IN (' + @Periods + ')
        ) Pivotted
ORDER BY
        Pivotted.CustomerCode ASC'
 
EXECUTE (@Query)

Open in new window

RiteshShah

so, what is the result now?

Mark Wills

give me a moment - writing a function to emulate periods - starting from 1289 - sheesh... mine will be different :)

Richard Quadling

ASKER

Exactly what I want. The blog has it.

ralmada

>>In that case you have to remove last "," like <<
No need to, I've used
SELECT @cols = COALESCE('[' + @cols+'],' ,'') + Period FROM CTE

ralmada

anyhow, forget about it. I'm curious to see marks solution.

Richard Quadling

ASKER

EugeneZ has the majority of the points here as his link was the useful and covered all the things I wanted. The other posts didn't actually get to me before I had arrived at a working solution.

Mark Wills

Well, for what it is worth... and have to remember not to use the beta when posting (removes plus signs - apologies in advance if they drop off)

declare @start_period int
set @start_period = ((DATEPART(year, GetDate()) - 1900) * 12 + DATEPART(month, GetDate())) - 24 
 
declare @columns varchar(max)
set @columns = substring((select ', ['+dbo.CcYyMmmmFromPeriod(Period)+']' from periodbanks where period >=@start_period group by [period]  for xml path('')),2,8000)
 
declare @sql varchar(max)
set @sql = 'SELECT * FROM 
 
(SELECT     PB.CustomerCode, dbo.CcYyMmmmFromPeriod(PB.Period) AS Period, SterlingPayments
 FROM       Customers CU
 INNER JOIN PeriodBanks PB ON CU.CustomerCode = PB.CustomerCode
 WHERE      ISNULL(CU.ShortName, '''') <> ''CLOSED''
 AND        PB.Period >= '+convert(varchar,@start_period)+') Data
 
PIVOT
 
(SUM(Data.SterlingPayments) FOR Data.Period IN ('+@columns+') ) Pivotted
 
ORDER BY 1'
 
exec(@sql)

Open in new window

Richard Quadling

ASKER

This is my final code. Now as an SP.

CREATE PROCEDURE [dbo].[PaymentHistory]
AS
BEGIN
SET NOCOUNT ON;
 
DECLARE
	@Periods VARCHAR(8000),
	@Query VARCHAR(8000),
	@HistoryLimit INTEGER
 
SELECT
	@HistoryLimit = MAX(Period) - 24
FROM
	PeriodBanks
 
SELECT
	@Periods = ISNULL
		(
		@Periods + ', [' + dbo.CcYyMmmmFromPeriod(Period) + ']',
		'[' + dbo.CcYyMmmmFromPeriod(Period) + ']'
	)
FROM
	(
	SELECT DISTINCT TOP 24
		PB.Period
	FROM
		PeriodBanks PB
	ORDER BY
		PB.Period DESC
	) Periods
ORDER BY
	Period DESC
 
SET @Query = 'SELECT
	CustomerCode,
	Name,
	' + @Periods + '
FROM
	(
	SELECT
		PB.CustomerCode,
		CU.Name,
		dbo.CcYyMmmmFromPeriod(PB.Period) AS Period,
		PB.SterlingPayments
	FROM
		Customers CU
		INNER JOIN
		PeriodBanks PB
			ON
				ISNULL(CU.ShortName, '''') <> ''CLOSED''
				AND
				CU.CustomerCode = PB.CustomerCode
				AND
				PB.Period > ' + CAST(@HistoryLimit AS VARCHAR(4)) + '
	) Data
PIVOT
	(
	SUM(Data.SterlingPayments)
	FOR Data.Period
	IN (' + @Periods + ')
	) Pivotted
ORDER BY
	Pivotted.CustomerCode ASC'
 
EXECUTE (@Query)
END

Open in new window

RiteshShah

Mark, Good script, I appreciate. :)

Richard Quadling

ASKER

And who says a programmers work is ever finished. Now they want to include the archive data also! ARGH!And other stuff.

USE [TripleCDataConversion]
GO
/****** Object:  StoredProcedure [dbo].[PaymentHistory]    Script Date: 05/07/2009 16:40:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author      : Richard Quadling
-- Create date : Thursday, 7th May 2009
-- Description : Provide the last 24 periods of payments
-- =============================================
ALTER PROCEDURE [dbo].[PaymentHistory]
AS
BEGIN
SET NOCOUNT ON;
 
DECLARE
	@Periods VARCHAR(8000),
	@Query VARCHAR(8000),
	@HistoryRangeStart INTEGER,
	@HistoryRangeEnd INTEGER
 
-- Find the limit of the historical analysis.
SELECT
	@HistoryRangeStart = CurrentPeriod - 23,
	@HistoryRangeEnd = CurrentPeriod
FROM
	StaticData
 
-- Build the column names.
SELECT
	@Periods = ISNULL
		(
		@Periods + ', [' + dbo.CcYyMmmmFromPeriod(Period) + ']',
		'[' + dbo.CcYyMmmmFromPeriod(Period) + ']'
	)
FROM
	(
	SELECT DISTINCT
		PB.Period
	FROM
		PeriodBanks PB
	WHERE
		PB.Period BETWEEN @HistoryRangeStart AND @HistoryRangeEnd
	) Periods
ORDER BY
	Period DESC
 
-- Build the query to generate the pivotted dataset.
SET @Query = 'SELECT
	CustomerCode,
	Name,
	ShortName,
	' + @Periods + '
FROM
	(
	SELECT
		PBCombined.CustomerCode,
		CU.Name,
		CU.ShortName,
		dbo.CcYyMmmmFromPeriod(PBCombined.Period) AS Period,
		PBCombined.SterlingPayments
	FROM
		Customers CU
		INNER JOIN
			(
			SELECT
				PBLive.CustomerCode,
				PBLive.Period,
				PBLive.SterlingPayments
			FROM
				PeriodBanks PBLive
			WHERE
				PBLive.Period BETWEEN ' + CAST(@HistoryRangeStart AS VARCHAR(4)) + ' AND ' + CAST(@HistoryRangeEnd AS VARCHAR(4)) + '
 
			UNION ALL
 
			SELECT
				PBArchive.CustomerCode,
				PBArchive.Period,
				PBArchive.SterlingPayments
			FROM
				ArchivedBM PBArchive
			WHERE
				PBArchive.Period BETWEEN ' + CAST(@HistoryRangeStart AS VARCHAR(4)) + ' AND ' + CAST(@HistoryRangeEnd AS VARCHAR(4)) + '
			) PBCombined
			ON
				CU.CustomerCode = PBCombined.CustomerCode
	) Data
PIVOT
	(
	SUM(Data.SterlingPayments)
	FOR Data.Period
	IN (' + @Periods + ')
	) Pivotted
ORDER BY
	Pivotted.CustomerCode ASC'
 
-- Run the query.
EXECUTE (@Query)
END

Open in new window

Mark Wills

Thanks RiteshShah, I appreciate your acknowledgement.

I did have a stored procedure somewhere that already did most of that. All you have to provide is a column name, the pivot on column, the select column and the table and it does it all for you. Then any complex query, just create as a view - which is what I started to do... but dates got out of hand...

have a look below - there is another trick using dates - see how they dynamically convert in the pivot...

create view vw_last_2_years
as
SELECT
      PB.CustomerCode,
      dateadd(month,1,convert(datetime,'01 '+ dbo.CcYyMmmmFromPeriod(PB.Period))) - 1 AS Period_End_Date,
      SterlingPayments
FROM
      Customers CU
      INNER JOIN
      PeriodBanks PB ON CU.CustomerCode = PB.CustomerCode
WHERE
      ISNULL(CU.ShortName, '') <> 'CLOSED'
      AND
      PB.Period >= ((DATEPART(year, GetDate()) - 1900) * 12 + DATEPART(month, GetDate())) - 24
go
 
 
-- now the pivot. notice we are using string dd MMM yyyy as column headers and is dynamically / implicitly converted if used in a datetime context
-- that way we can keep the correct chronology for headings.
 
 
declare @columns varchar(max)
set @columns = substring((select ', ['+convert(varchar,period_end_date,106)+']' from vw_last_2_years group by period_end_date for xml path('')),2,8000)
 
declare @sql varchar(max)
set @sql = 'SELECT * FROM 
(SELECT customercode, period_End_Date, sterlingpayments from vw_last_2_years) Data
PIVOT
(SUM(SterlingPayments) FOR Period_End_Date IN ('+@columns+') ) Pivotted
ORDER BY 1'
 
exec(@sql)

Open in new window

RiteshShah

all I can say is COOOOOOL !!!!

Mark Wills

And as for that dynamic stored procedure (re-wrote the beggar) - using the vw_last_2_years view from above... it should work for any pivot that can be expressed from a single datasource. note the last param is a style code for converts, can be '' and only really used for dates.

create procedure usp_dynamic_pivot(@sourcedata varchar(200), @pivot_by varchar(2000), @pivot_on_type varchar(200), @pivot_on varchar(200), @pivot_for varchar(200), @pivot_for_style varchar(4))
as 
begin
 
declare @columns varchar(max)
declare @sql nvarchar(max)
 
set @sql = N'set @columns = substring((select '', [''+convert(varchar,'+@pivot_for+@pivot_for_style+')+'']'' from '+@sourcedata+' group by '+@pivot_for+' for xml path('''')),2,8000)'
execute sp_executesql @sql,
                      N'@columns varchar(8000) output',
                      @columns=@columns output 
 
set @sql = N'SELECT * FROM 
    (SELECT '+@pivot_by+','+@pivot_for+','+@pivot_on+' from '+@sourcedata+') src
    PIVOT
    ('+@pivot_on_type+'('+@pivot_on+') FOR '+@pivot_for+' IN ('+@columns+') ) pvt
    ORDER BY 1'
execute sp_executesql @sql
 
end
 
 
-- now use the stored procedure - so long as pivot can be expressed in terms of a single data source...
 
usp_dynamic_pivot 'vw_last_2_years','customercode','sum','sterlingpayments','period_end_date',',106'

Open in new window

Mark Wills

Told you I was slow, and somewhat deliberate with my answers. Now does anyone want a freshly coded function CcYyMmmmFromPeriod (@p int) ?

All too late, hope someone finds it helpful, or maybe educational, or maybe just for fun :)

RiteshShah

Hi Mark,

I really like your script, can I post it on my Blog at SQLHub.com, ofcourse, with your name and reference.... I just want to share it so more people can get benifit out of it

Mark Wills

Sure, would like to see my name up there :)

Need to put a small caveat as to limitation of use (as in might need to prepare a view first), and of course, no error checking.

Do you want me to tidy it up first ?

Mark Wills

p.s. my email is on my bio....

RiteshShah

It would be great if you can create one simple and small example, I will publish it along with your script.

RiteshShah

you can send me email with the script and example. my email address is in my BIO here.