CONVERT COLUMN VALUES TO COMMA SEPARATED ONE ROW VALUE..

  • twin.devil

    SSC-Insane

    Points: 22208

    Glad to help.

  • repalaa

    Grasshopper

    Points: 10

    ColdCoffee - Friday, March 2, 2012 12:14 PM

    Similar to Rory's , but doenst require a ROW_NUMBERSELECT OutTab.title ,OutTab.[subtitle] , Cities = STUFF ( ( SELECT ','+InrTab.value FROM [test12] InrTab WHERE InrTab.title = OutTab.title AND InrTab.subtitle = OutTab.subtitle ORDER BY InrTab.value FOR XML PATH(''),TYPE ).value('.','VARCHAR(MAX)') , 1,1,SPACE(0))FROM [test12] OutTabGROUP BY OutTab.title , OutTab.[subtitle] ;

    the examples that are provided are nested select statements, Is there a way that can be moved to a join ? and bring the column from that joined table?

  • drew.allen

    SSC Guru

    Points: 76739

    repalaa - Thursday, January 24, 2019 1:29 PM

    ColdCoffee - Friday, March 2, 2012 12:14 PM

    Similar to Rory's , but doenst require a ROW_NUMBERSELECT OutTab.title ,OutTab.[subtitle] , Cities = STUFF ( ( SELECT ','+InrTab.value FROM [test12] InrTab WHERE InrTab.title = OutTab.title AND InrTab.subtitle = OutTab.subtitle ORDER BY InrTab.value FOR XML PATH(''),TYPE ).value('.','VARCHAR(MAX)') , 1,1,SPACE(0))FROM [test12] OutTabGROUP BY OutTab.title , OutTab.[subtitle] ;

    the examples that are provided are nested select statements, Is there a way that can be moved to a join ? and bring the column from that joined table?

    If you want to replace the subquery with a join, you'll need to use STRING_AGG() in place of the XML concatenation method.  STRING_AGG() is available starting in SQL 2017.

    Drew

    PS: You do realize that this thread is almost six years old.

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • lduvall

    SSCarpal Tunnel

    Points: 4869

    I have a similar problem to the original post, but I've to mimic the solutions provided here and still can't get what I need to achieve. I would really love some help. This is the structure I have:

    CREATE TABLE [dbo].[help](
    [course_module_instance_xref_ident] [int] NULL,
    [question_ordinal] [int] NULL,
    [question_text] [nvarchar](max) NULL,
    [answer_text] [nvarchar](max) NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO
    INSERT [dbo].[help] ([course_module_instance_xref_ident], [question_ordinal], [question_text], [answer_text]) VALUES (18418476, 1, N'Class Name', N'Cupid Non invasive Technologist Echo Vascular Sonographer')
    GO
    INSERT [dbo].[help] ([course_module_instance_xref_ident], [question_ordinal], [question_text], [answer_text]) VALUES (18418476, 2, N'Date (MM/DD/YYYY)', N'09/03/2020')
    GO
    INSERT [dbo].[help] ([course_module_instance_xref_ident], [question_ordinal], [question_text], [answer_text]) VALUES (18418476, 3, N'Instructor Name', N'Jane Doe')
    GO
    INSERT [dbo].[help] ([course_module_instance_xref_ident], [question_ordinal], [question_text], [answer_text]) VALUES (18418476, 4, N'Please rate the ease of connecting to your virtual class', N'Satisfied')
    GO
    INSERT [dbo].[help] ([course_module_instance_xref_ident], [question_ordinal], [question_text], [answer_text]) VALUES (18418476, 5, N'My instructor: Was comfortable with Webex Training platform', N'Very Satisfied')
    GO
    INSERT [dbo].[help] ([course_module_instance_xref_ident], [question_ordinal], [question_text], [answer_text]) VALUES (18418476, 6, N'My instructor: Encouraged participation', N'Very Satisfied')
    GO
    INSERT [dbo].[help] ([course_module_instance_xref_ident], [question_ordinal], [question_text], [answer_text]) VALUES (18418476, 7, N'My instructor: Answered questions appropriately', N'Very Satisfied')
    GO
    INSERT [dbo].[help] ([course_module_instance_xref_ident], [question_ordinal], [question_text], [answer_text]) VALUES (18418476, 8, N'My instructor: Was prepared and organized', N'Very Satisfied')
    GO
    INSERT [dbo].[help] ([course_module_instance_xref_ident], [question_ordinal], [question_text], [answer_text]) VALUES (18418476, 9, N'Please rate your instructor. Other (please specify)', N'Good')
    GO
    INSERT [dbo].[help] ([course_module_instance_xref_ident], [question_ordinal], [question_text], [answer_text]) VALUES (18418476, 10, N'Did you find the length of the class to be appropriate?', N'Yes')
    GO
    INSERT [dbo].[help] ([course_module_instance_xref_ident], [question_ordinal], [question_text], [answer_text]) VALUES (18418476, 11, N'I feel comfortable with the hands on exercises we completed', N'Somewhat Satisfied')
    GO
    INSERT [dbo].[help] ([course_module_instance_xref_ident], [question_ordinal], [question_text], [answer_text]) VALUES (18418476, 12, N'I feel confident in the skills I learned in this class', N'Agree')
    GO
    INSERT [dbo].[help] ([course_module_instance_xref_ident], [question_ordinal], [question_text], [answer_text]) VALUES (18418476, 13, N'Other feeback', N'None')
    GO
    INSERT [dbo].[help] ([course_module_instance_xref_ident], [question_ordinal], [question_text], [answer_text]) VALUES (18637968, 1, N'Class Name', N'NON-INVASIVE TECH PART 1')
    GO
    INSERT [dbo].[help] ([course_module_instance_xref_ident], [question_ordinal], [question_text], [answer_text]) VALUES (18637968, 2, N'Date (MM/DD/YYYY)', N'09/11/2020')
    GO
    INSERT [dbo].[help] ([course_module_instance_xref_ident], [question_ordinal], [question_text], [answer_text]) VALUES (18637968, 3, N'Instructor Name', N'John Doe')
    GO
    INSERT [dbo].[help] ([course_module_instance_xref_ident], [question_ordinal], [question_text], [answer_text]) VALUES (18637968, 4, N'Please rate the ease of connecting to your virtual class', N'Neutral')
    GO
    INSERT [dbo].[help] ([course_module_instance_xref_ident], [question_ordinal], [question_text], [answer_text]) VALUES (18637968, 5, N'My instructor: Was comfortable with Webex Training platform', N'Somewhat Satisfied')
    GO
    INSERT [dbo].[help] ([course_module_instance_xref_ident], [question_ordinal], [question_text], [answer_text]) VALUES (18637968, 6, N'My instructor: Encouraged participation', N'Somewhat Dissatisfied')
    GO
    INSERT [dbo].[help] ([course_module_instance_xref_ident], [question_ordinal], [question_text], [answer_text]) VALUES (18637968, 7, N'My instructor: Answered questions appropriately', N'Neutral')
    GO
    INSERT [dbo].[help] ([course_module_instance_xref_ident], [question_ordinal], [question_text], [answer_text]) VALUES (18637968, 8, N'My instructor: Was prepared and organized', N'Neutral')
    GO
    INSERT [dbo].[help] ([course_module_instance_xref_ident], [question_ordinal], [question_text], [answer_text]) VALUES (18637968, 9, N'Please rate your instructor. Other (please specify)', N'NEUTRAL. NEEDS TO BE A PERSONAL INSTRUCTOR FOR THIS TRAINING.')
    GO
    INSERT [dbo].[help] ([course_module_instance_xref_ident], [question_ordinal], [question_text], [answer_text]) VALUES (18637968, 10, N'Did you find the length of the class to be appropriate?', N'No')
    GO
    INSERT [dbo].[help] ([course_module_instance_xref_ident], [question_ordinal], [question_text], [answer_text]) VALUES (18637968, 11, N'I feel comfortable with the hands on exercises we completed', N'Very Dissatisfied')
    GO
    INSERT [dbo].[help] ([course_module_instance_xref_ident], [question_ordinal], [question_text], [answer_text]) VALUES (18637968, 12, N'I feel confident in the skills I learned in this class', N'Disgree')
    GO
    INSERT [dbo].[help] ([course_module_instance_xref_ident], [question_ordinal], [question_text], [answer_text]) VALUES (18637968, 13, N'Other feeback', N'NEEDED ADDITIONAL ACTIVATES.')
    GO

    The required output (which is idiotic but required) is

    18418476, "Class Name","Cupid Non invasive Technologist Echo Vascular Sonographer","Date (MM/DD/YYYY)","09/03/2020","Instructor Name","Jane Doe","Please rate the ease of connecting to your virtual class","Satisfied",

    "My instructor: Was comfortable with Webex Training platform","Very Satisfied","My instructor: Encouraged participation","Very Satisfied","My instructor: Answered questions appropriately","Very Satisfied","My instructor: Was prepared and organized","Very Satisfied",Please rate your instructor. Other (please specify)","Did you find the length of the class to be appropriate?","Yes","I feel comfortable with the hands on exercises we completed","Somewhat Satisfied,"I feel confident in the skills I learned in this class","Other feeback","None"

    18637968,"Class Name","NON-INVASIVE TECH PART 1","Date (MM/DD/YYYY)","09/11/2020", etc...

     

    In other words, they're wanting:

    course_instance_xref_ident, Q1, A1, Q2,A2,Q3,A3,Q4,A4,Q5,A5,Q6,A6,Q7,A7,Q8,A8,Q9,A9,Q10,A10,Q11,A11,Q12,A12,Q13,A13

     

     

  • Jeff Moden

    SSC Guru

    Points: 997144

    You've posted in a 2005 forum.  What version of SQL Server do you actually have?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • lduvall

    SSCarpal Tunnel

    Points: 4869

    I apologize. I'm using SQL Server 2017 Standard Edition.

  • Jason A. Long

    SSC-Insane

    Points: 23711

    lduvall wrote:

    I apologize. I'm using SQL Server 2017 Standard Edition.

    That means you can use the new(ish) STRING_AGG() function... Give this a shot.

    SELECT 
    CONCAT(
    h.course_module_instance_xref_ident, N', ',
    STRING_AGG(CONCAT(N'"', h.question_text, N'", "', h.answer_text, N'"'), N', ') WITHIN GROUP (ORDER BY h.question_ordinal ASC)
    )
    FROM
    dbo.help h
    GROUP BY
    h.course_module_instance_xref_ident;

    Here are the results...

    18418476, "Class Name", "Cupid Non invasive Technologist Echo Vascular Sonographer", "Date (MM/DD/YYYY)", "09/03/2020", "Instructor Name", "Jane Doe", "Please rate the ease of connecting to your virtual class", "Satisfied", "My instructor:  Was comfortable with Webex Training platform", "Very Satisfied", "My instructor:  Encouraged participation", "Very Satisfied", "My instructor:  Answered questions appropriately", "Very Satisfied", "My instructor:  Was prepared and organized", "Very Satisfied", "Please rate your instructor. Other (please specify)", "Good", "Did you find the length of the class to be appropriate?", "Yes", "I feel comfortable with the hands on exercises we completed", "Somewhat Satisfied", "I feel confident in the skills I learned in this class", "Agree", "Other feeback", "None"

    18637968, "Class Name", "NON-INVASIVE TECH PART 1", "Date (MM/DD/YYYY)", "09/11/2020", "Instructor Name", "John Doe", "Please rate the ease of connecting to your virtual class", "Neutral", "My instructor: Was comfortable with Webex Training platform", "Somewhat Satisfied", "My instructor: Encouraged participation", "Somewhat Dissatisfied", "My instructor: Answered questions appropriately", "Neutral", "My instructor: Was prepared and organized", "Neutral", "Please rate your instructor. Other (please specify)", "NEUTRAL. NEEDS TO BE A PERSONAL INSTRUCTOR FOR THIS TRAINING.", "Did you find the length of the class to be appropriate?", "No", "I feel comfortable with the hands on exercises we completed", "Very Dissatisfied", "I feel confident in the skills I learned in this class", "Disgree", "Other feeback", "NEEDED ADDITIONAL ACTIVATES."

     

    Edit: Let us know if you aren't sure how or why this works.

  • lduvall

    SSCarpal Tunnel

    Points: 4869

    Thank you SO much. These are indeed the results in the structure I need them. I'm going to research that syntax and see if I can teach  myself why it works the way it does. I'm very, very grateful.

    Leigh Anne

  • Jason A. Long

    SSC-Insane

    Points: 23711

    lduvall wrote:

    Thank you SO much. These are indeed the results in the structure I need them. I'm going to research that syntax and see if I can teach  myself why it works the way it does. I'm very, very grateful.

    Leigh Anne

    It's not a problem. Glad to help.

    The STRING_AGG() function is the "secret sauce" in the solution. Everything else is just simple string concatenation.

  • Jeff Moden

    SSC Guru

    Points: 997144

    drew.allen wrote:

    PS: You do realize that this thread is almost six years old.

     

    An updated answer to an old question is still a good thing.  Someone has already benefited from it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply