InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 17:34:41 GMT20http://www.sqlservercentral.com/Forums/Topic918906-392-1.aspxSubhro, 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 [b]cnt[/b] 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!Mon, 10 May 2010 23:06:39 GMTColdCoffeehttp://www.sqlservercentral.com/Forums/Topic918906-392-1.aspxThanks for your assistanceCheersSubhroMon, 10 May 2010 22:37:44 GMTShintuhttp://www.sqlservercentral.com/Forums/Topic918906-392-1.aspxThank 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?ThanksSubhroMon, 10 May 2010 22:32:19 GMTShintuhttp://www.sqlservercentral.com/Forums/Topic918906-392-1.aspxSubhro, 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[code="sql"]declare @table table(empid int, cnt2007 int, cnt2008 int, cnt2009 int)insert into @tableselect 1, 1, 1, 1union allselect 2, 1, 2 ,1union allselect 3, 2, 0, 2select empid, cast((replace([Year], 'cnt','')) as int) as orderyear , value as numorders From ( select * from @table ) UNPIVOT_TABLEunpivot (value for [Year] in (cnt2007,cnt2008,cnt2009)) UNPIVOT_handlewhere value <> 0[/code]Check it out and tel me if thats right..Cheers!Mon, 10 May 2010 07:51:56 GMTColdCoffeehttp://www.sqlservercentral.com/Forums/Topic918906-392-1.aspx[quote][b]mukherjee.subhro (5/10/2010)[/b][hr]...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? Mon, 10 May 2010 07:46:33 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic918906-392-1.aspxHello ! 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.SubhroMon, 10 May 2010 07:43:46 GMTShintuhttp://www.sqlservercentral.com/Forums/Topic918906-392-1.aspxSubhro, 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![code="sql"]declare @table table(empid int, cnt2007 int, cnt2008 int, cnt2009 int)insert into @tableselect 1, 1, 1, 1union allselect 2, 1, 2 ,1union allselect 3, 2, 0, 2select empid, cast((replace([Year], 'cnt','')) as int) as orderyear, value as numorders From ( select * from @table ) UNPIVOT_TABLEunpivot (value for [Year] in (cnt2007,cnt2008,cnt2009)) UNPIVOT_handlewhere value <> 0[/code]For more on those string functions, Click on the following :[url=http://msdn.microsoft.com/en-us/library/ms187748(SQL.90).aspx]SUBSTRING[/url][url=http://msdn.microsoft.com/en-us/library/ms186862(SQL.90).aspx]REPLACE[/url][url=http://msdn.microsoft.com/en-us/library/ms188043(SQL.90).aspx]STUFF[/url]Cheers!Mon, 10 May 2010 07:23:01 GMTColdCoffeehttp://www.sqlservercentral.com/Forums/Topic918906-392-1.aspx[quote][b]mukherjee.subhro (5/10/2010)[/b][hr]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.ThanksSubhro[/quote]Check out SUBSTRING() in BOL.Is this part of your course specific to PIVOT / UNPIVOT?Mon, 10 May 2010 07:15:39 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic918906-392-1.aspxYea ! 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.ThanksSubhroMon, 10 May 2010 07:10:39 GMTShintuhttp://www.sqlservercentral.com/Forums/Topic918906-392-1.aspx[quote][b]mukherjee.subhro (5/10/2010)[/b][hr]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?ThanksSubhro[/quote]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?Mon, 10 May 2010 06:57:46 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic918906-392-1.aspxHello 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?ThanksSubhroMon, 10 May 2010 06:44:07 GMTShintuhttp://www.sqlservercentral.com/Forums/Topic918906-392-1.aspxTry it without the CAST, change[code="sql"]SELECT empid,CAST(orderyear AS INT)AS orderyear,numorders[/code]to[code="sql"]SELECT empid, orderyear, numorders[/code]Mon, 10 May 2010 05:46:54 GMTChrisM@Workhttp://www.sqlservercentral.com/Forums/Topic918906-392-1.aspxHello ! 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 cnt2009INTO dbo.EmpYearOrdersFROM (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 12 1 2 13 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).[b][font="Arial Black"]NOWMy question is I have made the query of UnPivoting as SELECT empid,CAST(orderyear AS INT)AS orderyear,numordersFROM dbo.EmpYearOrders UNPIVOT(numorders FOR orderyear IN (cnt2007,cnt2008,cnt2009)) AS PWHERE 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][/b]Mon, 10 May 2010 04:51:01 GMTShintu