November 22, 2013 at 9:17 am
Hi All,
Wondered if someone could possibly help me with a pivot question.
I am trying to pivot some data as you would normally however I am trying to also group the pivot into three sub column groups too.
Basically the scenario is that I have three sub groups Budget, Person, RenewalDate for each Service (Service being the pivot point). So for each unique service I want to display the budget person and renewal date for each service by company.
I have created two tables to illustrate the base data and the required output.
Would someone please be kind enough to explain perhaps with an example of how I can do this? It would be also great if I could be shown how to do this dynamically too because the number of Services is unknown, i.e. it could be 4 Services or 20, each with three sub columns, budget, person and renewal date.
Please find code below. It should be quite self explanatory as to what I am trying to do. But please let me know if you need any more information.
Many thanks in advance.
create table #BaseData
(
Company nvarchar(100),
Person nvarchar(50),
[Service] nvarchar(100),
Budget int,
RenewalDate datetime
)
insert into #BaseData(Company, Person, [Service], Budget, RenewalDate)
select 'A', 'Siwel Noswod', 'Service1', 40, '2014-07-01 00:00:00.000'
insert into #BaseData(Company, Person, [Service], Budget, RenewalDate)
select 'A', 'Siwel Noswod', 'Service2', 60, '2013-11-01 00:00:00.000'
insert into #BaseData(Company, Person, [Service], Budget, RenewalDate)
select 'B', 'Joe Bloges', 'Service1', 50, '2014-06-01 00:00:00.000'
insert into #BaseData(Company, Person, [Service], Budget, RenewalDate)
select 'B', 'Siwel Noswod', 'Service2', 20, '2014-10-01 00:00:00.000'
insert into #BaseData(Company, Person, [Service], Budget, RenewalDate)
select 'C', 'Joe Bloges', 'Service1', 68, '2012-01-01 00:00:00.000'
insert into #BaseData(Company, Person, [Service], Budget, RenewalDate)
select 'C', 'Micheal', 'Service3', 11, '2012-09-01 00:00:00.000'
insert into #BaseData(Company, Person, [Service], Budget, RenewalDate)
select 'D', 'Joe Bloges', 'Service3', 88, '2010-03-01 00:00:00.000'
select * from #BaseData
drop table #BaseData
create table #RequiredOutput
(
Company nvarchar(100),
Service1Budget int,
Service1Person nvarchar(100),
Service1RenewalDate datetime,
Service2Budget int,
Service2Person nvarchar(100),
Service2RenewalDate datetime,
Service3Budget int,
Service3Person nvarchar(100),
Service3RenewalDate datetime
)
insert into #RequiredOutput(Company, Service1Budget, Service1Person, Service1RenewalDate, Service2Budget, Service2Person, Service2RenewalDate)
select 'A', 40, 'Siwel Noswod', '2014-07-01 00:00:00.000', 60, 'Siwel Noswod', '2013-11-01 00:00:00.000'
insert into #RequiredOutput(Company, Service1Budget, Service1Person, Service1RenewalDate, Service2Budget, Service2Person, Service2RenewalDate)
select 'B', 50, 'Joe Bloges', '2014-06-01 00:00:00.000', 20, 'Siwel Noswod', '2014-10-01 00:00:00.000'
insert into #RequiredOutput(Company, Service1Budget, Service1Person, Service1RenewalDate, Service3Budget, Service3Person, Service3RenewalDate)
select 'C', 68, 'Joe Bloges', '2012-01-01 00:00:00.000', 11, 'Michael', '2012-09-01 00:00:00.000'
insert into #RequiredOutput(Company, Service3Budget, Service3Person, Service3RenewalDate)
select 'D', 88, 'Joe Bloges', '2010-03-01 00:00:00.000'
select * from #RequiredOutput
drop table #RequiredOutput
November 22, 2013 at 9:43 am
I can help you with the basic code, however, you need to work on doing it dynamic as it involves more work and time which I don't have.
For dynamic code check the following article:
http://www.sqlservercentral.com/articles/Crosstab/65048/
SELECT Company,
MAX( CASE WHEN RIGHT(Service, 1) = 1 THEN Budget END) Service1Buget,
MAX( CASE WHEN RIGHT(Service, 1) = 1 THEN Person END) Service1Person,
MAX( CASE WHEN RIGHT(Service, 1) = 1 THEN RenewalDate END) Service1RenewalDate,
MAX( CASE WHEN RIGHT(Service, 1) = 2 THEN Budget END) Service2Buget,
MAX( CASE WHEN RIGHT(Service, 1) = 2 THEN Person END) Service2Person,
MAX( CASE WHEN RIGHT(Service, 1) = 2 THEN RenewalDate END) Service2RenewalDate,
MAX( CASE WHEN RIGHT(Service, 1) = 3 THEN Budget END) Service3Buget,
MAX( CASE WHEN RIGHT(Service, 1) = 3 THEN Person END) Service3Person,
MAX( CASE WHEN RIGHT(Service, 1) = 3 THEN RenewalDate END) Service3RenewalDate
FROM #BaseData
GROUP BY Company
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply