0

Attendence_Stu_Main {table1}

Att_id varchar(50)P.k,                                     
st_rollno varchar(50)
st_name varchar(50)
branch_name varchar(50)
sem_no int
batch_year varchar(50)
batch_name varchar(50)
scode   varchar(50)
sess_no int
attendence int 
total int

Attendence_Stu_MainL1 {table2}

st_id varchar(50) p.k.
st_rollno varchar(50),
st_name varchar(50),
branch_name varchar(50),
sem_no int,
batch_year varchar(50),
batch_name varchar(50),
scode   varchar(50),
sess_no int,
attendence int, 
total int

I have been using below query in a SqlDataSource in ASP.NET

query = @"SELECT DISTINCT Attendence_Stu_Main.st_id, Attendence_Stu_Main.st_rollno,
 Attendence_Stu_Main.st_name,
 Attendence_Stu_Main.scode,
 Attendence_Stu_Main.attendence AS Theory,
 Attendence_Stu_Main.total As Total,
 Attendence_Stu_MainL1.attendence AS Lab,
 Attendence_Stu_MainL1.total AS Total1 
FROM  Attendence_Stu_Main 
LEFT OUTER JOIN Attendence_Stu_MainL1 
    ON Attendence_Stu_Main.st_id = Attendence_Stu_MainL1.st_id 
    AND Attendence_Stu_Main.scode = Attendence_Stu_MainL1.scode 
ORDER BY Attendence_Stu_Main.scode,Attendence_Stu_Main.st_rollno";

above query shows output in this form in GridVIew:---

st_id   st_rollno  st_name  subject  Theory      Total   Lab      Total1
--------------------------------------------------------------------------------
086001  IT001      PRANAV   mat        21           22       11       14
086002  IT002      DEEP     mat        21           22       11       14
086001  IT001      PRANAV   sci        20           24       09       12
086002  IT002      DEEP     sci        21           24       08       12

I want my output as below, using SQL Server and ASP.NET controls features, if anyone could help me..

st_id   st_rollno   st_name   subject   Theory   Total            Lab      Total1
---------------------------------------------------------------------------------------
086001  IT001      PRANAV   mat,sci 21,20       22,24          11,09       14,12
086002  IT002       DEEP    mat,sci 21,21       22 ,24         11,08       14,12
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Juhil H Patel
  • 1
  • 3
  • 1
    What is your question? Do you want `AutoGenerateColumns="false"` on the GridView so you can specify what columns show up? – MikeSmithDev Jan 28 '13 at 17:54
  • It seems like he wants to group the results by the columns st_id, st_rollno and st_name, concatenating the detail values for the other columns. See this link: http://stackoverflow.com/questions/8868604/sql-group-concat-function-in-sql-server – Mateus Schneiders Jan 28 '13 at 18:10

1 Answers1

2

There is no good way to do this in sql server outside of something like a cursor. Comma separated column data is considered an anti-pattern, and so it is difficult to make this happen with the core sql language by design.

Your best solution here will be to combine those records on the client side (relative to the database... from the asp.net standpoint, this means the web server)

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794