sql queries write down

Question

1.57/5 (3 votes)

See more:

SQL-Server-2005

I have have a table

and for output we need

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

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

Posted 24-Aug-11 9:43am

udaipal

Updated 24-Aug-11 9:49am

Chris Maunder772.8K

v2

Add a Solution

Comments

AspDotNetDev 24-Aug-11 16:52pm

Do you have an identity field or some field you can use to sort by (e.g., date created)? If not, then you may have to use a cursor.

walterhevedeich 24-Aug-11 16:54pm

Your question is not clear. How can we get the values on the third column? You somehow failed to explain this on your question.

Shahan Ayyub 25-Aug-11 6:46am

In your question you have this at the end of each row:
38
20
83
0
Is this the "new column" ? or you have concatenated it to the second column with <space> ? Kindly mention it in you question.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

raviprajapat · Answer 1 · 2011-08-24T12:15:00

I create a function in sql

CREATE FUNCTION UpdateNameMark(@name varchar(50)) 
returns int
as

   BEGIN 
   declare @No int
   if (@name='u')
   select @No= 38
   else if (@name='h')
   select @No =20
   else if (@name='j')
   select @No= 83
   else if (@name='p')
   select @No= 0
   
   return @No
      
 END

if want this

select name , mark , NameMark(name) from <your table="" name=""> </your>

if you want this

updaye <tablename> set mark= NameMark(name)</tablename>

Shahan Ayyub · Answer 2 · 2011-08-26T23:39:00

I have created a table with column [ID] int, [name] varchar, [marks] int where table name [tbl_Rep]is choosen, and add the records you have provided. Using the script below i am getting the same result you have asked for.

So, Check this script:

SQL

select * into #temp from tbl_Rep

alter table #temp add [marks2] int NULL

    DECLARE @count INT;
    Set @count = 0;
    DECLARE @max INT;
    Set @max = (Select Count(*) from tbl_Rep);
    print @count;
    WHILE (@count < @max)
    BEGIN
       update #temp Set [Marks2]=(select [Marks] from #temp where ID=@count+1) where ID=@count
       SET @count = (@count + 1)
    END
update #temp Set [Marks2]=0 where ID= (select MAX(ID) from #temp)
select * from #temp

drop table #temp

Shahan Ayyub · Answer 3 · 2011-08-28T22:37:00

As an alternative, a more optimized way is to join ID+1 of table 'A' to ID of table 'B' instead of looping:

SQL

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]

sql queries write down

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0