//
You're reading...
Challenges, SQL

SQL Challenge 1

Problem:

how do I write a query in SQL to do this?

I have a table:

name mark 
u 23 
h 38 
j 20 
p 83

and for output we need

name mark mark2 
u 23 38 
h 38 20 
j 20 83 
p 83 0

Solution:

The query to accomplish the task: is

;WITH
 Test (ID, Name, Marks) AS
 (
 SELECT ID,[name],[Marks] FROM tbl_Rep
 )SELECT
 A.[ID],A.[Name],A.[Marks],ISNULL(B.[Marks],0) as [Marks2]
 FROM Test A
 Left Join [tbl_Rep] B On A.[ID]+1=B.[ID]

Here in this code we have created a temporary table using Common Table Expression and using self join we have joined two instances of a same table with id of one equals to the one plus id. Thats’ the whole process is this.

we can do it with the help of loop but join will be the more efficient technique. Also, in this script IsNull() can be replaced by Coalesce() function.

Regards,

Shahan Ayyub

Advertisements

About Shahan Ayyub

Shahan is a Microsoft Certified Professional of .Net. Mostly self taught programmer and a quick learner, who just love to code and always eager to find unique solutions to the common problems. Currently working as an iOS developer.

Discussion

2 thoughts on “SQL Challenge 1

  1. Here is a simpler way to do this:

    ;WITH cte AS (
    SELECT name
    , mark
    , ROW_NUMBER() OVER (ORDER BY (SELECT(0)) AS n
    FROM tbl_Rep
    )
    SELECT a.name, a.mark, COALESCE(b.mark,0) as mark2
    FROM cte a
    LEFT JOIN cte b
    ON b.n = a.n+1

    Posted by jsimonbi | August 30, 2011, 12:54 am
    • There is another way as I planned to update in few days:

      ;WITH
      Test (ID, Name, Marks) AS
      (
      SELECT ID,[name],[Marks] FROM tbl_Rep
      )SELECT
      A.[ID],A.[Name],A.[Marks],ISNULL(B.[Marks],0) as [Marks2]
      FROM
      Test A
      Left Join [tbl_Rep] B On A.[ID]+1=B.[ID]

      You have created a query that is also applicable if we have (or don’t have) a primary key right ?

      There are several ways to do so. I have seen another way where Rank() can be used as well.

      Thank you, for reading and sharing. 🙂

      Posted by Shahan Ayyub | August 30, 2011, 1:03 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: