Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2007
    Posts
    18

    Unanswered: multiple rows into a single row and combining column values SQL Server

    I joined these two tables and it pulled up the proper amount of records. If you check out the image you will see what the results are for this query.

    Now all I need for this part would be to roll these up where I have one row per ProgramID and all the AttributeNames' together in a AttributeNames column for each id.


    EXAMPLE: All in one row.
    ProgramID | AttributeNames
    887 | Studydesign, Control Groups, Primary Outcomes.

    I have attached an image of the SQL VIEW that I need to modified so it does this.



    THE QUERY:
    SELECT TOP (100) PERCENT dbo.tblProgramAttributes.ProgramID, dbo.tblProgramAttributes.AttributeID AS PAattributeID, dbo.tblAttributes.AttributeID,
    dbo.tblAttributes.AttributeName
    FROM dbo.tblProgramAttributes INNER JOIN
    dbo.tblAttributes ON dbo.tblProgramAttributes.AttributeID = dbo.tblAttributes.AttributeID
    WHERE (dbo.tblProgramAttributes.AttributeID NOT LIKE '%ProgramType%')
    ORDER BY dbo.tblProgramAttributes.ProgramID DESC


    Thanks for all your help.
    Attached Thumbnails Attached Thumbnails CSMPG-ProgramsSQLview2.jpg  

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It sounds like you want to concatenate the values from multiple rows in to a single column.

    If so, I would strongly recommend against doing this in SQL. Instead let your presentation layer (form, report, web page, etc) do this piece of work.


    As an aside: remove the ORDER BY clause from your view.
    If you need to specify an order do that on the final select statement e.g.
    Code:
    SELECT list
         , of
         , columns
    FROM   your_view
    ORDER
        BY columns
    Last edited by gvee; 04-08-14 at 05:16.
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2007
    Posts
    18
    I am trying to make this view query two tables and then roll up each Program ID into one row with all the AttributeNames in the AttributeNames column together.


    The reason I am doing this is so this entire operation can be treated as a table to be used in a Stored Procedure with other tables.

    Originally I had 3 tables involved but it became to confusing, it looked like this to do the rollup:

    with mycte AS (SELECT ProgramID,
    Title,
    Reference,
    PrimaryTopicArea,
    Status_ScreenedOut,
    AttributeID,
    AttributeName
    FROM View_RollupProgram_AttributesANDid_With_Descriptio ns2
    )

    SELECT m.ProgramID,
    m.Title,
    m.Reference,
    m.PrimaryTopicArea,
    m.Status_ScreenedOut,

    stuff((select ',' + m2.AttributeName from mycte m2
    WHERE m.ProgramID = m2.ProgramID AND
    m.Title = m2.Title AND
    m.Reference = m2.Reference AND
    m.PrimaryTopicArea = m2.PrimaryTopicArea AND
    m.Status_ScreenedOut = m2.Status_ScreenedOut
    for xml path('')),1,1,'') AttributeNames
    from mycte m
    Group By m.ProgramID,
    m.Title,
    m.Reference,
    m.PrimaryTopicArea,
    m.Status_ScreenedOut


    I am trying to do something simular to this but with the view in this post.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Quote Originally Posted by gvee View Post
    If so, I would strongly recommend doing this in SQL.
    While I am not an expert on GVee's mind, I think he meant to say "strongly recommend not doing this in SQL."

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by MCrowley View Post
    While I am not an expert on GVee's mind, I think he meant to say "strongly recommend not doing this in SQL."
    Woops! Well spotted, sir
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    No worries, G. It happens to all of us.

    As to the original question, why is the concatenation being done on the database server? What is consuming this recordset?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •