One to many query question

Question

I have the following table structure

Subject

SubjectID int

SubjectDetails (varchar(100)

SubjectType

SubjectTypeId (PK)

SubjectID (FK)

SubjectType (varchar(30)

I want to have this type of result set which is essential one Subject row, with SubjectType(s) as another column comma delimited.

So my result set would look like this

SubjectID            SubjectDetails                   SubjectTypes

1                         test details                    SubjectType1, SubjectType2, SubjectType3

How would I accomplish this in TSQL?

Answer 1

Use FOR XML. See Concatenating Row Values in Transact-SQL.

Answer 2

You can try another way:

create table test (SubjectID int, SubjectDetails  varchar(200), SubjectType varchar(200))
Insert into test values (1,'test details ',' SubjectType1'),(1,'test details ',' SubjectType2'),(1,'test details ',' SubjectType3')
Select SubjectID, s.SubjectDetails, ISNULL([1]+',','') +  ISNULL([2]+',','')+ ISNULL([3] ,'')  as  SubjectTypes  from 
(Select  s.SubjectID, s.SubjectDetails, st.SubjectType
, row_number() Over(partition by s.SubjectID Order By s.SubjectType) rn 
from Subject s 
Inner Join SubjectType st On s.SubjectID=st.SubjectID) src  
Pivot (Max(SubjectType) For rn In ([1],[2],[3])) pvt
 
 
Drop table test

Answer 3

PFB Code

select 1 as Subjectid, 'Hi' as subjectDetails into #Subject
Select 1 as SubjectTypeid,1 as subjectid,'Type1'SubjectType into #Subjecttype union all
Select 2 as SubjectTypeid,1 as subjectid,'Type2'SubjectType union all 
Select 2 as SubjectTypeid,1 as subjectid,'Type2'SubjectType 
 
 
select * ,
 STUFF( 
        ( 
            SELECT 
                SubjectType AS [data()] 
            FROM 
                #Subjecttype ST
            WHERE 
                ST.subjectid = S.subjectid 
            FOR XML PATH ('') 
        ), 1,1,''	) 
from #subject S

Thanks

Saravana Kumar C

Answer 4

I have placed another solution here. Sorry I cant not place T-SQL code here, somehow my browser is not allowing that.

http://www.thesqlpost.blogspot.com/2013/09/sql-server-getting-comma-delimited.html

Ashish.

  

Answer 5

Please, please read a book on RDBMS. The Normal Forms will be taught in the front chapters. This disaster violates First Normal Form (1NF). 

An SQL programmer would do this report formatting in a presentation layer. Never in the database. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL