A technical blog about my projects, challenges, and discoveries in the world of data warehousing using SQL Server, Power BI Desktop, DevExpress, and more.

Tuesday, March 30, 2010

Pivoting Rows to Columns - Part 2

The last post looked at how to pivot rows to columns using multiple joins to the same table. In this post I will explain how to pivot using multiple subqueries in the SELECT clause of the query.

I will also be using a special "view" object in the example solutions. Views are essentially the results of queries that have been saved into the database in such a way that you can use the sets of results just as you would use a table. The view I'm going to use is called Shelby.VIEW_SHELBY_CUSTINFO_ALL. This is a view of the custom information collected on the Custom Tabs of the GlobaFILE module in Shelby v.5. Each row of this view represents one custom value for one name entry in the NANames module.

Here is an example of results from the Shelby.VIEW_SHELBY_CUSTINFO_ALL view:

select * from Shelby.VIEW_SHELBY_CUSTINFO_ALL


Now, let's limit this down to just the Spiritual Gifts tab and the Teacher check box option on that tab:

select * from Shelby.VIEW_SHELBY_CUSTINFO_ALL as CustomInfo where CustomInfo.TabName = 'Spiritual Gifts' and CustomInfo.FieldName = 'Teacher'


So far, so good. Bear with me just a moment as we follow this train of thought two steps further. Let's further limit this down to the value of the Teacher field for just one person. For this example, I will limit it down to the person with a NameCounter value of 8:

select * from Shelby.VIEW_SHELBY_CUSTINFO_ALL as CustomInfo where CustomInfo.TabName = 'Spiritual Gifts' and CustomInfo.FieldName = 'Teacher' and CustomInfo.NameCounter = 8


Now, one last refinement before we talk about how to use this info in a subquery. Let's just get the one column of information we really want out of this view, the actual "true/false" value of the field.

select CustomInfo.Value_c from Shelby.VIEW_SHELBY_CUSTINFO_ALL as CustomInfo where CustomInfo.TabName = 'Spiritual Gifts' and CustomInfo.FieldName = 'Teacher' and CustomInfo.NameCounter = 8



This does not look like much at all, but it is precisely what we need to know about the custom value of the "Teacher" field for one person. (The negative one represents "true," meaning that this person has the check box checked on his or her record.)

Now we can look at how to meld this information as a subquery into a main query that pulls the main name information out of the database.

In case you have not used subqueries in the SELECT clause of a query before, here is a run-down of the basic principles:
  • A subquery uses the same syntax as a regular query.
  • A subquery can have its own SELECT, FROM, WHERE, GROUP BY, and HAVING clauses, just like a regular query.
  • A subquery that is returning a value to a SELECT column must return exactly one value each time it executes: one column and one row only.
  • A subquery may be correlated to the primary query by using one or more columns from tables in the primary query as part of conditions in the WHERE clause of the subquery.
What we need to do it correlate the query we wrote above with another query that pull name information out of the database. Note the bold text in the following query, which shows how the correlation is happening:

select
n.NameCounter,
n.FirstMiddle,
n.LastName,
SpiritualGift_Teacher = (select CustomInfo.Value_c from Shelby.VIEW_SHELBY_CUSTINFO_ALL as CustomInfo where
CustomInfo.TabName = 'Spiritual Gifts' and CustomInfo.FieldName = 'Teacher' and CustomInfo.NameCounter = n.NameCounter)
from
Shelby.NANames as n



What is happening is that for each row of results the subquery executes, substituting the value of the NameCounter from NANames on that row for the WHERE condition in the subquery. Once we have one subquery added, it is quite easy to add more to pull additional custom values into the results:

select
n.NameCounter,
n.FirstMiddle,
n.LastName,
SpiritualGift_Teacher = (select CustomInfo.Value_c from Shelby.VIEW_SHELBY_CUSTINFO_ALL as CustomInfo where
CustomInfo.TabName = 'Spiritual Gifts' and CustomInfo.FieldName = 'Teacher' and CustomInfo.NameCounter = n.NameCounter),
SpiritualGift_Encouragement = (select CustomInfo.Value_c from Shelby.VIEW_SHELBY_CUSTINFO_ALL as CustomInfo where
CustomInfo.TabName = 'Spiritual Gifts' and CustomInfo.FieldName = 'Encouragement' and CustomInfo.NameCounter = n.NameCounter),
SpiritualGift_Service = (select CustomInfo.Value_c from Shelby.VIEW_SHELBY_CUSTINFO_ALL as CustomInfo where
CustomInfo.TabName = 'Spiritual Gifts' and CustomInfo.FieldName = 'Service' and CustomInfo.NameCounter = n.NameCounter)
from
Shelby.NANames as n


In case you would like a comparison, here is the phone number example from the previous post done with this subquery technique:

select
n.NameCounter,
n.FirstMiddle,
n.LastName,
MainPhone = (select p.PhoneNu from Shelby.NAPhones as p where p.PhoneCounter = 1 and p.NameCounter = n.NameCounter),
BusinessPhone = (select p.PhoneNu from Shelby.NAPhones as p where p.PhoneCounter = 2 and p.NameCounter = n.NameCounter)
from
Shelby.NANames as n

No comments:

Post a Comment

Followers