Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

PIVOT UNPIVOT IN SQL2005 Expand / Collapse
Author
Message
Posted Monday, May 10, 2010 4:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 17, 2014 11:24 PM
Points: 35, Visits: 53
Hello !
Guys , I am placing a problem in front of you; Please go through that. It is in the Exercise in a familiar T-SQL book.
--->Run the following code to create and populate the EmpYearOrders table:


USE tempdb;

IF OBJECT_ID('dbo.EmpYearOrders', 'U') IS NOT NULL DROP TABLE dbo.EmpYearOrders;

SELECT empid, [2007] AS cnt2007, [2008] AS cnt2008, [2009] AS cnt2009
INTO dbo.EmpYearOrders
FROM (SELECT empid, YEAR(orderdate) AS orderyear
FROM dbo.Orders) AS D
PIVOT(COUNT(orderyear)
FOR orderyear IN([2007], [2008], [2009])) AS P;

SELECT * FROM dbo.EmpYearOrders;

Output:

empid cnt2007 cnt2008 cnt2009
----------- ----------- ----------- -----------
1 1 1 1
2 1 2 1
3 2 0 2

Write a query against the EmpYearOrders table that unpivots the data, returning a row for each employee and order year with the number of orders. Exclude rows where the number of orders is 0 (in our example, employee 3 in year 2008).

NOW

My question is I have made the query of UnPivoting as


SELECT empid,CAST(orderyear AS INT)AS orderyear,numorders
FROM dbo.EmpYearOrders
UNPIVOT(numorders FOR orderyear IN (cnt2007,cnt2008,cnt2009)) AS P
WHERE numorders<>0;

IT is giving an error as --->> Incorrect syntax near 'CAST', expected 'AS'.

Can any one help me to indicate what's wrong in the query and also provide the solution with the explanation what is to be improved and why?

Post #918906
Posted Monday, May 10, 2010 5:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:52 AM
Points: 7,123, Visits: 13,497
Try it without the CAST, change

SELECT empid,CAST(orderyear AS INT)AS orderyear,numorders

to

SELECT empid, orderyear, numorders



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #918917
Posted Monday, May 10, 2010 6:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 17, 2014 11:24 PM
Points: 35, Visits: 53
Hello Sir

Please look at the image file that I have send with this reply,I have run as per your suggestion removing the typecast and it shows in the Result section of the studio the orderyear field have output like cnt2007 .... and all; the 'cnt' part should be removed, for that I have used the Typecast , but I think the type casting is wrong , can you please give me idea related to proper typecasting?

Thanks

Subhro


  Post Attachments 
testdb.JPG (5 views, 154.71 KB)
Post #918942
Posted Monday, May 10, 2010 6:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:52 AM
Points: 7,123, Visits: 13,497
mukherjee.subhro (5/10/2010)
Hello Sir

Please look at the image file that I have send with this reply,I have run as per your suggestion removing the typecast and it shows in the Result section of the studio the orderyear field have output like cnt2007 .... and all; the 'cnt' part should be removed, for that I have used the Typecast , but I think the type casting is wrong , can you please give me idea related to proper typecasting?

Thanks

Subhro


I'm sorry, I'm not entirely sure what you mean - I think you mean stripping the first three alpha characters from orderyear. Please can you post the output as you would like to see it?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #918961
Posted Monday, May 10, 2010 7:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 17, 2014 11:24 PM
Points: 35, Visits: 53
Yea !
I mean what you have understood i.e. stripping the first 3 alphabet and taking only the numeric portion (i.e. year) . The output is in the image that I have send with that message.

Thanks

Subhro
Post #918975
Posted Monday, May 10, 2010 7:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:52 AM
Points: 7,123, Visits: 13,497
mukherjee.subhro (5/10/2010)
Yea !
I mean what you have understood i.e. stripping the first 3 alphabet and taking only the numeric portion (i.e. year) . The output is in the image that I have send with that message.

Thanks

Subhro


Check out SUBSTRING() in BOL.

Is this part of your course specific to PIVOT / UNPIVOT?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #918978
Posted Monday, May 10, 2010 7:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:03 PM
Points: 2,262, Visits: 5,405
Subhro, you need to first strip the occurence "cnt" using string functions like "STUFF" or "REPLACE" or "SUBSTRING" and then type-cast. Here is a code that will produce the result you were wanting.

I strongly recommend you try to understand the code ; If not please tel us here, we will explain what my code does. As you are doing an exercise and self-leaning, i am pretty reluctant to post this code, but still, i dont want you to stuck up with a minor hiccup and thats why i am publishing the apt code :

Here's the code!
declare @table table
(empid int, cnt2007 int, cnt2008 int, cnt2009 int)
insert into @table
select 1, 1, 1, 1
union all
select 2, 1, 2 ,1
union all
select 3, 2, 0, 2

select empid, cast((replace([Year], 'cnt','')) as int) as orderyear, value as numorders From
( select * from @table ) UNPIVOT_TABLE
unpivot
(value for [Year] in (cnt2007,cnt2008,cnt2009)) UNPIVOT_handle
where value <> 0

For more on those string functions, Click on the following :

SUBSTRING

REPLACE

STUFF

Cheers!
Post #918984
Posted Monday, May 10, 2010 7:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 17, 2014 11:24 PM
Points: 35, Visits: 53
Hello ! ColdCoffee

Thanks for the attempt you have made but the out put is not what I want, I have send an image attachment with this message , there you will find the execution result for the query you have send, if you go to the Result section , under the orderyear field the O/P is coming like cnt2007,cnt2008.... like that;
I want to omit this cnt portion, if you have any suggestion then please sent me by tomorrow.

I think I have made it clear.

*** I have also a solution but I want to discuss it after I get any better answer from anyone.

Thanks once more.


Subhro


  Post Attachments 
testyCoffey.JPG (9 views, 144.00 KB)
Post #919003
Posted Monday, May 10, 2010 7:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:52 AM
Points: 7,123, Visits: 13,497
[quote]mukherjee.subhro (5/10/2010)
...the orderyear field the O/P is coming like cnt2007,cnt2008.... like that;
I want to omit this cnt portion, if you have any suggestion then please sent me by tomorrow.

I think I have made it clear... /quote]

Check out SUBSTRING() in BOL.

Is this part of your course specific to PIVOT / UNPIVOT?


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #919005
Posted Monday, May 10, 2010 7:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 9:03 PM
Points: 2,262, Visits: 5,405
Subhro, you probably have not used my edited code.. please run my code in my prevoius post (i have updated it) and check with your dsired result! THe first image u have attached (your expected output) exactly matches with my new code..

I am posting the code again for your convenience
declare @table table
(empid int, cnt2007 int, cnt2008 int, cnt2009 int)
insert into @table
select 1, 1, 1, 1
union all
select 2, 1, 2 ,1
union all
select 3, 2, 0, 2

select empid, cast((replace([Year], 'cnt','')) as int) as orderyear , value as numorders From
( select * from @table ) UNPIVOT_TABLE
unpivot
(value for [Year] in (cnt2007,cnt2008,cnt2009)) UNPIVOT_handle
where value <> 0

Check it out and tel me if thats right..

Cheers!
Post #919010
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse