February 1, 2013 at 5:29 am
Hello Everyone.
I'm struggling a little bit with doing a transposition of some data and wondered if any of you incredibly clever people (Blatent flattery :-D) would be able to help please? Just some pointers in the right direction would be great as I've been trying with pivots and I just cannot figure out how that would work in this case.
I have some data which is effectively in the following format.
ReportDateTurnoverRetention1Retention2NewClientsAverageBillAverageBillLastYearLeaderContribution
31/12/201234737.3267647410739.594439.73147816.86
30/11/201234612.145934067748.499456.072411890.65
31/10/201233644.096343949145.802745.025412809.12
and I need to try and get it end up looking something like this.
ColumnValue31/12/201230/11/201231/10/2012
Turnover34737.3234612.1434612.14
Retention1676593634
Retention2474406394
NewClients1077791
AverageBill39.594448.499445.8027
AverageBillLastYear39.731456.072445.0254
LeaderContribution7816.8611890.6512809.12
Any help would be greatly appreciated. It does need to be dynamic as those dates change, but I can dynamicafy it once I get the jist of it. (Yes I know that's a made up word!)
Many thanks for any help
February 1, 2013 at 5:32 am
Have a look in Books Online for the Unpivot keyword, that's what you're looking for here, probably followed by a Pivot.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 1, 2013 at 6:26 am
Thank you Gila, that's exactly what I was needing to do.
SQL Code included below in case anyone else every stumbles on this and would like an example! There was apparently a need for all the columns to be of a compatible type, hence all those CASTs.
I'm sure this can be optimised too, but it's just the scraps to get the idea working!
SELECT
*
FROM
(
SELECT
ReportDate,
ColumnName,
Value
FROM
(
SELECT
CONVERT(NVARCHAR(10), Date, 103) AS ReportDate,
CAST(MonthlyTurnover AS NVARCHAR(15)) as MonthlyTurnover,
CAST(Retention1 AS NVARCHAR(15)) as Retention1,
CAST(Retention2 AS NVARCHAR(15)) as Retention2,
CAST(NewClients AS NVARCHAR(15)) AS NewClients,
CAST(AverageBill AS NVARCHAR(15)) AS AverageBill,
CAST(AverageBillLastYear AS NVARCHAR(15)) AS AverageBillLastYear,
CAST((SELECT SUM(Takings) FROM tablec c WHERE c.MemberId = b.MemberID AND DATEPART(MONTH, c.date) = DATEPART(MONTH, a.date) AND DATEPART(YEAR, c.date) = DATEPART(YEAR, a.date)) AS NVARCHAR(15)) AS LeaderContribution
FROM
tablea a
LEFT OUTER JOIN
tableb b ON a.Id = b.ID
INNER JOIN
tabled d ON a.id = d.id
where
a.Id = 54
and datepart(YEAR, Date) = 2012
) p
UNPIVOT
(
Value for ColumnName IN (MonthlyTurnover, Retention1,Retention2, NewClients, AverageBill, AverageBillLastYear, LeaderContribution)
) AS upvt
) p2
PIVOT (MAX(Value) FOR ReportDate IN([31/01/2012],[29/02/2012],[31/03/2012],[30/04/2012],[31/05/2012],[30/06/2012],[31/07/2012],[31/08/2012],[30/09/2012],[31/10/2012],[30/11/2012],[31/12/2012])) AS Pvt ORDER BY 1
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply