New to pivots and need an example

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

New to pivots and need an example

Author

Topic

waveform
Yak Posting Veteran

93 Posts

Posted - 2011-12-11 : 12:00:44

Coming across the word "pivot" for the first time, at least in databases. I've been reading this page: [url]http://www.sqlsnippets.com/en/topic-12180.html[/url] and a bit bewildered. Could I ask someone to solve this example for me to help me along?

This is for "Distributing". Say there's a table of Employees, and each is tagged with one or more Roles via a many-to-many interim table:


+------------+      +------------+     
| Employees  |      | EmplRoles  |     +----------+
+------------+      + -----------+     | Roles    |
| EmployeeID |  <-  | EmployeeId |     +----------+
| FirstName  |      | RoleId     |  -> | RoleId   |
| LastName   |      +------------+     | RoleName |
+------------+			       +----------+

I want to display a list of Employees, one per line, and beside each person's name show the roles to which they're assigned. Like so:

Harry Jones: Administration, Bookkeeping, Blog Editor.
Julie Smith: Market Research, Blog Editor.

I could do a simple INNER JOIN of course, and use code logic to display the right record in the right place. However, if I want to just get a single resultset row for each employee, with each role as separate columns in their rows, how would I do that using this "Distribution" method of pivots?
http://www.sqlsnippets.com/en/topic-12196.html

Even better - is there way to concatenate those Role records into a single, comma-separated column? So I would have:


FirstName  LastName  Roles
---------  --------  ----------------------------------------------
"Harry"    "Jones"   "Administration, Bookkeeping, Blog Editor"
"Julie"    "Smith"   "Market Research, Blog Editor"

Or is that asking too much? :)

Many thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-11 : 12:09:04

concatenating is much simpler.

see below


SELECT e.FirstName, e.LastName,
STUFF((SELECT ',' + r.RoleName 
       FROM Roles r
       INNER JOIN EmplRoles er
       ON er.RoleId = r.RoleId 
       WHERE er.EmployeeId = e.EmployeeId 
       ORDER BY r.RoleId 
       FOR XML PATH('')),1,1,'') AS [Roles]
FROM Employees e

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

waveform
Yak Posting Veteran

93 Posts

Posted - 2011-12-11 : 14:18:01

Just... wow! Thanks very much, works a charm. Used it on a course/event system just now, which has a table of available Courses and a table of Event times for when each course runs. Tried this:

SELECT c.CourseTitle, 
       STUFF((SELECT ',' + CONVERT(VARCHAR(20), e.StartDateUTC, 100) FROM ev_CourseEvent e
              WHERE e.CourseId = c.CourseId AND e.StartDateUTC > GETUTCDATE() ORDER BY e.StartDateUTC
              FOR XML PATH('')),1,1,'') AS StartTimes
FROM ev_Course AS c

Ended up with this:


CourseTitle |  StartTimes
------------+------------------------------------------
Course 1    |  Jul 26 2012 12:00AM
Course 2    |  NULL
Course 3    |  Mar  4 2012 12:00AM,Aug  8 2012 12:00AM
etc.

Awesome! And it makes UI paging so much easier. I don't quite understand the FOR XML PATH bit, I'll look that up, seems very useful. :)

Next question... What's the best way of ordering the rows of Courses by the most recent event time of each Course? Eg. in that list, "Course 3" should be first, followed by "Course 1". I have done it this way, but doing another sub-query seems excessive.


SELECT c.CourseTitle, 
       STUFF((SELECT ',' + CONVERT(VARCHAR(20), e.StartDateUTC, 100) 
              FROM ev_CourseEvent e WHERE e.CourseId = c.CourseId AND e.StartDateUTC > GETUTCDATE() 
              ORDER BY e.StartDateUTC FOR XML PATH('')),1,1,'') AS StartTimes 
FROM ev_Course AS c
ORDER BY (SELECT MAX(e2.StartDateUTC) 
          FROM ev_CourseEvent e2 
          WHERE e2.CourseId = c.CourseId AND e2.StartDateUTC > GETUTCDATE()

Results in a nicely ordered list:


CourseTitle |  StartTimes
------------+------------------------------------------
Course 2    |  NULL
Course 3    |  Mar  4 2012 12:00AM,Aug  8 2012 12:00AM
Course 1    |  Jul 26 2012 12:00AM

But is there a better way to order it than a subquery, and how to exclude the NULLs (no upcoming event records)?

Exciting! :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-11 : 23:51:07

[code]
SELECT c.CourseTitle,
STUFF((SELECT ',' + CONVERT(VARCHAR(20), e.StartDateUTC, 100) FROM ev_CourseEvent e
WHERE e.CourseId = c.CourseId AND e.StartDateUTC > GETUTCDATE() ORDER BY e.StartDateUTC
FOR XML PATH('')),1,1,'') AS StartTimes
FROM ev_Course AS c
CROSS APPLY (SELECT MAX(StartDateUTC) AS MaxDate
FROM ev_CourseEvent
WHERE CourseId = c.CourseId
AND e.StartDateUTC > GETUTCDATE()
)l
ORDER BY l.MaxDate DESC
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-11 : 23:55:33

The FOX XML builds a xml doc dynamically using matching values returned by select appended by delimiter (in this case ,) in between. The node name is made '' to return the xml document with a dummy node which is why you get it as a delimited list. The STUFF part is to remove the extra delimiter which it puts at the start of the string. you can try this to get an idea of how FOR XML PATH works


SELECT e.StartDateUTC FROM ev_CourseEvent e
              WHERE e.StartDateUTC > GETUTCDATE() ORDER BY e.StartDateUTC
              FOR XML PATH('Dates')

SELECT e.StartDateUTC FROM ev_CourseEvent e
              WHERE e.CourseId = <put some value here>
              AND e.StartDateUTC > GETUTCDATE() 
              ORDER BY e.StartDateUTC
              FOR XML PATH('')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources