Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


PIVOT UNPIVOT IN SQL2005


PIVOT UNPIVOT IN SQL2005

Author
Message
Shintu
Shintu
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 64
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?


ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9005 Visits: 19028
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
Shintu
Shintu
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 64
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
Attachments
testdb.JPG (10 views, 154.00 KB)
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9005 Visits: 19028
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
Shintu
Shintu
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 64
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
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9005 Visits: 19028
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
ColdCoffee
ColdCoffee
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2317 Visits: 5545
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
Shintu
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 64
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
Attachments
testyCoffey.JPG (12 views, 143.00 KB)
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 9005 Visits: 19028
[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
ColdCoffee
ColdCoffee
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2317 Visits: 5545
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search