One to many query question RRS feed

  • Question

  • I have the following table structure


    SubjectID int

    SubjectDetails (varchar(100)


    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?

    Tuesday, September 24, 2013 6:08 PM


All replies

    • Marked as answer by spark29er Tuesday, September 24, 2013 9:41 PM
    Tuesday, September 24, 2013 6:17 PM
  • 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

    Tuesday, September 24, 2013 8:57 PM
  • 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 * ,
                    SubjectType AS [data()] 
                    #Subjecttype ST
                    ST.subjectid = S.subjectid 
                FOR XML PATH ('') 
            ), 1,1,''	) 
    from #subject S


    Saravana Kumar C

    Tuesday, September 24, 2013 9:11 PM
  • I have placed another solution here. Sorry I cant not place T-SQL code here, somehow my browser is not allowing that.



    • Proposed as answer by Ashish.Upadhyay Tuesday, September 24, 2013 9:52 PM
    Tuesday, September 24, 2013 9:52 PM
  • 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

    Tuesday, September 24, 2013 10:00 PM