PIVOT UNPIVOT IN SQL2005

Question

Post reply

PIVOT UNPIVOT IN SQL2005

Shintu

SSC Eights!

Points: 803
More actions
May 10, 2010 at 4:51 am

#220894

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).
[font="Arial Black"]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?
[/font]

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

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

Shintu SSC Eights! Points: 803 More actions · Answer 2

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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

Shintu SSC Eights! Points: 803 More actions · Answer 4

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

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!

Shintu SSC Eights! Points: 803 More actions · Answer 7

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 9

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!

Shintu SSC Eights! Points: 803 More actions · Answer 10

Thank U so so much Cold Coffee,And very sorry that I am responding today as I had left my office at that moment.

May I know your name please, as U can guess I am subhro from India.

Can u just explain what is the idea behind this query you have send?

Thanks

Subhro

Shintu SSC Eights! Points: 803 More actions · Answer 11

Shintu

SSC Eights!

Points: 803

May 10, 2010 at 10:37 pm

#1165330

Thanks for your assistance

Cheers

Subhro

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 12

Subhro,

There is a REPLACE operation followed by the CAST in my code.

REPLACE will replace a set of characters , with a replacement value , on the string in which you want a part of it to be replaced. For syntax, kindly refer to the link i provided in the previous post!

In our case, we dont need cnt from the OrderYear column.

So, we used REPLACE('cnt2007', 'cnt', '') ;

This implies, in the string 'cnt2007', if u find 'cnt' in it, replace it with '' (a empty string). Thus, at the end of this code, your new string will be '2007'; As we need that to be INT, we cast '2007' to INT.

Hope this information helps you! If you further need assistance , let us know here!

I am Prasanna, from India! Glad to help you and meet you in the forums!

Cheers!