在SQL Server 2008 R2中以逗号分隔的1行中获取相同ID的值 [英] Get values for the same ID into 1 row separated by a comma in SQL Server 2008 R2
问题描述
我有一个生成这个的查询
StudentID | 建筑 | 期间 | 成绩
12345 2             1        11      
12345 2             5        11
43210 &NBSP ;         2             1        12
但是我需要一种方法来按学生ID分组句号:
StudentID | 构建 | 期间 | 成绩
12345 2             1,5-      11
43210  &NBSP ;        2             1         12
这是我的询问:
选择r.student_id [StudentID],
r.building [建筑] ,
ab.attedancePeriod [期间],
r.grade [等级]
来自注册r
inner加入att_bottom ab on ab.student_id = r.student_id
其中ab.building ='2'
和ab.attendance_c ='T'
欢迎提出任何建议!
更新
下面的查询检索分配给建筑物2的所有StudentID,无论他们是否在特定日期(今天的日期)有出勤代码T。 br />
当我只需要那些没有的时候(这将是那些有周期的那些),它会给那些没有的值赋值。
选择 r.student_id [StudentID],
r.building [Building],
( 选择 ab.attendancePeriod + ' ,'
来自 att_bottom ab
其中 ab.student_id = r.student_id
和 ab.building = ' 2'
和 ab.attendance_c = ' T '
AND CONVERT ( varchar ,ab.attendance_date, 102 )= convert ( varchar ,getdate(), 102 )
FOR XML PATH(' ')) AS PERIOD,
r.GRADE
FROM 注册r
结果
StudentID | 建筑 | 期间 | 成绩
12345 2             1,        11     
43210 2             1,          12
98775            2           NULL    10
SQL Wizardry第七部分 - PIVOT和任意数据列表 [ ^ ]使用FOR XML显示如何执行此操作。\
< br $>
选择 r.student_id [StudentID],
r .building [建筑],
ab.attedancePeriod [期间],
STUFF
(
(
SELECT ' ,' + convert (< span class =code-keyword> varchar ( 2 ),等级)
FROM 注册
WHERE register.student_id = r.student_id
group BY 月(student_id)
订单 按月(年级)
FOR XML PATH(' ')
), 1 , 1 ,' < span class =code-string>'
) AS 等级
来自注册r
内部 join att_bottom ab on ab.student_id = r.student_id
其中 ab.building = ' 2'
和 ab.attendance_c = ' T'
group by r.student_id
是我的猜测,但没有源表,很难知道。
哦,你可能想试试这个:
选择 r.student_id [StudentID],
r.building [建筑],
(选择 ab.attendancePeriod + ' ,'
来自 att_bottom ab
其中 ab.student_id = r.student_id
和 ab.building = ' 2'
和 ab.attendance_c = ' T'
AND CONVERT ( varchar ,ab.attendance_date,< span class =code-digit> 102 )= convert ( varchar ,getdate( ), 102 )
FOR XML PATH( ' ')) AS PERIOD,
r.GRADE
FROM 注册r
内部 加入 att_bottom a.student_id = r.student_id
其中 a。出勤率= ' T'
我认为问题是,你是在内部层面上过滤,而不是在外层上过滤。内层的过滤器可能会被移除。
您是否检查过这个?
I have a query that generates this
StudentID |Building |Period |Grade
12345 2 1 11
12345 2 5 11
43210 2 1 12
But I need a way to group the Periods by the StudentID:
StudentID |Building |Period | Grade
12345 2 1,5 11
43210 2 1 12
This is my query:
select r.student_id [StudentID],
r.building [Building],
ab.attedancePeriod [Period],
r.grade [Grade]
from register r
inner join att_bottom ab on ab.student_id = r.student_id
where ab.building = '2'
and ab.attendance_c = 'T'
Any suggestions are more than welcomed!
Update
The query below retrieves all of the StudentID's assigned to building 2 regardless if they have an attendance code of T or not for the specific date (today's date).
It's assigning a NULL value to the ones that don't when I just need the ones that do (which will be the ones with a period).
select r.student_id [StudentID],
r.building [Building],
(select ab.attendancePeriod + ','
from att_bottom ab
where ab.student_id = r.student_id
and ab.building = '2'
and ab.attendance_c ='T'
AND CONVERT(varchar,ab.attendance_date,102) = convert(varchar,getdate(),102)
FOR XML PATH ('') ) AS PERIOD,
r.GRADE
FROM register r
Result
StudentID |Building |Period |Grade
12345 2 1 , 11
43210 2 1 , 12
98775 2 NULL 10
SQL Wizardry Part Seven - PIVOT and arbitrary lists of data[^] shows how to do this, using FOR XML.\
select r.student_id [StudentID], r.building [Building], ab.attedancePeriod [Period], STUFF ( ( SELECT ',' + convert(varchar(2), grade) FROM register WHERE register.student_id = r.student_id group BY month(student_id) order by month(grade) FOR XML PATH('') ), 1, 1, '' ) AS Grades from register r inner join att_bottom ab on ab.student_id = r.student_id where ab.building = '2' and ab.attendance_c = 'T' group by r.student_id
is my guess, but without the source tables, it's hard to know.
Oh, you may want to try this:
select r.student_id [StudentID], r.building [Building], (select ab.attendancePeriod + ',' from att_bottom ab where ab.student_id = r.student_id and ab.building = '2' and ab.attendance_c ='T' AND CONVERT(varchar,ab.attendance_date,102) = convert(varchar,getdate(),102) FOR XML PATH ('') ) AS PERIOD, r.GRADE FROM register r inner join att_bottom a on a.student_id = r.student_id where a.attendance = 'T'
I think the issue is, you're filtering on the inner level, but not the outer level. The filter on the inner level may be able to be removed.
Did you check this ?
这篇关于在SQL Server 2008 R2中以逗号分隔的1行中获取相同ID的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!