[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 210
  • Last Modified:

Concatenating values into one line where data is same

Hi experts

I have a table which contains acad_period, register_id, register_group, week_no and student_id (see spreadsheet).  This table in spreadsheet shows an example of students who have not been marked for one particular register between weeks 2-5.  What I would like to do is output al list of student names for each week they are unmarked.  If students are different each week then I want seperate rows (eg week 2 and 3 in spreadsheet) with student_id concatenated for each week, but if same students are off a number of weeks then I want to concatenate the week numbers and student_id's into one row (example week 4 and 5 in spreadsheet)

I have attached my code so far but the output I am getting is incorrect

Help appreciated thanks sample-output.xls
;WITH CTE AS(
SELECT distinct acad_period ,
		register_id,
		register_group,
		STUFF(
			(SELECT ', ' + CONVERT(varchar,week_no)
			FROM #register_info 
			WHERE acad_period = t2.acad_period
			and register_id = t2.register_id
			and register_group = t2.register_group
			and student_id = t2.student_id
		
			order by cast(week_no as int)

			FOR XML PATH('')),1,1,'') as Week_Not_Marked,

		STUFF(
			(SELECT ', ' + student_id
			FROM #register_info 
			WHERE acad_period = t2.acad_period
			and register_id = t2.register_id
			and register_group = t2.register_group
			and week_no = t2.week_no
			order by student_id

			FOR XML PATH('')),1,1,'') as Students_Not_Marked

	FROM #register_info as t2
)

Open in new window

0
lisa_mc
Asked:
lisa_mc
  • 2
  • 2
1 Solution
 
lisa_mcAuthor Commented:
Hi emoreau

Could you amend my code to fit the link you provided as im lost

thanks
0
 
Éric MoreauSenior .Net ConsultantCommented:
here you go:

create table #register_info (
      acad_period                  varchar(10)
      , register_id            varchar(10)
      , register_group      varchar(10)
      , week_no                  varchar(10)
      , student_id            varchar(10)
)

insert into #register_info values('10/11', 'GH345', 'HY33', 2, 'ALL34536')
insert into #register_info values('10/11', 'GH345', 'HY33', 2, 'AVE45972')
insert into #register_info values('10/11', 'GH345', 'HY33', 2, 'BAR10067')
insert into #register_info values('10/11', 'GH345', 'HY33', 2, 'BAT23412')
insert into #register_info values('10/11', 'GH345', 'HY33', 2, 'BLA56299')
insert into #register_info values('10/11', 'GH345', 'HY33', 3, 'ALL34536')
insert into #register_info values('10/11', 'GH345', 'HY33', 3, 'AVE45972')
insert into #register_info values('10/11', 'GH345', 'HY33', 3, 'BAR10067')
insert into #register_info values('10/11', 'GH345', 'HY33', 3, 'BAT23412')
insert into #register_info values('10/11', 'GH345', 'HY33', 4, 'ALL34536')
insert into #register_info values('10/11', 'GH345', 'HY33', 4, 'AVE45972')
insert into #register_info values('10/11', 'GH345', 'HY33', 4, 'BAR10067')
insert into #register_info values('10/11', 'GH345', 'HY33', 5, 'ALL34536')
insert into #register_info values('10/11', 'GH345', 'HY33', 5, 'AVE45972')
insert into #register_info values('10/11', 'GH345', 'HY33', 5, 'BAR10067')


select distinct acad_period, register_id, register_group, week_no --, student_id            
, STUFF(
       (SELECT ', ' + student_id
        FROM #register_info
        WHERE acad_period = R.acad_period
        and register_id = R.register_id
        and register_group = R.register_group
        and week_no = R.week_no
        ORDER BY acad_period, register_id, register_group, week_no
        FOR XML PATH('')
       )
       , 1
       , 2
       , '') AS Students_Not_Marked
into #register_info2  
FROM #register_info AS R

--SELECT * FROM #register_info2

select distinct acad_period, register_id, register_group
, STUFF(
       (SELECT ', ' + week_no
        FROM #register_info2
        WHERE acad_period = R.acad_period
        and register_id = R.register_id
        and register_group = R.register_group
        and Students_Not_Marked = R.Students_Not_Marked
        ORDER BY acad_period, register_id, register_group, Students_Not_Marked
        FOR XML PATH('')
       )
       , 1
       , 2
       , '') AS Week_Not_Marked
, Students_Not_Marked      
FROM #register_info2 AS R


drop table #register_info
drop table #register_info2
0
 
lisa_mcAuthor Commented:
fantastic I was going abit crazy as it wasn't concatenating the weeks_not_marked field but I know why

ORDER BY acad_period, register_id, register_group, week_no
        FOR XML PATH('')
that line of code above should be changed to

ORDER BY student_id
        FOR XML PATH('')

thank you so much for your help I would never have gotten that in a million years :-)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now