-3

SQL result:

id student_name class
----------------------
1  abc          1A  
2  xyz          1A 
3  dsk          1A
4  uij          1A
................. 
.................
.................
.................
.................
up 1000 results

I want to format the data in my specific format

id1 student_name1 class1 id2 student_name2 class2 id3 student_name3 class3
-----------------------------------------------------------------------------------
1  abc             1A     2  abc            1A    3  abc          1A 
4  abc             1A     5  abc            1A    6  abc          1A
7  abc             1A
halfer
  • 19,824
  • 17
  • 99
  • 186
Ravi Kumar
  • 81
  • 11
  • 1
    Hi Ravi Kumar, this is not how you ask a question on StackOverflow. Please follow [John Skeet's guide](http://codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question/) to posting a question and update your question. Since time is precious to us all, I'd insist more on the `Sample code and data` section. – Radu Gheorghiu Jan 06 '16 at 08:21
  • THANKS SIR NEXT TIME I WILL FOLLOW ALL RULES – Ravi Kumar Jan 06 '16 at 10:18
  • 1
    Please also stop using all caps – James Z Jan 07 '16 at 17:44

1 Answers1

0

As comment says, your question isn't that clear, so I wrote query that does what I think you need ... try it out and let me know if it does what you wanted

if object_ID('tempdb..#Temp') IS NOT NULL drop table #Temp
create table #Temp (ID int, Student_name nvarchar(100), class nvarchar(10))

insert into #Temp (ID, Student_Name, Class)
values
(1, 'abc', '1A'),
(2, 'xyz', '1A'),
(3, 'dsk', '1A'),
(4, 'uij', '1A'),
(5, 'sss', '1A'),
(6, 'fff', '1A'),
(7, 'ccc', '1A')

select t1.ID [ID1], 
   t1.student_name [student_name1],
   t1.class [class1],
   t2.ID [ID2], 
   t2.student_name [student_name2],
   t2.class [class2],
   t3.ID [ID3], 
   t3.student_name [student_name3],
   t3.class [class3]
from #Temp t1
LEFT join #Temp t2 on t1.ID + 1 = t2.ID
LEFT join #Temp t3 on t1.ID + 2 = t3.ID
where (t1.ID - 1) % 3 = 0
Veljko89
  • 1,813
  • 3
  • 28
  • 43
  • You could also do this with row_number + pivot, so you wouldn't need to join the table with itself and it should be a lot faster. – James Z Jan 07 '16 at 17:46
  • Not sure about that @JamesZ , would be fun to see execution plan of both join and pivot ... my money is on joins here, just put an index on ID table and it should fly, that's just my opinion, but then again i might be wrong ... it seems to me as this situation http://stackoverflow.com/questions/7448453/sql-server-pivot-vs-multiple-join – Veljko89 Jan 08 '16 at 10:50
  • @Veljko89 That example isn't really the same thing because here you only have one table, with row_number there's no need for any joins, just read the table once, and it also works if there are gaps in the IDs. – James Z Jan 08 '16 at 11:35