November 25, 2013 at 7:22 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.
IMPORTANT:
1. I really need it to be dynamic
2. the Services are not standardised names, they are numbered for illustration purposes only, they vary in naming convention.
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 25, 2013 at 7:52 am
You already have a post about this issue and I gave you a static example and a link with an article to make it dynamic. I could easily give you the answer but you won't learn as much as if you try by yourself.
What have you tried?
Original post: http://www.sqlservercentral.com/Forums/Topic1516851-392-1.aspx#bm1516861
November 25, 2013 at 8:15 am
Hey, yes I know, I tried but couldn't get it working dynamically so thought I would repost so that it would come to the top of the forum page to see if someone would be able to help me.
Plus with the right(service, 1) it kind of seems like the service must be called ServiceN, but the names vary dramatically.
Thanks
November 25, 2013 at 8:48 am
What exactly did you try? I'd rather help you to correct it than give you the whole answer to your problem. That way you can identify what you did wrong.
November 25, 2013 at 9:44 am
Hi,
Thanks, I managed to get it working dynamically.
Thank you for your original query which I used.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply