[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Concatenating values into one line where data is same

Posted on 2011-09-19
4
Medium Priority
?
209 Views
Last Modified: 2012-05-12
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
Comment
Question by:lisa_mc
  • 2
  • 2
4 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 36559659
0
 
LVL 3

Author Comment

by:lisa_mc
ID: 36560036
Hi emoreau

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

thanks
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 2000 total points
ID: 36560230
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
 
LVL 3

Author Comment

by:lisa_mc
ID: 36560383
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question