InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 22:04:30 GMT20http://www.sqlservercentral.com/Forums/Topic928203-149-1.aspxPLEASE GIVE ME SOLUTION FOR THE ABOVE BY ANY ONE THANKS IN ADVANCEFri, 08 Mar 2013 01:11:07 GMTkravitej9http://www.sqlservercentral.com/Forums/Topic928203-149-1.aspxHi thank for ur reply.I have another questionI need an auto decrement in oracle 11gFor example i am having a table as contactsand column name ad IDwhen i delete an row. the ID should derease by 1 automaticallycan u please explain with an exampleWed, 06 Mar 2013 00:14:47 GMTkravitej9http://www.sqlservercentral.com/Forums/Topic928203-149-1.aspx[quote][b]kravitej9 (1/29/2013)[/b][hr]Can u send me the query[/quote]This may help..[code="sql"]DECLARE @temp AS TABLE(v1 INT, v2 INT, v3 INT)INSERT INTO @tempSELECT 1,3, NULL UNION ALLSELECT 3,4,5 UNION ALLSELECT 4,2, NULL UNION ALLSELECT 3,4, NULL UNION ALLSELECT 2, NULL, NULLSELECT DISTINCTcp.*FROM@tempCROSS APPLY ( SELECT Newcol FROM (VALUES (v1), (v2), (v3)) X (Newcol) ) cp[/code]AndyTue, 29 Jan 2013 03:59:11 GMTAndy Hyslophttp://www.sqlservercentral.com/Forums/Topic928203-149-1.aspxCan u send me the queryTue, 29 Jan 2013 02:51:34 GMTkravitej9http://www.sqlservercentral.com/Forums/Topic928203-149-1.aspx[quote][b]kravitej9 (1/31/2012)[/b][hr]hi,i need a query for i have data as 1,3 3,4,5 4,2 3,4 2on writing query i need to get the ouput as 12345with out duplicateplease send me quickly[/quote]please create a new thread...this is an old post and unrelated to your question.....look up UNION in BOL...this may help youTue, 31 Jan 2012 05:23:22 GMTJ Livingston SQLhttp://www.sqlservercentral.com/Forums/Topic928203-149-1.aspxhi,i need a query for i have data as 1,3 3,4,5 4,2 3,4 2on writing query i need to get the ouput as 12345with out duplicateplease send me quicklyTue, 31 Jan 2012 04:40:18 GMTkravitej9http://www.sqlservercentral.com/Forums/Topic928203-149-1.aspxApologies for not supplying the data set up code, I will remember to do that in the future!I will try the code given above, and let you know.Thanks for your helpWed, 26 May 2010 08:04:25 GMTkevin_batchelorhttp://www.sqlservercentral.com/Forums/Topic928203-149-1.aspxI would also recommend you to go through the following article on how to post readily consumable data[url=http://www.sqlservercentral.com/articles/Best+Practices/61537/]CLICK HERE FOR FORUM POSTING ETIQUETTES - JEFF MODEN[/url]Hope the code above this post helped you! Tell us if it did the trick!C'est Pras!Wed, 26 May 2010 07:44:42 GMTColdCoffeehttp://www.sqlservercentral.com/Forums/Topic928203-149-1.aspxHi there, here is a piece of code that wll do what you wanted:As you are first time poster, i took the onus on my side to create DDLs for your request; going further, please provide full ready-to-use DDLs so that many volunteers here will give you tested code back :-)Now , sample data and table :[code="sql"]DECLARE @table TABLE ( [Name] varchar(15), [Order] varchar(15), [Month] varchar(15), [Detail] varchar(15), [Value] numeric(10,2), [Margin] numeric(10,2))insert into @table([Name], [Order] ,[Month] ,[Detail] ,[Value] ,[Margin]) select 'Fred' ,'Ord1', 'Month2', 'Ace', 1300, 400union all select 'Fred' ,'Ord2', 'Month2' ,'Long', 0 ,-15010union all select 'Fred' ,'Ord3', 'Month2' ,'Bing', 2270 ,309.09union all select 'Fred' ,'Ord4', 'Month1' ,'Stuff', 6471.6, 5383.81union all select 'Fred' ,'Ord4', 'Month2' ,'Stuff', 3275, -462.79union all select 'Fred' ,'Ord5', 'Month2' ,'Roof' ,4940 ,1140.72union all select 'Fred' ,'Ord6', 'Month2' ,'Wall' ,12545, 2110.37[/code]Now the code that will provide you the output:[code="sql"];with cte as( select [Name], [Order] ,[Month] ,[Detail] ,[Value] ,[Margin] , row_number() over(partition by [Order] order by [Order]) rn from @table ),only_one as( select [order] from cte group by [order] having count(*) = 1),multiple as( select * from cte where [order] in (select distinct [Order] from cte where rn > 1))select [Name], [Order] ,[Month] ,[Detail] ,[Value] ,[Margin] from ctewhere [order] in (select [order] from only_one)union all select t1.[Name], t1.[Order] ,t1.[Month] ,t1.[Detail] ,t1.[Value]-t2.[Value] [Value] , t1.[Margin] -t2.[Margin] [Margin] from multiple t1 cross join multiple t2where t1.rn = 2 and t2.rn = 1 [/code]Wed, 26 May 2010 07:42:46 GMTColdCoffeehttp://www.sqlservercentral.com/Forums/Topic928203-149-1.aspxHiI currently use the following statement:SELECT * FROM Month1_Month2_Diff_Detail ORDER BY Name, Order, Monthand receive the following back:Name Order Month Detail Value MarginFred Ord1 Month2 Ace 1300 400Fred Ord2 Month2 Long 0 -15010Fred Ord3 Month2 Bing 2270 309.09Fred Ord4 Month1 Stuff 6471.6 5383.81Fred Ord4 Month2 Stuff 3275 -462.79Fred Ord5 Month2 Roof 4940 1140.72Fred Ord6 Month2 Wall 12545 2110.37As you can see, there is only one Order row for each Order exceptOrd4, where there are two. How do I change the ouput so thatthe data for Ord4 looks like:Fred Ord4 Month2 Stuff -3196.6 -5846.6where the data for Ord4 and Month1 is subtracted from the data for Ord 4 and Month2? I have multiple Salesman also, soit needs to work for that too.I don't need to see the column month either, I had to show itso that the data could be seen.Any ideas?Wed, 26 May 2010 07:00:26 GMTkevin_batchelor