July 11, 2013 at 11:24 am
i have to grab this filemakerID based on trainer id
table1
TrainerIDFilemakerID
1078 5342
1138 8739
1810 4609
and associate the filemakerID with a course:
table2
EntryIDTrainerIDCourseNums
138 1138 37
139 1138 120
140 1138 119
654 1078 150
655 1078 151
656 1078 38
657 1078 36
658 1810 37
659 1810 159
660 1810 111
and combine course numbers(separated by comas) associated with same filemakerID and insert the data into a table3: Can this be done without putting data into temp tables or processing with a cursor to combine and separate coursenums by commas?
FilemakerID CourseNums
5342 36, 37, 38, 150, 151
8739 37, 119, 120
4609 37, 159, 111
July 11, 2013 at 12:19 pm
July 11, 2013 at 12:34 pm
You absolutely do not need a cursor or any intermediate table for this type of thing. As eluded to by Manic Star you can use FOR XML to do this quite easily.
The first thing you should always do around here is post ddl and sample data in a consumable format like this:
create table #t1
(
TrainerID int,
FilemakerID int
)
insert #t1
select 1078, 5342 union all
select 1138, 8739 union all
select 1810, 4609
create table #t2
(
EntryID int,
TrainerID int,
CourseNums int
)
insert #t2
select 138, 1138, 37 union all
select 139, 1138, 120 union all
select 140, 1138, 119 union all
select 654, 1078, 150 union all
select 655, 1078, 151 union all
select 656, 1078, 38 union all
select 657, 1078, 36 union all
select 658, 1810, 37 union all
select 659, 1810, 159 union all
select 660, 1810, 111
This makes it easy for the volunteers around here to work on your problem instead of setting it up. 😉
Now the query itself is quite simple once you understand how this works.
select FilemakerID,
STUFF((select ',' + cast(CourseNums as varchar(10))
from #t2 t2 where t2.TrainerID = t.TrainerID
order by CourseNums
FOR XML PATH('')), 1, 1, ' ')
from #t1 t
group by FilemakerID, t.TrainerID
order by FilemakerID
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 11, 2013 at 1:24 pm
Thank you very much, and that article is very helpful...may i ask one more bit of advice form you?
select FilemakerID,
STUFF((select ',' + cast(Courseid as varchar(10))
from MOC_Trainers_Courses t2 where t2.TrainerID = t.TrainerID
order by Courseid
FOR XML PATH('')), 1, 1, ' ')
from MOC_TrainerList t
group by FilemakerID, t.TrainerID
How Would i omit the lines where null comes back for couseid
FilemakerID(No column name)
4018 NULL
4019 NULL
4023 NULL
4041 33, 1560, 1561, 1562
4046 NULL
🙂
July 11, 2013 at 1:35 pm
TryingToLearn (7/11/2013)
Thank you very much, and that article is very helpful...may i ask one more bit of advice form you?
select FilemakerID,
STUFF((select ',' + cast(Courseid as varchar(10))
from MOC_Trainers_Courses t2 where t2.TrainerID = t.TrainerID
order by Courseid
FOR XML PATH('')), 1, 1, ' ')
from MOC_TrainerList t
group by FilemakerID, t.TrainerID
How Would i omit the lines where null comes back for couseid
FilemakerID(No column name)
4018 NULL
4019 NULL
4023 NULL
4041 33, 1560, 1561, 1562
4046 NULL
🙂
I included some sample data to demonstrate how you can do this.
create table #t1
(
TrainerID int,
FilemakerID int
)
insert #t1
select 1078, 5342 union all
select 1138, 8739 union all
select 1810, 4609 union all
select 123, 123
create table #t2
(
EntryID int,
TrainerID int,
CourseNums int
)
insert #t2
select 138, 1138, 37 union all
select 139, 1138, 120 union all
select 140, 1138, 119 union all
select 654, 1078, 150 union all
select 655, 1078, 151 union all
select 656, 1078, 38 union all
select 657, 1078, 36 union all
select 658, 1810, 37 union all
select 659, 1810, 159 union all
select 660, 1810, 111
select * from
(
select FilemakerID,
STUFF((select ',' + cast(CourseNums as varchar(10))
from #t2 t2 where t2.TrainerID = t.TrainerID
order by CourseNums
FOR XMLPATH('')), 1, 1, ' ') as CourseList
from #t1 t
group by FilemakerID, t.TrainerID
) x
where x.CourseList is not null
order by FilemakerID
drop table #t1
drop table #t2
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 11, 2013 at 1:52 pm
Thank you so much(when i compare my attempts to your reply its sad.), in the future i will include the data...Again thank you for the introduction to XML(and STUFF)...
July 11, 2013 at 2:03 pm
TryingToLearn (7/11/2013)
Thank you so much(when i compare my attempts to your reply its sad.), in the future i will include the data...Again thank you for the introduction to XML(and STUFF)...
You are quite welcome. Glad that worked for you and thanks for letting us know. Make sure you read and understand that code before you use it in production. It will be your phone that is ringing at 3am when something goes wrong and I will be sound asleep in my bed. 😎
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply