Solved

Concatenating values into one line where data is same

Posted on 2011-09-19
4
194 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 69

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 69

Accepted Solution

by:
Éric Moreau earned 500 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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now