PIVOT UNPIVOT IN SQL2005

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » PIVOT UNPIVOT IN SQL2005

13 posts, Page 1 of 2

1 2 »»

PIVOT UNPIVOT IN SQL2005

Rate Topic

Display Mode

Topic Options

Author

Message

Shintu

Posted Monday, May 10, 2010 4:51 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, January 07, 2011 2:34 AM
Points: 35, Visits: 51

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

ChrisM@Work

Posted Monday, May 10, 2010 5:46 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:21 AM
Points: 5,602, Visits: 10,950

Try it without the CAST, change

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

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

Shintu

Posted Monday, May 10, 2010 6:44 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, January 07, 2011 2:34 AM
Points: 35, Visits: 51

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 (4 views, 154.71 KB)

Post #918942

ChrisM@Work

Posted Monday, May 10, 2010 6:57 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:21 AM
Points: 5,602, Visits: 10,950

mukherjee.subhro (5/10/2010)

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}

Post #918961

Shintu

Posted Monday, May 10, 2010 7:10 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, January 07, 2011 2:34 AM
Points: 35, Visits: 51

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

ChrisM@Work

Posted Monday, May 10, 2010 7:15 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:21 AM
Points: 5,602, Visits: 10,950

mukherjee.subhro (5/10/2010)

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}

Post #918978

ColdCoffee

Posted Monday, May 10, 2010 7:23 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:37 PM
Points: 2,248, Visits: 5,352

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

Shintu

Posted Monday, May 10, 2010 7:43 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, January 07, 2011 2:34 AM
Points: 35, Visits: 51

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

ChrisM@Work

Posted Monday, May 10, 2010 7:46 AM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:21 AM
Points: 5,602, Visits: 10,950

[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}

Post #919005

ColdCoffee

Posted Monday, May 10, 2010 7:51 AM

SSCrazy

Group: General Forum Members
Last Login: Wednesday, May 01, 2013 4:37 PM
Points: 2,248, Visits: 5,352

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 »

13 posts, Page 1 of 2

1 2 »»

Permissions