September 11, 2008 at 8:33 am
I have a very large table that stores all the information for an account, there is an account id. There is another table that stores all the status dates that an account can have.
AcctStatusDateStatusIdStatus
45906/15/0511No Decision
48309/28/0510Won
51803/18/0515On Hold - Other
51803/18/056Loss
52707/27/056Loss
53705/23/056Loss
54006/06/056Loss
55901/17/062Cancelled
57105/11/052Cancelled
58105/13/0511No Decision
61309/28/0511No Decision
61407/29/0511No Decision
I need to create a query that shows, by account all the possible statuses and when they occurred on one line, there is a predefined list of actual statuses that can happen, if not show null...
AcctCancelLoss On Hold - TeamOn Hold - OtherWinDecision
518Null 03/18/05Null 03/18/05Null Null
61406/06/085/3/20088/2/2008 Null 9/12/2008Null
Has anyone had experience doing something like this??? When I try subqueries it takes forever to run.
September 11, 2008 at 8:36 am
Have a look at the PIVOT keyword in SQL 2005. It may help with what you're trying to do.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 11, 2008 at 8:58 am
Search for "cross tab" as well. That's what this is.
September 11, 2008 at 11:29 am
I've tried the Pivot table and it doesn't seem to be working... can you comment on the following query?
Select BidId,
'Cancelled',
'In Progress',
'Loss',
'New',
'No Bid',
'Referred Out'
FROM (
Select bi.BidId, dbo.Status.Name, dbo.StatusComments.CommentDate
From Bids bi INNER JOIN
dbo.BidFormOwning ON bi.BidId = dbo.BidFormOwning.BidId INNER JOIN
dbo.BidRevision ON dbo.BidFormOwning.BidFormOwningId = dbo.BidRevision.BidFormOwningId INNER JOIN
dbo.StatusComments ON dbo.BidRevision.BidRevisionId = dbo.StatusComments.RevisionId INNER JOIN
dbo.SupportTeams ON dbo.BidFormOwning.SupportTeamId = dbo.SupportTeams.SupportTeamId INNER JOIN
dbo.Status ON dbo.StatusComments.StatusId = dbo.Status.StatusId
where dbo.BidFormOwning.SupportTeamId=2
and dbo.Status.Name in ('Cancelled',
'In Progress',
'Loss',
'New',
'No Bid',
'Referred Out')) as a
Pivot
(
Max(CommentDate)
FOR Name In ([Cancelled],
[In Progress],
[Loss],
[New],
[No Bid],
[Referred Out])) as b
order by BidId
September 11, 2008 at 11:59 am
Define 'doesn't seem to be working' please.
Throws an error?
Gives unexpected results?
Spawns a black hole?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 11, 2008 at 12:06 pm
This is the result I get................................
BidId(no column name)(no column name)(no column name)(no column name)(no column name)(no column name)
518CancelledIn ProgressLossNewNo BidReferred Out
527CancelledIn ProgressLossNewNo BidReferred Out
537CancelledIn ProgressLossNewNo BidReferred Out
540CancelledIn ProgressLossNewNo BidReferred Out
559CancelledIn ProgressLossNewNo BidReferred Out
571CancelledIn ProgressLossNewNo BidReferred Out
626CancelledIn ProgressLossNewNo BidReferred Out
September 11, 2008 at 3:06 pm
I have tried this over and over and can't find my error, any help would be appreciated
September 11, 2008 at 3:14 pm
You're so very close....:D
You're selecting string constants in your outer SELECT statement. Switch them to referring to columns of the same name instead.
In other words - this is the "top" of your posted query:
Select BidId,
'Cancelled',
'In Progress',
'Loss',
'New',
'No Bid',
'Referred Out'
Replace with:
Select BidId,
Cancelled,
[In Progress],
[Loss],
New,
[No Bid],
[Referred Out]
And you should get what you're looking for.....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 11, 2008 at 3:51 pm
Matt, you are a life saver! Was beating my head over it. Was the first time ever used Pivot and had copied the code from an "Expert" site. Thanks!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply