June 30, 2014 at 3:44 am
Glad to help.
January 24, 2019 at 1:29 pm
ColdCoffee - Friday, March 2, 2012 12:14 PMSimilar to Rory's , but doenst require a ROW_NUMBERSELECT OutTab.title ,OutTab.[subtitle] , Cities = STUFF ( ( SELECT ','+InrTab.valueFROM [test12] InrTabWHERE InrTab.title = OutTab.title AND InrTab.subtitle = OutTab.subtitleORDER BY InrTab.valueFOR 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?
January 24, 2019 at 2:12 pm
repalaa - Thursday, January 24, 2019 1:29 PMColdCoffee - Friday, March 2, 2012 12:14 PMSimilar to Rory's , but doenst require a ROW_NUMBERSELECT OutTab.title ,OutTab.[subtitle] , Cities = STUFF ( ( SELECT ','+InrTab.valueFROM [test12] InrTabWHERE InrTab.title = OutTab.title AND InrTab.subtitle = OutTab.subtitleORDER BY InrTab.valueFOR 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
September 15, 2020 at 7:44 pm
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
September 15, 2020 at 7:55 pm
You've posted in a 2005 forum. What version of SQL Server do you actually have?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 15, 2020 at 8:18 pm
I apologize. I'm using SQL Server 2017 Standard Edition.
September 15, 2020 at 8:59 pm
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.
September 15, 2020 at 9:29 pm
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
September 15, 2020 at 9:36 pm
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.
September 15, 2020 at 9:45 pm
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
Change is inevitable... Change for the better is not.
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply