-2
ID          Name    Age   Result
1           Karthi  21    11.05
1           Karthi  21    25.00
2           Raja    23    15.00
2           Raja    23    17.00

I need result in 

ID Name   Age  Result  Result1
1  Karthi 21   11.05    25.00
2  Raja   23   15.00    17.00

like that ....

KARTHIKEYAN P
  • 3
  • 4
  • You can do this with group by name and a sum() function for the result. – Dean Nov 03 '17 at 17:32
  • What RDBMS? You could assign a row_number to each ID and then pivot on the row number. or perform a self join. Will there always only be 2 results or could it be n? Does the smaller of the results always come first in the results or how do you determine which goes to result vs result1? – xQbert Nov 03 '17 at 17:34
  • i don't need sum of result ...i just show in single row ... – KARTHIKEYAN P Nov 03 '17 at 17:35
  • 1
    There are hundreds of questions like this already. Did you really not find anything on a search? – underscore_d Nov 03 '17 at 17:35
  • WHERE'S YOUR SQL SERVER TAG???? – Eric Nov 03 '17 at 18:00
  • Have you even tried anything? Have you Googled your question? There are tons of example. – Eric Nov 03 '17 at 18:01
  • Possible duplicate of [Efficiently convert rows to columns in sql server](https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) – Clockwork-Muse Nov 03 '17 at 20:50

1 Answers1

0

Suppose you have the data in table named myTable then the following query will do the trick:

--The low values per ID
select id, Name, age , max(Result) as Result1
into #tmp1 from myTable
GROUP BY id,Name,Age

--The high values per ID
select id, Name, Age, min(Result) as Result
into #tmp2 from myTable
GROUP BY id,Name,Age

--Both values on the same line
select distinct t1.id, t1.Name, t1.age, t1.Result1, t2.Result
from #tmp1 t1
join #tmp2 t2 on t1.id = t2.id

It will work ONLY for two lines per ID.

Radu
  • 995
  • 12
  • 23
  • In case id have 3 or 2 or 1 like wise the count changing how to do that.. – KARTHIKEYAN P Nov 03 '17 at 18:30
  • There is a solution also for 3 lines per ID like that: --The low values per ID select id, Name, age , max(Result) as Result1 into #tmp1 from myTable GROUP BY id,Name,Age --The high values per ID select id, Name, Age, min(Result) as Result into #tmp2 from myTable GROUP BY id,Name,Age --Both values on the same line select distinct t1.id, t1.Name, t1.age, t1.Result1, t2.Result , t3.Result from #tmp1 t1 join #tmp2 t2 on t1.id = t2.id left join myTable t3 on t1.id = t3.id and t1.Result1 <> t3.Result and t2.Result <> t3.Result – Radu Nov 03 '17 at 19:03
  • KARTHIKEYAN P can you please accept my answer? [How to accept an answer](https://stackoverflow.com/help/someone-answers) – Radu Nov 04 '17 at 17:14