Solved

Creating a pivot of some data.

Posted on 2009-05-07
28
328 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:RQuadling
  • 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:RQuadling
ID: 24325827
Yah. The ugly way.

Testing...
0
 
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:RQuadling
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:RQuadling
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 40

Author Closing Comment

by:RQuadling
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:RQuadling
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:RQuadling
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

747 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now