Mixing
SQL Server Management Studio concatenate multiple rows using FOR XML PATH
0
I'm having again a bit of trouble implementing the XML PATH method of concatenating multiple rows.
Given the following SQL Server temptable:
project | NAME | display_name
-----------+------------+-------------------
a Developer Amelia
a Developer Ruby
a Administrator Olivia
b User Isla
b Developer Emily
c User Poppy
c Administrator Ava
c Administrator James
The result should be:
project | NAME | display_name
-----------+----------------+----------------
a Developer Amelia, Ruby
Administrator Olivia
b User Isla
Developer Emily
c User Poppy
Administrator Ava,James
Is something like this even possible with XML PATH etc. ?
sql-server edited Nov 19 '18 at 14:47
marc_s
571k12811031251
asked Nov 19 '18 at 10:16
happenz
102
add a comment |
0
I'm having again a bit of trouble implementing the XML PATH method of concatenating multiple rows.
Given the following SQL Server temptable:
project | NAME | display_name
-----------+------------+-------------------
a Developer Amelia
a Developer Ruby
a Administrator Olivia
b User Isla
b Developer Emily
c User Poppy
c Administrator Ava
c Administrator James
The result should be:
project | NAME | display_name
-----------+----------------+----------------
a Developer Amelia, Ruby
Administrator Olivia
b User Isla
Developer Emily
c User Poppy
Administrator Ava,James
Is something like this even possible with XML PATH etc. ?
sql-server edited Nov 19 '18 at 14:47
marc_s
571k12811031251
asked Nov 19 '18 at 10:16
happenz
102
Your query is a simple case of the usual solution. Could you post it to see what's going wrong?
– George Menoutis
Nov 19 '18 at 10:18
add a comment |
0
0
0
I'm having again a bit of trouble implementing the XML PATH method of concatenating multiple rows.
Given the following SQL Server temptable:
project | NAME | display_name
-----------+------------+-------------------
a Developer Amelia
a Developer Ruby
a Administrator Olivia
b User Isla
b Developer Emily
c User Poppy
c Administrator Ava
c Administrator James
The result should be:
project | NAME | display_name
-----------+----------------+----------------
a Developer Amelia, Ruby
Administrator Olivia
b User Isla
Developer Emily
c User Poppy
Administrator Ava,James
Is something like this even possible with XML PATH etc. ?
sql-server edited Nov 19 '18 at 14:47
marc_s
571k12811031251
asked Nov 19 '18 at 10:16
happenz
102
I'm having again a bit of trouble implementing the XML PATH method of concatenating multiple rows.
Given the following SQL Server temptable:
project | NAME | display_name
-----------+------------+-------------------
a Developer Amelia
a Developer Ruby
a Administrator Olivia
b User Isla
b Developer Emily
c User Poppy
c Administrator Ava
c Administrator James
The result should be:
project | NAME | display_name
-----------+----------------+----------------
a Developer Amelia, Ruby
Administrator Olivia
b User Isla
Developer Emily
c User Poppy
Administrator Ava,James
Is something like this even possible with XML PATH etc. ?
sql-server sql-server edited Nov 19 '18 at 14:47
marc_s
571k12811031251
asked Nov 19 '18 at 10:16
happenz
102
edited Nov 19 '18 at 14:47
marc_s
571k12811031251
asked Nov 19 '18 at 10:16
happenz
102
edited Nov 19 '18 at 14:47
marc_s
571k12811031251
edited Nov 19 '18 at 14:47
marc_s
571k12811031251
edited Nov 19 '18 at 14:47
marc_s
571k12811031251
571k12811031251
asked Nov 19 '18 at 10:16
happenz
102
asked Nov 19 '18 at 10:16
happenz
102
asked Nov 19 '18 at 10:16
happenz
102
102
Your query is a simple case of the usual solution. Could you post it to see what's going wrong?
– George Menoutis
Nov 19 '18 at 10:18
add a comment |
Your query is a simple case of the usual solution. Could you post it to see what's going wrong?
– George Menoutis
Nov 19 '18 at 10:18
Your query is a simple case of the usual solution. Could you post it to see what's going wrong?
– George Menoutis
Nov 19 '18 at 10:18
Your query is a simple case of the usual solution. Could you post it to see what's going wrong?
– George Menoutis
Nov 19 '18 at 10:18
add a comment |
1 Answer
1
active
oldest
votes
1
Yes you can use FOR XML PATH() clause :
SELECT DISTINCT t.project, t.name, STUFF(t1.display_name, 1, 1, '') AS display_name
FROM table t CROSS APPLY
(SELECT ', '+t1.display_name
FROM table t1
WHERE t1.project = t.project AND t1.name = t.name
FOR XML PATH('')
) t1(display_name);
answered Nov 19 '18 at 10:22
Yogesh Sharma
28.3k51335
I like your sintaxis of calling the subquery on a cross apply and removing the trailing , on the result. Much cleaner, I will use it.
– Marc Guillot
Nov 19 '18 at 10:26
@Yogesh Sharma thank you this works perfectly!
– happenz
Nov 19 '18 at 10:42
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53372442%2fsql-server-management-studio-concatenate-multiple-rows-using-for-xml-path%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Yes you can use FOR XML PATH() clause :
SELECT DISTINCT t.project, t.name, STUFF(t1.display_name, 1, 1, '') AS display_name
FROM table t CROSS APPLY
(SELECT ', '+t1.display_name
FROM table t1
WHERE t1.project = t.project AND t1.name = t.name
FOR XML PATH('')
) t1(display_name);
answered Nov 19 '18 at 10:22
Yogesh Sharma
28.3k51335
I like your sintaxis of calling the subquery on a cross apply and removing the trailing , on the result. Much cleaner, I will use it.
– Marc Guillot
Nov 19 '18 at 10:26
@Yogesh Sharma thank you this works perfectly!
– happenz
Nov 19 '18 at 10:42
add a comment |
1
Yes you can use FOR XML PATH() clause :
SELECT DISTINCT t.project, t.name, STUFF(t1.display_name, 1, 1, '') AS display_name
FROM table t CROSS APPLY
(SELECT ', '+t1.display_name
FROM table t1
WHERE t1.project = t.project AND t1.name = t.name
FOR XML PATH('')
) t1(display_name);
answered Nov 19 '18 at 10:22
Yogesh Sharma
28.3k51335
I like your sintaxis of calling the subquery on a cross apply and removing the trailing , on the result. Much cleaner, I will use it.
– Marc Guillot
Nov 19 '18 at 10:26
@Yogesh Sharma thank you this works perfectly!
– happenz
Nov 19 '18 at 10:42
add a comment |
1
1
1
Yes you can use FOR XML PATH() clause :
SELECT DISTINCT t.project, t.name, STUFF(t1.display_name, 1, 1, '') AS display_name
FROM table t CROSS APPLY
(SELECT ', '+t1.display_name
FROM table t1
WHERE t1.project = t.project AND t1.name = t.name
FOR XML PATH('')
) t1(display_name);
answered Nov 19 '18 at 10:22
Yogesh Sharma
28.3k51335
Yes you can use FOR XML PATH() clause :
SELECT DISTINCT t.project, t.name, STUFF(t1.display_name, 1, 1, '') AS display_name
FROM table t CROSS APPLY
(SELECT ', '+t1.display_name
FROM table t1
WHERE t1.project = t.project AND t1.name = t.name
FOR XML PATH('')
) t1(display_name);
answered Nov 19 '18 at 10:22
Yogesh Sharma
28.3k51335
answered Nov 19 '18 at 10:22
Yogesh Sharma
28.3k51335
answered Nov 19 '18 at 10:22
Yogesh Sharma
28.3k51335
answered Nov 19 '18 at 10:22
Yogesh Sharma
28.3k51335
28.3k51335
I like your sintaxis of calling the subquery on a cross apply and removing the trailing , on the result. Much cleaner, I will use it.
– Marc Guillot
Nov 19 '18 at 10:26
@Yogesh Sharma thank you this works perfectly!
– happenz
Nov 19 '18 at 10:42
add a comment |
I like your sintaxis of calling the subquery on a cross apply and removing the trailing , on the result. Much cleaner, I will use it.
– Marc Guillot
Nov 19 '18 at 10:26
@Yogesh Sharma thank you this works perfectly!
– happenz
Nov 19 '18 at 10:42
I like your sintaxis of calling the subquery on a cross apply and removing the trailing , on the result. Much cleaner, I will use it.
– Marc Guillot
Nov 19 '18 at 10:26
I like your sintaxis of calling the subquery on a cross apply and removing the trailing , on the result. Much cleaner, I will use it.
– Marc Guillot
Nov 19 '18 at 10:26
@Yogesh Sharma thank you this works perfectly!
– happenz
Nov 19 '18 at 10:42
@Yogesh Sharma thank you this works perfectly!
– happenz
Nov 19 '18 at 10:42
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53372442%2fsql-server-management-studio-concatenate-multiple-rows-using-for-xml-path%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Your query is a simple case of the usual solution. Could you post it to see what's going wrong?
– George Menoutis
Nov 19 '18 at 10:18