Pivot table query - making it dynamic

Question

Pivot table query - making it dynamic

terry999

SSCertifiable

Points: 5036
More actions
November 17, 2011 at 7:08 am

#250768

Hi
I have a simple Pivot table query that lists customer services, this is used for to make reporting easier for my users.
CustID SvcName
1 CarWash
1 Service
2 CarWash
2 Overhaul
CustID Carwash Service Overhaul
1 1 1 0
2 1 0 1
I'm trying to get around the problem of when the syntax in SQL where you have to explicitly define the SvcName in the Pivot TSQL. I.E I cannot use *
SELECT Custid , ISNULL([Carwash],0) as [Carwash] etc etc
FROM
(select Custid , SvcName, 1 as HasSvc from tblservices as tS
inner join tblServiceDefaults as tDes
on tS.svcid = tDes.svcid) AS SourceTable
PIVOT
(
MAX (HasSvc)
FOR SvcName IN ([Carwash], [Service], [Overhaul])
) AS PivotTable;
This means if I add an extra Service I have to add the new service to the Query and recompile.
The only way I can see this to achieve this is to have
1. trigger on tblServiceDefaults (the list of Services) to fire an SP which dynamically re-creates the view when a service is added/updated
I then would have to somehow recompile any view that uses this view. Also the normal user who has limited rights would need rights to alter this view.
Is there a better way.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

This isn't completely dynamic, but close.

Instead of using SvcName, use a key/identity (1,2,3,4,5 ....) write the pivot to reference the key and have a set # that is more than what you have now. Then later join back to the Svc table to get the name.

Again, it's not dynamic, but if you only have 5 SvcNames currently, and you write the query to use up to 10, it buys you some time.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

drew.allen SSC Guru Points: 76988 More actions · Answer 2

You're probably better off leaving the pivot to your reporting software. You don't provide enough details to say for sure.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

terry999 SSCertifiable Points: 5036 More actions · Answer 3

Thanks Jason. I could do this becuase for every svcname there is a contious SVCID which is an Identity field. So I could add say 10 onto the last svcid I have

When I join back to the list of services(tblservicedfaults) I cannot see how I will replace the column headers which are now SVCIDs to SVCNames?

My users get this stuff in Excel so want the coulm headers to be service names?

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 4

terryshamir (11/17/2011)
Thanks Jason. I could do this becuase for every svcname there is a contious SVCID which is an Identity field. So I could add say 10 onto the last svcid I have
When I join back to the list of services(tblservicedfaults) I cannot see how I will replace the column headers which are now SVCIDs to SVCNames?
My users get this stuff in Excel so want the coulm headers to be service names?

Sounds like in one way or another you're going to need dynamic sql, or move the pivot into the front end as suggested.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

terry999 SSCertifiable Points: 5036 More actions · Answer 5

You are correct, this is really formatting so should be done by data presenter.

The reporting software is Excel. the people using do not have the skills (neither do I or do I want to learn) to do a pivot table but most importantly they will mix up excel spreadsheets and I'll end up fixing stuff all the time.

There is a clear demarcation line - I do not know Excel makes my life easier.

terry999 SSCertifiable Points: 5036 More actions · Answer 6

Thanks

But your idea is a good one, something I'm going to file away.

I've just written the SP that generates the view.

Its just any view that calls it must be also recompiled when a new service is added and I'm not sure sp_recompile will do this.

drew.allen SSC Guru Points: 76988 More actions · Answer 7

terryshamir (11/17/2011)
The reporting software is Excel. the people using do not have the skills (neither do I or do I want to learn) to do a pivot table but most importantly they will mix up excel spreadsheets and I'll end up fixing stuff all the time.

SSRS and many other reporting tools can export to Excel. Excel is a good reporting tool when using cubes as your back end, but is pretty poor for reporting on relational data.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA