Merge rows into 1 rows with separate columns

Question

FirstName |LastName |OtherName |admim_no| ExamCategory | subject | Exam| Score| Grade 
Anthony   | Tee     | Tony     | 1000   | MockExams2014|Chemistry| PP1 | 12   | D+
Anthony   | Tee     | Tony     | 1000   | MockExams2014|Biology  | PP1 | 50   | C+
Moses K.  | Ndu     |          | 1001   | MockExams2014|Chemistry| PP1 | 65   | B+
Moses K.  | Ndu     |          | 1001   | MockExams2014|Biology  | PP1 | 85   | A

What I want to achieve in sql server 2012 is for each exam category I have admission_no grouped into one row and then the columns subject, exam, score, grade be placed as individual columns for the 2nd row so that you have for example admission_no 1000 and all different data for the columns (subject, exam, score, grade) as single row. I cant get how to go around it. I have tried the below unsuccessfully.

My result should like below

Anthony |   Tee |    Tony  |  1000|  MockExams2014|Chemistry|   PP1 |  12| D+   Biology|   PP1 |   50| C+

SQLITE: merging rows into single row if they share a column

enter image description here

So for `admission_no 1000` you want two `subject` columns, two `Grade` columns, etc.? That is, all those last 4 columns repeated as many times as different subjects that `admin_no` has? — Andrew, Nov 25 '14 at 17:16
You can do this with a dynamic pivot or a dynamic cross tab. There are tons of questions on this topic around this site and the entire internet. Just search for dynamic pivot and will find them. — Sean Lange, Nov 25 '14 at 18:22

score 0 · Accepted Answer · answered Nov 25 '14 at 19:10

If you would allow to concatenate then repeated values into on row you would be able to work with a joined subquery using XML PATH, show as addition (2) below.
To get the repeated values as columns you could count the maximum rows for admim_no and create a dynamic sql using as many left joins as necessary to get the needed columns.

/* test data
Create table TestX (FirstName varchar(50) ,LastName varchar(50),OtherName varchar(50), admim_no int,ExamCategory varchar(50), subject varchar(50), Exam Varchar(50), Score int,grade varchar(50))
Insert into TestX Values ('Anthony','Tee','Tony',1000 ,' MockExams2014','Chemistry','PP1',12,'D+')
Insert into TestX Values ('Anthony','Tee','Tony',1000 ,' MockExams2014','Biology','PP1',50,'C+')
Insert into TestX Values ('Moses K','Ndu',NULL,1001 ,' MockExams2014','Chemistry','PP1',65,'B+')
Insert into TestX Values ('Moses K','Ndu',NULL,1001 ,' MockExams2014','Biology','PP1',85,'A')
Insert into TestX Values ('Moses K','Ndu',NULL,1001 ,' MockExams2014','Physiks','PP2',12,'D+')
*/


Declare @maxcnt int
Declare @cnt int
Declare @Dyn1 varchar(max)
Declare @Dyn2 varchar(max)

Select @maxcnt=MAX(cnt) from
       (Select COUNT(*) as cnt from TestX Group by admim_no ) a




Set @Dyn1='
;With CTE as
(
Select *, Row_Number() over (Partition by admim_no order by admim_no ) as rn  from TestX
)
Select CTE0.*
'
Set @Dyn2='
           From CTE CTE0'
Set @cnt=1
While @maxcnt>1
  begin
  Set @Dyn1 = @Dyn1 + ',CTE' + Cast(@cnt as varchar(5))+'.subject
                       ,CTE' + Cast(@cnt as varchar(5))+'.Exam
                       ,CTE' + Cast(@cnt as varchar(5))+'.Score
                       ,CTE' + Cast(@cnt as varchar(5))+'.grade'
  Set @Dyn2 = @Dyn2 + ' 
                       LEFT Join CTE CTE' + Cast(@cnt as varchar(5))+'
                       on CTE' + Cast(@cnt as varchar(5))+'.rn=CTE0.rn + '+Cast(@cnt as varchar(5))+' 
                       and CTE' + Cast(@cnt as varchar(5))+'.admim_no=CTE0.admim_no'   
  Set @maxcnt=@maxcnt-1
  Set @cnt=@cnt+1
  end 
Set @Dyn2 = @Dyn1 + @Dyn2 + '
                       where CTE0.rn=1'
Print @Dyn2
Exec(@Dyn2)

the generated SQL for the test data provided would look like:

;With CTE as
(
Select *, Row_Number() over (Partition by admim_no order by admim_no ) as rn  from TestX
)
Select CTE0.*
,CTE1.subject
                       ,CTE1.Exam
                       ,CTE1.Score
                       ,CTE1.grade,CTE2.subject
                       ,CTE2.Exam
                       ,CTE2.Score
                       ,CTE2.grade
           From CTE CTE0 
                       LEFT Join CTE CTE1
                       on CTE1.rn=CTE0.rn + 1 
                       and CTE1.admim_no=CTE0.admim_no 
                       LEFT Join CTE CTE2
                       on CTE2.rn=CTE0.rn + 2 
                       and CTE2.admim_no=CTE0.admim_no
                       where CTE0.rn=1

the result like

Anthony Tee Tony    1000     MockExams2014  Chemistry   PP1 12  D+  1   Biology PP1 50  C+  NULL    NULL    NULL    NULL
Moses K Ndu NULL    1001     MockExams2014  Chemistry   PP1 65  B+  1   Biology PP1 85  A   Physiks PP2 12  D+

The easier way with one column for outpu as initial mentioned (2) would look like this:

Declare @a table (FirstName varchar(50) ,LastName varchar(50),OtherName varchar(50), admim_no int,ExamCategory varchar(50), subject varchar(50), Exam Varchar(50), Score int,grade varchar(50))
Insert into @a Values ('Anthony','Tee','Tony',1000 ,' MockExams2014','Chemistry','PP1',12,'D+')
Insert into @a Values ('Anthony','Tee','Tony',1000 ,' MockExams2014','Biology','PP1',50,'C+')
Insert into @a Values ('Moses K','Ndu',NULL,1001 ,' MockExams2014','Chemistry','PP1',65,'B+')
Insert into @a Values ('Moses K','Ndu',NULL,1001 ,' MockExams2014','Biology','PP1',85,'A')
Insert into @a Values ('Moses K','Ndu',NULL,1001 ,' MockExams2014','Physiks','PP1',12,'D+')


Select FirstName,LastName,OtherName,admim_no,ExamCategory
, STUFF(
             (SELECT ',' + subject +', ' + Exam +', ' + CAST(Score as varchar(20)) + ', ' + Grade 
              FROM @a a2 where a1.admim_no = a2.admim_no            
              FOR XML PATH (''))       
             , 1, 1, '') as subjectExamScoreGrade       

from  @a a1
Group by  FirstName,LastName,OtherName,admim_no,ExamCategory

Thank you. Great Man. Kudos. I m so greatfull. Keep up. Don't know what to give you. Thank you once more. — Anthony Wanyoike, Nov 29 '14 at 05:42

Merge rows into 1 rows with separate columns

1 Answers1