NEED TO FIND ALTERNATIVE CODE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

NEED TO FIND ALTERNATIVE CODE

Author

Topic

Nil35
Starting Member

20 Posts

Posted - 2013-07-17 : 11:54:56

CREATE TABLE #t1
(
ID int,
COL1 int,
COL2 int
)
INSERT INTO #t1
SELECT 0,0,NULL
UNION ALL
SELECT 1,3,1
UNION ALL
SELECT 2,5,3
UNION ALL
SELECT 3,4,2
UNION ALL
SELECT 4,2,1

SELECT A.*,A.COL1 + B.COL2 AS COLAB FROM #T1 a
LEFT JOIN #T1 B ON A.ID = B.ID +1

DROP TABLE #t1

USNING SQL SERVER 2008R2
ANY ALTERNATIVE CODE FOR THIS
PLEASE HELP ME OUT

THANKS

nil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-17 : 12:04:36

Why whats the issue with current code?


SELECT t1.*,t1.COL1 + t2.COL2  AS COLAB
FROM #t1 t1
OUTER APPLY(SELECT COL2
            FROM #t1
            WHERE ID = t1.ID -1
           )t2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Nil35
Starting Member

20 Posts

Posted - 2013-07-17 : 12:09:53

THANKS VISAKH

nil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-17 : 13:21:28

This is another method


SELECT 
MAX(CASE WHEN n=0 THEN ID END) AS ID,
 MAX(CASE WHEN n=0 THEN COL1 END) AS COl1,
 MAX(CASE WHEN n=0 THEN COL2 END) AS COl2,
 MAX(CASE WHEN n=0 THEN COL1 END) + MAX(CASE WHEN n=1 THEN COL2 END) AS COLAB
FROM #t1 t1
CROSS JOIN (VALUES(0),(1)) AS t2(n)
GROUP BY t1.ID + t2.n 
HAVING MAX(CASE WHEN n=0 THEN ID END) IS NOT NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources