T- SQL Miscellaneous 5

1) For the given input provide the given output

CREATE TABLE TableA (id INT)

INSERT INTO TableA VALUES(1)

INSERT INTO TableA VALUES(2)

INSERT INTO TableA VALUES(3)

INSERT INTO TableA VALUES(4)

INSERT INTO TableA VALUES(5)

INSERT INTO TableA VALUES(-5)

INSERT INTO TableA VALUES(-4)

INSERT INTO TableA VALUES(-3)

INSERT INTO TableA VALUES(-2)

INSERT INTO TableA VALUES(-1)

INSERT INTO TableA VALUES(-6)

SELECT * FROM TableA

;WITH CTEA

AS

(

      SELECT *,Rno=ROW_NUMBER() OVER(ORDER BY ID ASC)

      FROM TableA

      WHERE id<0

),

CTEB

AS

(

    SELECT *,Rno=ROW_NUMBER() OVER(ORDER BY ID ASC)

      FROM TableA

      WHERE id>0

)

SELECT A.id,B.id

FROM CTEA A

FULL OUTER JOIN CTEB  B ON A.Rno=b.rno

2) Transforming rows into comma separated values

All the queries below gives the same output

DECLARE @val VARCHAR(10)

SELECT @val=COALESCE(@val,'')+val+','

FROM

(

       SELECT '1' AS val

       UNION ALL

       SELECT '2'

       UNION ALL

       SELECT '3'

)x

SELECT @val

-----------------------------------------

DECLARE @val VARCHAR(10)

SELECT @val=ISNULL(@val,'')+val+','

FROM

(

       SELECT '1' AS val

       UNION ALL

       SELECT '2'

       UNION ALL

       SELECT '3'

)x

SELECT @val

-----------------------------------------

DECLARE @val VARCHAR(10)

SET @val=''

SELECT @val=@val+val+','

FROM

(

       SELECT '1' AS val

       UNION ALL

       SELECT '2'

       UNION ALL

       SELECT '3'

)x

SELECT @val

Lets see hows this works. You can see that in the above query, multiple output is assigned to a variable. But a variable will take one value at a time. Below query will illustrate that,

DECLARE @val VARCHAR(10)

SET @val=''

SELECT @val=val

FROM

(

       SELECT '1' AS val

       UNION ALL

       SELECT '2'

       UNION ALL

       SELECT '3'

)x

SELECT @val

On executing the above query, you will get the result as 3. It means, each and every record from the select is assigned to the variable and at the end of query execution last value is held in the variable. Since the expression used is @val=@val+Val, it functions as below
@val=@val+Val
@val=''+1+',' ->1,
@val=1,+2+',' ->1,2,
@val=1,2+3+',' -> 1,2,3,