This one is the interview question asked to one of my friend for the post of t-SQL developer.
We have a table with ID column containing following values in it
And the Output should be
We have a table with ID column containing following values in it
And the Output should be
How would you go about it and write the query for it.
Before proceeding for a solution lets build the table and populate it with the required values.
DECLARE @TAB TABLE
(
ID SMALLINT
)
INSERT INTO @TAB
SELECT 71 UNION ALL
SELECT 24 UNION ALL
SELECT -12 UNION ALL
SELECT -129 UNION ALL
SELECT 13 UNION ALL
SELECT 0 UNION ALL
SELECT -90
SELECT * FROM @TAB
There might be multiple solutions for this problem but this is what I could think of
;WITH Positive AS
(
SELECT
ID AS Pos
,ROW_NUMBER() OVER (ORDER BY ID) AS RANK1
FROM @TAB
WHERE ID >= 0
),Negative AS
(
SELECT
ID AS Neg
,ROW_NUMBER() OVER (ORDER BY ID DESC) AS RANK2
FROM @TAB
WHERE ID < 0
)
SELECT Pos,Neg
FROM Positive
FULL OUTER JOIN Negative
ON RANK1 = RANK2
Answer
ReplyDeleteselect
positive=case when id>0 then id end,
negative=case when id<0 then id end
from
table_name;
That won't give you the expected answer
Delete