|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
separate columnsHi all
I have a tables like this.. fCode Number 1 10 2 -3 3 4 1 -2 2 8 I want to separate postivie and negative values for fCode. like this fCode PNum NNum 1 10 -2 2 8 -3 3 4 0 can I do that without using Union ??? thanks in advance If you will only have a max of 2 rows per fCode use this
create table #test(fCode int,Number int) insert into #test select 1,10 union all select 2,-3 union all select 3,4 union all select 1,-2 union all select 2,8 select fCode, max(number) pNum, case when min(number) =max(number) then 0 else min(number) end nNum from #test group by fcode http://sqlservercode.blogspot.com/ no,I have many rows..those were just sample numbers....
Show quote "SQL" <denis.g***@gmail.com> wrote in message news:1140192985.777594.56760@f14g2000cwb.googlegroups.com... > If you will only have a max of 2 rows per fCode use this > > create table #test(fCode int,Number int) > insert into #test > select 1,10 union all > select 2,-3 union all > select 3,4 union all > select 1,-2 union all > select 2,8 > > select fCode, max(number) pNum, > case when min(number) =max(number) then 0 else min(number) end nNum > from #test > group by fcode > > http://sqlservercode.blogspot.com/ > Can you post some more sample data with expected
result. Specifically what happens if there are many positive and negative numbers for a single fCode. Will this work for you... select t1.fCode,t1.Number as pNum,coalesce(t2.Number,0) as nNum from #Test t1 left outer join #Test t2 on t1.fCode=t2.fCode and t2.Number<0 where t1.Number>0 order by t1.fCode I want to summation of positive and negative numbers for every fCode.
<markc***@hotmail.com> wrote in message Show quote news:1140194104.028806.101860@z14g2000cwz.googlegroups.com... > > Can you post some more sample data with expected > result. Specifically what happens if there are many positive > and negative numbers for a single fCode. > > > Will this work for you... > > select t1.fCode,t1.Number as pNum,coalesce(t2.Number,0) as nNum > from #Test t1 > left outer join #Test t2 on t1.fCode=t2.fCode and t2.Number<0 > where t1.Number>0 > order by t1.fCode > Marc's code should do the trick if you just add in a sum.
select t1.fCode, sum(t1.Number) as pNum, sum(coalesce(t2.Number,0)) as nNum from #Test t1 left outer join #Test t2 on t1.fCode=t2.fCode and t2.Number<0 where t1.Number>0 order by t1.fCode Show quote "perspolis" <reza***@hotmail.com> wrote in message news:ew5Vbt%23MGHA.620@TK2MSFTNGP11.phx.gbl... > I want to summation of positive and negative numbers for every fCode. > <markc***@hotmail.com> wrote in message > news:1140194104.028806.101860@z14g2000cwz.googlegroups.com... > > > > Can you post some more sample data with expected > > result. Specifically what happens if there are many positive > > and negative numbers for a single fCode. > > > > > > Will this work for you... > > > > select t1.fCode,t1.Number as pNum,coalesce(t2.Number,0) as nNum > > from #Test t1 > > left outer join #Test t2 on t1.fCode=t2.fCode and t2.Number<0 > > where t1.Number>0 > > order by t1.fCode > > > > If you give more sample data and show how you want it displayed folks can
give a better answer. You need to know how it will display physically before you can write the logic to make it do so. What is the maximum amount of rows that you cna have per fCode, and how should it be displayed? Show quote "perspolis" <reza***@hotmail.com> wrote in message news:OV33q79MGHA.1028@TK2MSFTNGP11.phx.gbl... > no,I have many rows..those were just sample numbers.... > "SQL" <denis.g***@gmail.com> wrote in message > news:1140192985.777594.56760@f14g2000cwb.googlegroups.com... > > If you will only have a max of 2 rows per fCode use this > > > > create table #test(fCode int,Number int) > > insert into #test > > select 1,10 union all > > select 2,-3 union all > > select 3,4 union all > > select 1,-2 union all > > select 2,8 > > > > select fCode, max(number) pNum, > > case when min(number) =max(number) then 0 else min(number) end nNum > > from #test > > group by fcode > > > > http://sqlservercode.blogspot.com/ > > > > SELECT DISTINCT t1.fcode, ISNULL(t2.Number,0) AS "PNumber",
ISNULL(t3.Number,0) AS "NNumber" FROM [YourTable] t1 LEFT JOIN ( SELECT fcode, Number FROM [YourTable] WHERE Number >= 0 ) AS t2 ON t1.fcode = t2. fcode LEFT JOIN ( SELECT fcode, Number FROM [YourTable] WHERE Number < 0 ) AS t3 ON t1.fcode = t3.fcode ORDER BY t1.fcode Show quote "perspolis" wrote: > Hi all > I have a tables like this.. > fCode Number > 1 10 > 2 -3 > 3 4 > 1 -2 > 2 8 > I want to separate postivie and negative values for fCode. > like this > fCode PNum NNum > 1 10 -2 > 2 8 -3 > 3 4 0 > can I do that without using Union ??? > thanks in advance > > > Thanks all:)
Show quote "perspolis" <reza***@hotmail.com> wrote in message news:eTkeOy9MGHA.1124@TK2MSFTNGP10.phx.gbl... > Hi all > I have a tables like this.. > fCode Number > 1 10 > 2 -3 > 3 4 > 1 -2 > 2 8 > I want to separate postivie and negative values for fCode. > like this > fCode PNum NNum > 1 10 -2 > 2 8 -3 > 3 4 0 > can I do that without using Union ??? > thanks in advance > Try this one:
CREATE TABLE #Temp ( fCode INT, Number INT ) INSERT #Temp VALUEs (1, 10) INSERT #Temp VALUEs (2, -3) INSERT #Temp VALUEs (3, 4) INSERT #Temp VALUEs (1, -2) INSERT #Temp VALUEs (2, 8) SELECT tbl1.fCode, tbl1.Number, ISNULL(tbl2.Number, 0) FROM (SELECT #Temp.fCode, #Temp.Number FROM #Temp WHERE Number > 0) tbl1 LEFT OUTER JOIN (SELECT #Temp.fCode, #Temp.Number FROM #Temp WHERE Number <= 0) tbl2 ON tbl1.fCode = tbl2.fCode  |
|||||||||||||||||||||||
Other interesting topics