TSQL - Combine from one table into another with single row

Question

I have three tables as outlined below.

tblNews

NewsId | Title       |
======================
1      | Hello World |

tblSectionItems

rID  | SectionID | NewsID     |
=================================
 1   |   1       | 1          |
 2   |   2       | 1          |

tblSections

SectionId | SectionName | 
===================================
1         | Economy     |
2         | Politics    |

How would I go about and have a single query to output like the following? Or even better How can I do this with Linq - EF?

| NewsID | Title        | SectionNames
=====================================
| 1      | Hello World  | Economy, Politics   |

What I have tried up until now are with using JOINS which in turn outputs something like

| NewsID | Title        | SectionNames
=====================================
| 1      | Hello World  | Economy   |
| 1      | Hello World  | Politics  |

which is not a desired output.

Any help or direction to an article or a tutorial will be appreciated. Thanks..

Possible duplicate http://stackoverflow.com/questions/16525064/sql-server-find-duplicates-in-a-table-based-on-values-in-a-single-column/16525236#16525236 — EricZ, Jun 18 '13 at 13:59

score 0 · Accepted Answer · answered Jun 18 '13 at 14:17

0

Something like

select
 NewsID,
 Title,
 STUFF((SELECT ', '+ s.SectionName
  FROM tblSectionItems si
  inner join tblSections s on s.SectionId=si.SectionId
  WHERE si.NewsID= n.NewsID
  For XML PATH('')
 ),1,2,'') as IDs
FROM tblNews n

answered Jun 18 '13 at 14:17

Alexander Sigachov

1,541
11
16

Worked perfectly well.. Thank you very much @Alexander. In the meantime, I will go and read about that STUFF and XML PATH as I have never seen them before (not an sql guy I am..) – Subliminal Hash Jun 18 '13 at 14:21
STUFF is ordinar string function, but "For XML PATH('')" is ugly magic trick here, but it's often used for table-to-list operation – Alexander Sigachov Jun 18 '13 at 14:26
Realized that when reading about them, but unfortunately Entity Framework has no support for "For XML PATH('').. I have created a SP with your code and will see if I can use it with EF.. Thanks again a lot. – Subliminal Hash Jun 18 '13 at 14:44

TSQL - Combine from one table into another with single row

1 Answers1