CODEWITHSUNDEEP
sql
hets
hets

Reputation: 43

update field value with multiple rows

I have a stored proc where i have a temp table and i need to update value of "day_name" column. loop throug temp table and update the value

This is example of data(there are multiple student names):

student        StudentID    courseID    course_name           period    day_name
Bray, Clifford  14088      10064    Physical Education 9GYM     1         A
Bray, Clifford  14088      10064    Physical Education 9GYM     1         B
Bray, Clifford  14088      10064    Physical Education 9GYM     1         C
Bray, Clifford  14088      10064    Physical Education 9GYM     1         D
Bray, Clifford  14088      10064    Physical Education 9GYM     2         A
Bray, Clifford  14088      10064    Physical Education 9GYM     2         B
Bray, Clifford  14088      10064    Physical Education 9GYM     2         C
Bray, Clifford  14088      10064    Physical Education 9GYM     2         D
Bray, Clifford  14088      10065    Physical Education 11GYM    3         B
Bray, Clifford  14088      10065    Physical Education 11GYM    3         D
Bray, Clifford  14088      10065    Physical Education 11GYM    5         A
Bray, Clifford  14088      10065    Physical Education 11GYM    5         C
Bray, Clifford  14088      10065    Physical Education 11GYM    6         A
Bray, Clifford  14088      10065    Physical Education 11GYM    6         B
Bray, Clifford  14088      10065    Physical Education 11GYM    6         C
Bray, Clifford  14088      10065    Physical Education 11GYM    6         D
Bray, Clifford  14088      10059    Health 9P373                7         A
Bray, Clifford  14088      10059    Health 9P373                7         B
Bray, Clifford  14088      10059    Health 9P373                7         C

I would like data to be like this:

student        studentID    courseID    course_name         period  day_name
Bray, Clifford  14088      10064    Physical Education 9GYM     1    ABCD
Bray, Clifford  14088      10064    Physical Education 9GYM     2    ABCD
Bray, Clifford  14088      10065    Physical Education 11GYM    3    BD
Bray, Clifford  14088      10065    Physical Education 11GYM    5    AC
Bray, Clifford  14088      10065    Physical Education 11GYM    6    ABCD
Bray, Clifford  14088      10059    Health 9P373                7    ABC

Thanks very much in advance:) please please help....

Upvotes: 0

Views: 48

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

With this as input:

DECLARE @STUDENTS TABLE (STUDENT VARCHAR(20), STUDENTID INT, COURSEID INT, COURSE_NAME VARCHAR(MAX), PERIOD INT, DAY_NAME CHAR(1))

INSERT @STUDENTS VALUES

('Bray, Clifford', 14088, 10064, 'Physical Education 9GYM', 1, 'A'),
('Bray, Clifford', 14088, 10064, 'Physical Education 9GYM', 1, 'B'),
('Bray, Clifford', 14088, 10064, 'Physical Education 9GYM', 1, 'C'),
('Bray, Clifford', 14088, 10064, 'Physical Education 9GYM', 1, 'D'),
('Bray, Clifford', 14088, 10064, 'Physical Education 9GYM', 2, 'A'),
('Bray, Clifford', 14088, 10064, 'Physical Education 9GYM', 2, 'B'),
('Bray, Clifford', 14088, 10064, 'Physical Education 9GYM', 2, 'C'),
('Bray, Clifford', 14088, 10064, 'Physical Education 9GYM', 2, 'D'),
('Bray, Clifford', 14088, 10065, 'Physical Education 11GYM', 3, 'B'),
('Bray, Clifford', 14088, 10065, 'Physical Education 11GYM', 3, 'D'),
('Bray, Clifford', 14088, 10065, 'Physical Education 11GYM', 5, 'A'),
('Bray, Clifford', 14088, 10065, 'Physical Education 11GYM', 5, 'C')

this query:

SELECT STUDENT, STUDENTID, COURSEID, COURSE_NAME, PERIOD,
      (SELECT DISTINCT '' + DAY_NAME
       FROM @STUDENTS          
       WHERE S.STUDENTID = STUDENTID AND S.COURSEID = COURSEID AND S.PERIOD = PERIOD
       FOR XML PATH ('')) AS DAYS
FROM @STUDENTS AS S
GROUP BY STUDENT, STUDENTID, COURSEID, COURSE_NAME, PERIOD

produces the following output:

    STUDENT        STUDENTID    COURSEID COURSE_NAME                PERIOD  DAYS
   ------------------------------------------------------------------------------
    Bray, Clifford 14088        10064    Physical Education 9GYM    1       ABCD
    Bray, Clifford 14088        10064    Physical Education 9GYM    2       ABCD
    Bray, Clifford 14088        10065    Physical Education 11GYM   3       BD
    Bray, Clifford 14088        10065    Physical Education 11GYM   5       AC

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Given that you have a finite list of day names, the easiest way is probably an explicit aggregation:

select student, StudentID, courseID, course_name, period,
       (max(case when day_name = 'A' then day_name else '' end) +
        max(case when day_name = 'B' then day_name else '' end) +
        max(case when day_name = 'C' then day_name else '' end) +
        max(case when day_name = 'D' then day_name else '' end)
       ) as days        
from table t
group by student, StudentID, courseID, course_name, period;

If you want a table to look like this, I would suggest that you create a new table. Modifying the original one doesn't really make sense -- you would have to delete rows as well as modifying or inserting values.

Upvotes: 1

Related Questions