I want to merge contents of columns based on same student_id

Question

I have a table in SQL Server 2008 like this:

st_id   st_rollno  st_name  subject  Theory      Total   Lab      Total
--------------------------------------------------------------------------------
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 2008 features, if any 1 could help me..?

st_id   st_rollno   st_name   subject   Theory   Total            Lab      Total
---------------------------------------------------------------------------------------
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

This is not possible..ಠ_ಠ – Aniket Inge Jan 28 '13 at 05:48 — Aniket Inge, Jan 28 '13 at 05:48
same column name like Total is not possible in sql server – Prashant16 Jan 28 '13 at 05:53 — Prashant16, Jan 28 '13 at 05:53

score 1 · Answer 1 · answered Jan 28 '13 at 06:40

SQL Fiddle

MS SQL Server 2008 Schema Setup:

create table YourTable
(
  st_id varchar(6),
  st_rollno varchar(5),
  st_name varchar(6),
  subject varchar(3),
  Theory int,
  Total1 int,
  Lab int,
  Total2 int
)

insert into YourTable values
('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)

Query 1:

select T1.st_id,
       T1.st_rollno,
       T1.st_name,
       stuff(T3.X.query('subject').value('.', 'varchar(max)'), 1, 1, '') as subject,
       stuff(T3.X.query('Theory').value('.', 'varchar(max)'), 1, 1, '') as theory,
       stuff(T3.X.query('Total1').value('.', 'varchar(max)'), 1, 1, '') as Total1,
       stuff(T3.X.query('Lab').value('.', 'varchar(max)'), 1, 1, '') as Lab,
       stuff(T3.X.query('Total2').value('.', 'varchar(max)'), 1, 1, '') as Total2
from (
     select st_id, st_rollno, st_name
     from YourTable
     group by st_id, st_rollno, st_name
     ) as T1
cross apply 
     (
     select  ','+T2.subject                     as subject,
             ','+cast(T2.Theory as varchar(10)) as Theory,
             ','+cast(T2.Total1 as varchar(10)) as Total1,
             ','+cast(T2.Lab    as varchar(10)) as Lab,
             ','+cast(T2.Total2 as varchar(10)) as Total2
     from YourTable as T2
     where T1.st_id = T2.st_id and
           T1.st_name = T2.st_name and
           T1.st_rollno = T2.st_rollno
     for xml path(''), type
     ) as T3(X)

Results:

|  ST_ID | ST_ROLLNO | ST_NAME | SUBJECT | THEORY | TOTAL1 |  LAB | TOTAL2 |
----------------------------------------------------------------------------
| 086001 |     IT001 |  PRANAV | mat,sci |  21,20 |  22,24 | 11,9 |  14,12 |
| 086002 |     IT002 |  DEEP   | mat,sci |  21,21 |  22,24 | 11,8 |  14,12 |

i tried it in visual studio sqldatasource control .it showing me error that cross apply is not available.plz,contact customer service..what can i do? — Juhil H Patel, Jan 28 '13 at 08:35
@JuhilHPatel I don't know what the limitations of the `sqldatasource control` is or what to do about it. Perhaps you should ask that as a new question. — Mikael Eriksson, Jan 28 '13 at 09:46
@JuhilHPatel I like this answer better than mine below, but if it is not working for your setup I would try that. You might encounter the same issue though. — Matthew, Jan 28 '13 at 16:23

score 0 · Answer 2 · edited May 23 '17 at 12:21

Adapted from here: SQL Server Concatenate GROUP BY you are looking for something like this:

SELECT 
 st_id, st_rollno, st_name,
 STUFF
 (
    (
        SELECT 
            ',' +subject  
        FROM
            yourtable
        WHERE
            yourtable.st_id=st_id
        FOR XML PATH('')
    )
,1,1,'') AS subject, 

...

FROM yourtable

The core concepts to understand here are stuff and for xml I don't have the syntax quite right, but this should get you on the right path if no one else posts. I will update this when I get near SSMS and can test. Definitely this is doable. I find it easier in MySQL with Group_Concat.

Thanks a lot Matthew,,I know it is too much easier in MYSQL but i have to do it in sql2008 cause of client requirenments.. — Juhil H Patel, Jan 28 '13 at 06:19

I want to merge contents of columns based on same student_id

2 Answers2