Solved

Creating a pivot of some data.

Posted on 2009-05-07
28
335 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:Richard Quadling
  • 9
  • 8
  • 6
  • +2
28 Comments
 
LVL 42

Accepted Solution

by:
EugeneZ earned 400 total points
ID: 24325734
0
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 50 total points
ID: 24325764
whats your problem? your code seems ok, since you are looking for creating 24 column, you need dynamic pivot,
have a look at

http://www.sqlhub.com/search?q=PIVOT
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 24325827
Yah. The ugly way.

Testing...
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 51

Expert Comment

by:Mark Wills
ID: 24325932
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

0
 
LVL 41

Expert Comment

by:ralmada
ID: 24325955
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

0
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 50 total points
ID: 24325964
sorry typo
DECLARE @Cols VARCHAR(2000)
DECLARE @strQry VARCHAR(2000)
 
;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

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24326057
Ralmada,

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

Expert Comment

by:RiteshShah
ID: 24326086
Ralmada,

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

SET @Cols=LEFT(@Cols,LEN(@Cols)-1)
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 24326099
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

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24326125
so, what is the result now?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24326135
give me a moment - writing a function to emulate periods - starting from 1289 - sheesh... mine will be different :)
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 24326147
Exactly what I want. The blog has it.
0
 
LVL 41

Expert Comment

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

Expert Comment

by:ralmada
ID: 24326186
anyhow, forget about it. I'm curious to see marks solution.
0
 
LVL 40

Author Closing Comment

by:Richard Quadling
ID: 31578986
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.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24326365
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

0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 24326541
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

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24326747
Mark, Good script, I appreciate.  :)
0
 
LVL 40

Author Comment

by:Richard Quadling
ID: 24327027
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

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24327056
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

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24327091
all I can say is COOOOOOL !!!!
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24327821
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

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24327969
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 :)
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24332731
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
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24333306
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 ?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24333308
p.s. my email is on my bio....
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24333312
It would be great if you can create one simple and small example, I will publish it along with your script.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24333317
you can send me email with the script and example. my email address is in my BIO here.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Live Storage Vmotion VMs with shared VMDK 10 61
Why Delete * statement wont work with sql server ? 6 43
VB.NET 2008 - SQL Timeout 9 25
Proper Case SQL Command 2 13
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question