Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Pivot question with multiple columns and grouping Expand / Collapse
Author
Message
Posted Friday, November 22, 2013 9:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 24, 2014 6:18 AM
Points: 25, Visits: 87
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

Post #1516851
Posted Friday, November 22, 2013 9:43 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:00 PM
Points: 2,763, Visits: 5,905
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




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1516861
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse