SQL Query
-
I have one column table with negative and positive values and want to display positive and negative values in different columns using SQL Query. Column -10000 -17000 16000 25000 output should be like A B ----------------- -10000 16000 -17000 25000
-
I have one column table with negative and positive values and want to display positive and negative values in different columns using SQL Query. Column -10000 -17000 16000 25000 output should be like A B ----------------- -10000 16000 -17000 25000
And how do you determine that 16000 and -10000 belong into the same column, but not 16000 and -25000? What's the rule behind that?
-
I have one column table with negative and positive values and want to display positive and negative values in different columns using SQL Query. Column -10000 -17000 16000 25000 output should be like A B ----------------- -10000 16000 -17000 25000
One of the problems you will have with creating a query to do this is that the column of numbers will most likely not have an equal number of positive and negative values. Even if it does, I don't know of a way to create a single query to create the output you illustrate. The best I could do in this situation is the following which will output the same number of rows as in the original table but simply put a NULL value in the column that does not apply:
SELECT
CASE
WHEN NumberField > 0 THEN NumberField
ELSE NULL
END AS PositiveNumbers,
CASE
WHEN NumberField < 0 THEN NumberField
ELSE NULL
END AS NegativeNumbers
FROM NumbersTableOf course, you also have to decide what exactly to do with values of zero.
-NP Never underestimate the creativity of the end-user.
-
I have one column table with negative and positive values and want to display positive and negative values in different columns using SQL Query. Column -10000 -17000 16000 25000 output should be like A B ----------------- -10000 16000 -17000 25000
With SQL Server 2012?
SELECT A.[Column] A
, B.[Column] B
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY [Column] DESC) RN , [Column] FROM [Table] WHERE [Column]<0)A
FULL OUTER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY [Column]) RN , [Column] FROM [Table] WHERE [Column]>0)B
ON A.RN=B.RN -
With SQL Server 2012?
SELECT A.[Column] A
, B.[Column] B
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY [Column] DESC) RN , [Column] FROM [Table] WHERE [Column]<0)A
FULL OUTER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY [Column]) RN , [Column] FROM [Table] WHERE [Column]>0)B
ON A.RN=B.RNA good example of CAN be done versus SHOULD be done... But, well done...
-
With SQL Server 2012?
SELECT A.[Column] A
, B.[Column] B
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY [Column] DESC) RN , [Column] FROM [Table] WHERE [Column]<0)A
FULL OUTER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY [Column]) RN , [Column] FROM [Table] WHERE [Column]>0)B
ON A.RN=B.RNErk - I mean I like ROW_NUMBER but that is just... I agree with Tim!
Never underestimate the power of human stupidity RAH
-
A good example of CAN be done versus SHOULD be done... But, well done...
What? I didn't use CTEs. :-D
-
One of the problems you will have with creating a query to do this is that the column of numbers will most likely not have an equal number of positive and negative values. Even if it does, I don't know of a way to create a single query to create the output you illustrate. The best I could do in this situation is the following which will output the same number of rows as in the original table but simply put a NULL value in the column that does not apply:
SELECT
CASE
WHEN NumberField > 0 THEN NumberField
ELSE NULL
END AS PositiveNumbers,
CASE
WHEN NumberField < 0 THEN NumberField
ELSE NULL
END AS NegativeNumbers
FROM NumbersTableOf course, you also have to decide what exactly to do with values of zero.
-NP Never underestimate the creativity of the end-user.
thanks! that is useful for me.