Convert rows to columns using dynamic PIVOT table

Question

Post reply

Convert rows to columns using dynamic PIVOT table

vecerda

SSCrazy

Points: 2532
More actions
February 3, 2015 at 7:12 am

#314210

Hello guys,
I know this should be probably simple, but I cannot figure out what I am missing:
I have this query:
SELECT TOP (100) PERCENT dbo.Filteredfs_franchise.fs_franchiseid AS FranchiseId, dbo.Filteredfs_franchise.fs_brandidname AS Brand,
dbo.Filteredfs_franchise.fs_franchisetypename AS [Franchise Type], dbo.Filteredfs_franchise.fs_franchisenumber AS [Franchise Number],
dbo.Filteredfs_franchise.fs_transactiontypename AS [Transaction Type], dbo.Filteredfs_franchise.fs_franchisestatusname AS [Status Code],
dbo.Filteredfs_franchisee.fs_franchiseeid AS FranchiseeID, dbo.Filteredfs_franchisee.fs_accountidname AS [Franchisee Name Entity],
dbo.Filteredfs_franchisee.fs_contactidname AS [Franchisee Name Individual], CAST(dbo.Filteredfs_franchisee.fs_franchiseownership AS NUMERIC(10, 2))
AS [Percentage of Ownership]
FROM dbo.Filteredfs_franchise LEFT OUTER JOIN
dbo.Filteredfs_franchisee ON dbo.Filteredfs_franchise.fs_franchiseid = dbo.Filteredfs_franchisee.fs_franchiseid
WHERE (dbo.Filteredfs_franchise.fs_brandidname LIKE '%Moe%') AND (dbo.Filteredfs_franchise.statecodename = 'Active') AND
(dbo.Filteredfs_franchise.fs_franchisestatus IN ('8', '9', '16', '17', '18', '19', '20', '21', '22', '24')) AND (dbo.Filteredfs_franchisee.statecodename = 'Active')
ORDER BY brand, [Franchise Type], CONVERT(INT, dbo.Filteredfs_franchise.fs_franchisenumber)
I need to pivot this so I can get one row per franchiseID and multiple columns for [Franchisee Name Entity] and [Franchise Name Individual]. Each [Franchisee Name Entity] and [Franchise Name Individual] has associated percentage of ownership.
This has to be dynamic, because each FranchiseID can have anywhere from 1 to 12 respective owners and those can be any combination of of Entity and Individual. Please, see the attached example for Franchise Number 129 (that one would have 6 additional columns because there are 3 Individual owners with 1 respective Percentage of ownership).
The question is how do I PIVOT and preserve the percentage of ownership?
Any help would be greatly appreciated,
Thanks,
Petr

Viewing 4 posts - 1 through 3 (of 3 total)

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

jphillips 46546 SSC-Addicted Points: 439 More actions · Answer 1

You might want to check out Aaron Betrand's article on dynamic PIVOT.

http://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/

James Phillips
Sr. Consultant
Pragmatic Works

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

It's easy once you've worked through your first example. Here's another article written by Jeff Moden:

http://www.sqlservercentral.com/articles/Crosstab/65048/[/url]

A couple of minor changes to your query which may make the job easier:

SELECT -- TOP (100) PERCENT unnecessary

f.fs_franchiseid AS FranchiseId,

f.fs_brandidname AS Brand,

f.fs_franchisetypename AS [Franchise Type],

f.fs_franchisenumber AS [Franchise Number],

f.fs_transactiontypename AS [Transaction Type],

f.fs_franchisestatusname AS [Status Code],

e.fs_franchiseeid AS FranchiseeID,

e.fs_accountidname AS [Franchisee Name Entity],

e.fs_contactidname AS [Franchisee Name Individual],

CAST(e.fs_franchiseownership AS NUMERIC(10, 2)) AS [Percentage of Ownership]

FROM dbo.Filteredfs_franchise f

INNER JOIN dbo.Filteredfs_franchisee e

ON f.fs_franchiseid = e.fs_franchiseid

WHERE (f.fs_brandidname LIKE '%Moe%')

AND (f.statecodename = 'Active')

AND (f.fs_franchisestatus IN ('8', '9', '16', '17', '18', '19', '20', '21', '22', '24'))

AND (e.statecodename = 'Active') -- <-- this converts the LEFT JOIN into an INNER JOIN

ORDER BY brand, [Franchise Type], CONVERT(INT, f.fs_franchisenumber)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

vecerda SSCrazy Points: 2532 More actions · Answer 3

There are so many pieces to that article that I am not really which applies to my situation.

Can you, please, point to the part of the article that applies in this scenario?

thanks for your help,

Petr