Using the PIVOT function

15 pts.
Tags:
PIVOT
SQL Server 2008
SQL Server 2008 Reporting Services
SQL Server Reporting Services
Hi there,

I'm pretty new to SQL and I'm trying to use the PIVOT function in Sequel Server 2008 R2. I've followed a whole bunch of examples and tutorials but just can't quite get my syntax right.

I have a database of applications. Fields include ApplicationID, Status Description and StatusID, and the date each status was reached. I was to create a cross tab report which shows each status and the date it was reached on one row so that I can operate a DateDiff between the stages.

I cannot for the life of me figure out what is wrong with this query:

SELECT ApplicationID, DateReached, [1], [2], [3], [4], [5], [6], [7] FROMĀ 

(SELECT ApplicationID, DateReached, StatusID FROM vw_ApplicationsReceived) as SourceData

PIVOT (MAX(DateReached) FOR StatusID IN [1], [2], [3], [4], [5], [6], [7]) AS PivotTable

Please could someone tell me whether I'm heading in the right direction? If so, what about this query is invalid?

Many thanks,

Christina



Software/Hardware used:
SQL Server 2008 R2

Answer Wiki

Thanks. We'll let you know when a new response is added.

hi,
i have a sample for pivot, maybe it is helpful for you.

script as below,

create table #tb(name varchar(10) , course varchar(10) , score int)
insert into #tb values(‘zs’ , ‘english’ , 74)
insert into #tb values(‘zs’ , ‘math’ , 83)
insert into #tb values(‘zs’ , ‘physics’ , 93)
insert into #tb values(‘ls’ , ‘english’ , 74)
insert into #tb values(‘ls’ , ‘math’ , 84)
insert into #tb values(‘ls’ , ‘physics’ , 94)

select * from #tb a pivot (max(score) for course in (english,math,physics)) b
———————————————
result,
name english math physics
ls 74 84 94
zs 74 83 93

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Can you post the error message you are getting ?
    69,510 pointsBadges:
    report
  • carlosdl
    I don't have a SQL Server database at hand to try right now, but at first look, I would say that the pivoted columns should be enclosed in parenthesis.
    PIVOT (MAX(DateReached) FOR StatusID IN ([1], [2], [3], [4], [5], [6], [7])) AS 
    69,510 pointsBadges:
    report
  • carlosdl
    BTW, it is SQL Server, not "Sequel" Server.
    69,510 pointsBadges:
    report
  • ChristinaSym
    Hey there, Thanks for all your help, guys, it's much appreciated. I went with my original query in the end, with the following edit: SELECT ApplicationID, [1], [2], [3], [4], [5], [6], [7], [8], DATEDIFF(day,[1],[2]) AS 'Duration', DATEDIFF(day,[1],[7]) AS 'Duration1' FROM (SELECT ApplicationID, DateReached, StatusID FROM vw_ApplicationsReceived) AS SourceData PIVOT (MAX(SourceData.DateReached) FOR StatusID IN ([1], [2], [3], [4], [5], [6], [7], [8])) AS PivotTable All the best, Christina
    15 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following