Cross-Tab Help?! - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Cross-Tab Help?!

Author

Topic

JMann8
Starting Member

4 Posts

Posted - 2004-10-01 : 15:58:43

I have two tables (PCAL_Calibrations & PCAL_Comments) I use the query below to retrieve the data I want from PCAL_Calibrations:

SELECT c.CalibrationID, c.CalibratedPersonID, { fn CONCAT(e.LastName + ', ', e.FirstName) } AS FullName,
{ fn CONCAT(em.LastName + ', ', em.FirstName) } AS SupFullName, d.DeptDescription,
c.CurrentPositionTitle, c.TimeInCurrentPosition, c.PromotionPlanned, c.Rating, c.CalibratorComments
FROM PCAL_Calibrations c, Employees e, Departments d, Employees em
WHERE(e.WWID = c.CalibratedPersonID)
AND em.WWID = e.Supervisor_ID
AND d.DeptID = c.DeptID
AND c.DeptID IN (Select DeptID from departments)
AND e.CalibrationLevel IS NOT NULL
AND c.CalibrationYear = 2004
ORDER BY FullName

The Data structure of PCAL_Comments is:
CommentsID, CalibrationID, CommentorID, Comments, DateCreated

There is a 1 to many relationship between PCAL_Calibrations & PCAL_Comments on CalibrationID.

I need the results returned by the query above and appended to that as columns I need the CommentorID, Comments, DateCreated from PCAL_Comments for every comment row in the table.

Ideally the resulting columns will look like this if there was two rows in PCAL_comments with the same CalibrationID:
CalibrationID, CalibratedPersonID, FullName, SupFullName, DeptDescription, CurrentPositionTitle, TimeInCurrentPosition, PromotionPlanned, Rating, CalibratorComments, (DateCreated1, CommentorID1, Comments1,) (DateCreated2, CommentorID2, Comments2)

I have no idea how to begin to write this query... Any Ideas?

Thanks in advance for any help.
- Josh

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-10-01 : 17:47:44

It's too late on a friday to figure this out. You may want to do some searches on cross tab or pivot tables here at sqlteam to find some examples of how this can be accomplished.

This is very common request, so there should be tons of examples both in the articles online and in the forum.

-ec

JMann8
Starting Member

4 Posts

Posted - 2004-10-01 : 17:51:42

I have been searching for 3 days and have tried many "solutions" however none of them have worked just right for this situation. I'm not one to normally post questions. I can usually figure it out. But this seems truley beyond me. So Again, any help from people would be very much appreciated.

- Josh

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-10-01 : 23:14:03

What database are you using? The { fn .. } is not MS SQL. This is a SQL Server forum.

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2004-10-02 : 00:04:31

Those are ODBC extensions to SQL. Not pure TSQL, but I think it is something we can work around (it is just concatenation).

-ec

JMann8
Starting Member

4 Posts

Posted - 2004-10-04 : 15:03:25

I am attempting to use the crosstab stored procedure I found on this site: http://www.sqlteam.com/item.asp?ItemID=2955

I have modified my requirements slightly so that I can use the crosstab procedure. However, I am now facing the following problem:

This is the column result set I am now receiving:
CalibrationID, CalibratedPersonID, FullName, SupFullName, DeptDescription, CurrentPositionTitle, TimeInCurrentPosition, PromotionPlanned, Rating, 26172, 67548, 191253, 701048752

The numbered columns represent the crosstabbed CommentorID and the data appearing within the rows of those numbered columns should be the corresponding comments (PCAL_comments.Comments).

This is the query I am passing the stored proc:
EXECUTE crosstab 'SELECT DISTINCT c.CalibrationID, c.CalibratedPersonID,
{ fn CONCAT(e.LastName + '', '', e.FirstName) } AS FullName,
{ fn CONCAT(em.LastName + '', '', em.FirstName) } AS SupFullName, d.DeptDescription, c.CurrentPositionTitle, c.TimeInCurrentPosition,
c.PromotionPlanned, c.Rating FROM
PCAL_Calibrations c, Employees e, Departments d, Employees em, PCAL_Comments cm
WHERE e.WWID = c.CalibratedPersonID
AND c.CalibrationID = cm.CalibrationID
AND em.WWID = e.Supervisor_ID
AND d.DeptID = c.DeptID
AND e.CalibrationLevel IS NOT NULL
AND c.CalibrationYear = 2004
GROUP BY c.CalibrationID, c.CalibratedPersonID, e.LastName, e.FirstName,
em.LastName, em.FirstName, d.DeptDescription, c.CurrentPositionTitle, c.TimeInCurrentPosition,
c.PromotionPlanned, c.Rating, cm.comments, cm.commentorid ORDER BY FullName','(cm.Comments)','CommentorID','PCAL_Comments'

The issue is that I am now recieving duplicate rows for each calibration with multiple comments.
Example: If PCAL_Comments has multiple rows (comments) associated with a CalibrationID in PCAL_Calibrations, I get multiple rows for each comment. I need all the comments to appear on the same single row which it coresponds to by CalibrationID.


It currently looks like:

CalibrationID| -->| 26172| 67548| 191253| 701048752| 
17           |    |      | sdf..|       |          |
18           |    |      |      | dssd..|          |
18           |    | dsf..|      |       |          | 

I need it to look like:

CalibrationID| -->| 26172| 67548| 191253| 701048752|
17           |    |      | sdf..|       |          |
18           |    | dsf..|      | dssd..|          |

I hope this makes enough sense for someone to understand and help me with.

Thanks,
Josh

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-10-05 : 08:29:10

The reason you're getting "duplicates" (they're not really dupes) is that you are grouping on more than CalibrationID. In order to have the cross tab group the way you describe, you must group by CalibrationID ONLY. The other columns you are currently grouping by must either be removed entirely from the SELECT clause, or be put inside a Min() or Max() function.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources