Learning Pivoting in TSQL
I feel that this should be simple, but all the pivots I find seem to be more complicated than what I am looking for, so any help or re-direction would be much appreciated.
I have ‘ID_code’ and ‘product_name’ and I am looking for mismatched product names and have them put next to each other in a row as opposed to in a column like this:
Select distinct ID_Code, product_name
From table
Where ID_Code in
(Select ID_Code from table
Group by ID_Code
Having count(distinct product_name) <> 1)
I would like a table set out as
ID_Code Product_name1 Product_name2 Product_name3
Thanks very much, and have a Happy New Year!
tsql pivot
asked Dec 31 '18 at 13:59
Big_DBig_D
102
add a comment |
I feel that this should be simple, but all the pivots I find seem to be more complicated than what I am looking for, so any help or re-direction would be much appreciated.
I have ‘ID_code’ and ‘product_name’ and I am looking for mismatched product names and have them put next to each other in a row as opposed to in a column like this:
Select distinct ID_Code, product_name
From table
Where ID_Code in
(Select ID_Code from table
Group by ID_Code
Having count(distinct product_name) <> 1)
I would like a table set out as
ID_Code Product_name1 Product_name2 Product_name3
Thanks very much, and have a Happy New Year!
tsql pivot
asked Dec 31 '18 at 13:59
Big_DBig_D
102
add a comment |
I feel that this should be simple, but all the pivots I find seem to be more complicated than what I am looking for, so any help or re-direction would be much appreciated.
I have ‘ID_code’ and ‘product_name’ and I am looking for mismatched product names and have them put next to each other in a row as opposed to in a column like this:
Select distinct ID_Code, product_name
From table
Where ID_Code in
(Select ID_Code from table
Group by ID_Code
Having count(distinct product_name) <> 1)
I would like a table set out as
ID_Code Product_name1 Product_name2 Product_name3
Thanks very much, and have a Happy New Year!
tsql pivot
asked Dec 31 '18 at 13:59
Big_DBig_D
102
I feel that this should be simple, but all the pivots I find seem to be more complicated than what I am looking for, so any help or re-direction would be much appreciated.
I have ‘ID_code’ and ‘product_name’ and I am looking for mismatched product names and have them put next to each other in a row as opposed to in a column like this:
Select distinct ID_Code, product_name
From table
Where ID_Code in
(Select ID_Code from table
Group by ID_Code
Having count(distinct product_name) <> 1)
I would like a table set out as
ID_Code Product_name1 Product_name2 Product_name3
Thanks very much, and have a Happy New Year!
tsql pivot
tsql pivot
asked Dec 31 '18 at 13:59
Big_DBig_D
102
asked Dec 31 '18 at 13:59
Big_DBig_D
102
asked Dec 31 '18 at 13:59
Big_DBig_D
102
asked Dec 31 '18 at 13:59
Big_DBig_D
102
asked Dec 31 '18 at 13:59
Big_DBig_D
102
102
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
This should remove the duplicates but still returns one result if the product_name has a match.
;with testdata as(
SELECT '1' as ID_Code, 'bike' as product_name
UNION ALL SELECT '1', 'biker'
UNION ALL SELECT '1', 'bike'
UNION ALL SELECT '2', 'motorbike'
UNION ALL SELECT '2', 'motorbike'
UNION ALL SELECT '2', 'motorbike'
UNION ALL SELECT '2', 'motrbike'
UNION ALL SELECT '2', 'motorbiker'
)
--added this section to return distinct products
,cte as(
SELECT * FROM testdata d1
INTERSECT
SELECT * FROM testdata d2
)
SELECT --DISTINCT --Use DISTINCT here if need to return just one line per ID_Code
ID_Code
,product_name = STUFF((SELECT ', ' +
--Added this to track product_names for each ID_Code
t2.product_name + '_' + cast(ROW_NUMBER() OVER (PARTITION BY ID_Code ORDER BY product_name) as varchar(100))
FROM cte t2
WHERE t2.ID_Code = cte.ID_Code
FOR XML PATH('')), 1, 2, '')
FROM cte
Example here: db<>fiddle
More info about INTERSECT should this not be what works in this scenario.
answered Dec 31 '18 at 18:22
Marc0Marc0
1116
add a comment |
Your expected output appears to be somewhat inflexible, because we may not know exactly how many columns/products would be needed. Instead, I recommend and rolling up the mismatched products into a CSV string for output.
SELECT
ID_Code,
STUFF((SELECT ',' + t2.product_name
FROM yourTable t2
WHERE t1.ID_Code = t2.ID_Code
FOR XML PATH('')), 1, 1, '') products
FROM your_table t1
GROUP BY
ID_Code
HAVING
MIN(product_name) <> MAX(product_name); -- index friendly
Demo
answered Dec 31 '18 at 14:05
Tim BiegeleisenTim Biegeleisen
225k1391143
Thanks for your reply. However, SQL is telling me I have an incorrect syntax near the keyword Order, in your answer. Also is it possible to get the answers in separate columns so that I can compare them? In reality, there will be under 4 product names. Some would be null when there is only 1 mismatch.
– Big_D
Dec 31 '18 at 14:34
@Big_D Try usingARRAY_AGGinstead, q.v. my updated answer. I don't recommend the separate column idea.
– Tim Biegeleisen
Dec 31 '18 at 14:37
Unfortunately my sql doesn’t have that function. Will look at getting it later. Why is separate columns a bad idea? Thanks for all your feedback!
– Big_D
Dec 31 '18 at 15:13
@Big_D I updated my answer with SQL Server code. For whatever reason, I thought you were using Postgres. My query should run now.
– Tim Biegeleisen
Dec 31 '18 at 15:22
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
});
}
});
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%2f53988294%2flearning-pivoting-in-tsql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
This should remove the duplicates but still returns one result if the product_name has a match.
;with testdata as(
SELECT '1' as ID_Code, 'bike' as product_name
UNION ALL SELECT '1', 'biker'
UNION ALL SELECT '1', 'bike'
UNION ALL SELECT '2', 'motorbike'
UNION ALL SELECT '2', 'motorbike'
UNION ALL SELECT '2', 'motorbike'
UNION ALL SELECT '2', 'motrbike'
UNION ALL SELECT '2', 'motorbiker'
)
--added this section to return distinct products
,cte as(
SELECT * FROM testdata d1
INTERSECT
SELECT * FROM testdata d2
)
SELECT --DISTINCT --Use DISTINCT here if need to return just one line per ID_Code
ID_Code
,product_name = STUFF((SELECT ', ' +
--Added this to track product_names for each ID_Code
t2.product_name + '_' + cast(ROW_NUMBER() OVER (PARTITION BY ID_Code ORDER BY product_name) as varchar(100))
FROM cte t2
WHERE t2.ID_Code = cte.ID_Code
FOR XML PATH('')), 1, 2, '')
FROM cte
Example here: db<>fiddle
More info about INTERSECT should this not be what works in this scenario.
answered Dec 31 '18 at 18:22
Marc0Marc0
1116
add a comment |
This should remove the duplicates but still returns one result if the product_name has a match.
;with testdata as(
SELECT '1' as ID_Code, 'bike' as product_name
UNION ALL SELECT '1', 'biker'
UNION ALL SELECT '1', 'bike'
UNION ALL SELECT '2', 'motorbike'
UNION ALL SELECT '2', 'motorbike'
UNION ALL SELECT '2', 'motorbike'
UNION ALL SELECT '2', 'motrbike'
UNION ALL SELECT '2', 'motorbiker'
)
--added this section to return distinct products
,cte as(
SELECT * FROM testdata d1
INTERSECT
SELECT * FROM testdata d2
)
SELECT --DISTINCT --Use DISTINCT here if need to return just one line per ID_Code
ID_Code
,product_name = STUFF((SELECT ', ' +
--Added this to track product_names for each ID_Code
t2.product_name + '_' + cast(ROW_NUMBER() OVER (PARTITION BY ID_Code ORDER BY product_name) as varchar(100))
FROM cte t2
WHERE t2.ID_Code = cte.ID_Code
FOR XML PATH('')), 1, 2, '')
FROM cte
Example here: db<>fiddle
More info about INTERSECT should this not be what works in this scenario.
answered Dec 31 '18 at 18:22
Marc0Marc0
1116
add a comment |
This should remove the duplicates but still returns one result if the product_name has a match.
;with testdata as(
SELECT '1' as ID_Code, 'bike' as product_name
UNION ALL SELECT '1', 'biker'
UNION ALL SELECT '1', 'bike'
UNION ALL SELECT '2', 'motorbike'
UNION ALL SELECT '2', 'motorbike'
UNION ALL SELECT '2', 'motorbike'
UNION ALL SELECT '2', 'motrbike'
UNION ALL SELECT '2', 'motorbiker'
)
--added this section to return distinct products
,cte as(
SELECT * FROM testdata d1
INTERSECT
SELECT * FROM testdata d2
)
SELECT --DISTINCT --Use DISTINCT here if need to return just one line per ID_Code
ID_Code
,product_name = STUFF((SELECT ', ' +
--Added this to track product_names for each ID_Code
t2.product_name + '_' + cast(ROW_NUMBER() OVER (PARTITION BY ID_Code ORDER BY product_name) as varchar(100))
FROM cte t2
WHERE t2.ID_Code = cte.ID_Code
FOR XML PATH('')), 1, 2, '')
FROM cte
Example here: db<>fiddle
More info about INTERSECT should this not be what works in this scenario.
answered Dec 31 '18 at 18:22
Marc0Marc0
1116
This should remove the duplicates but still returns one result if the product_name has a match.
;with testdata as(
SELECT '1' as ID_Code, 'bike' as product_name
UNION ALL SELECT '1', 'biker'
UNION ALL SELECT '1', 'bike'
UNION ALL SELECT '2', 'motorbike'
UNION ALL SELECT '2', 'motorbike'
UNION ALL SELECT '2', 'motorbike'
UNION ALL SELECT '2', 'motrbike'
UNION ALL SELECT '2', 'motorbiker'
)
--added this section to return distinct products
,cte as(
SELECT * FROM testdata d1
INTERSECT
SELECT * FROM testdata d2
)
SELECT --DISTINCT --Use DISTINCT here if need to return just one line per ID_Code
ID_Code
,product_name = STUFF((SELECT ', ' +
--Added this to track product_names for each ID_Code
t2.product_name + '_' + cast(ROW_NUMBER() OVER (PARTITION BY ID_Code ORDER BY product_name) as varchar(100))
FROM cte t2
WHERE t2.ID_Code = cte.ID_Code
FOR XML PATH('')), 1, 2, '')
FROM cte
Example here: db<>fiddle
More info about INTERSECT should this not be what works in this scenario.
answered Dec 31 '18 at 18:22
Marc0Marc0
1116
edited Jan 2 at 16:50
answered Dec 31 '18 at 18:22
Marc0Marc0
1116
answered Dec 31 '18 at 18:22
Marc0Marc0
1116
answered Dec 31 '18 at 18:22
Marc0Marc0
1116
1116
add a comment |
add a comment |
Your expected output appears to be somewhat inflexible, because we may not know exactly how many columns/products would be needed. Instead, I recommend and rolling up the mismatched products into a CSV string for output.
SELECT
ID_Code,
STUFF((SELECT ',' + t2.product_name
FROM yourTable t2
WHERE t1.ID_Code = t2.ID_Code
FOR XML PATH('')), 1, 1, '') products
FROM your_table t1
GROUP BY
ID_Code
HAVING
MIN(product_name) <> MAX(product_name); -- index friendly
Demo
answered Dec 31 '18 at 14:05
Tim BiegeleisenTim Biegeleisen
225k1391143
Thanks for your reply. However, SQL is telling me I have an incorrect syntax near the keyword Order, in your answer. Also is it possible to get the answers in separate columns so that I can compare them? In reality, there will be under 4 product names. Some would be null when there is only 1 mismatch.
– Big_D
Dec 31 '18 at 14:34
@Big_D Try usingARRAY_AGGinstead, q.v. my updated answer. I don't recommend the separate column idea.
– Tim Biegeleisen
Dec 31 '18 at 14:37
Unfortunately my sql doesn’t have that function. Will look at getting it later. Why is separate columns a bad idea? Thanks for all your feedback!
– Big_D
Dec 31 '18 at 15:13
@Big_D I updated my answer with SQL Server code. For whatever reason, I thought you were using Postgres. My query should run now.
– Tim Biegeleisen
Dec 31 '18 at 15:22
add a comment |
Your expected output appears to be somewhat inflexible, because we may not know exactly how many columns/products would be needed. Instead, I recommend and rolling up the mismatched products into a CSV string for output.
SELECT
ID_Code,
STUFF((SELECT ',' + t2.product_name
FROM yourTable t2
WHERE t1.ID_Code = t2.ID_Code
FOR XML PATH('')), 1, 1, '') products
FROM your_table t1
GROUP BY
ID_Code
HAVING
MIN(product_name) <> MAX(product_name); -- index friendly
Demo
answered Dec 31 '18 at 14:05
Tim BiegeleisenTim Biegeleisen
225k1391143
Thanks for your reply. However, SQL is telling me I have an incorrect syntax near the keyword Order, in your answer. Also is it possible to get the answers in separate columns so that I can compare them? In reality, there will be under 4 product names. Some would be null when there is only 1 mismatch.
– Big_D
Dec 31 '18 at 14:34
@Big_D Try usingARRAY_AGGinstead, q.v. my updated answer. I don't recommend the separate column idea.
– Tim Biegeleisen
Dec 31 '18 at 14:37
Unfortunately my sql doesn’t have that function. Will look at getting it later. Why is separate columns a bad idea? Thanks for all your feedback!
– Big_D
Dec 31 '18 at 15:13
@Big_D I updated my answer with SQL Server code. For whatever reason, I thought you were using Postgres. My query should run now.
– Tim Biegeleisen
Dec 31 '18 at 15:22
add a comment |
Your expected output appears to be somewhat inflexible, because we may not know exactly how many columns/products would be needed. Instead, I recommend and rolling up the mismatched products into a CSV string for output.
SELECT
ID_Code,
STUFF((SELECT ',' + t2.product_name
FROM yourTable t2
WHERE t1.ID_Code = t2.ID_Code
FOR XML PATH('')), 1, 1, '') products
FROM your_table t1
GROUP BY
ID_Code
HAVING
MIN(product_name) <> MAX(product_name); -- index friendly
Demo
answered Dec 31 '18 at 14:05
Tim BiegeleisenTim Biegeleisen
225k1391143
Your expected output appears to be somewhat inflexible, because we may not know exactly how many columns/products would be needed. Instead, I recommend and rolling up the mismatched products into a CSV string for output.
SELECT
ID_Code,
STUFF((SELECT ',' + t2.product_name
FROM yourTable t2
WHERE t1.ID_Code = t2.ID_Code
FOR XML PATH('')), 1, 1, '') products
FROM your_table t1
GROUP BY
ID_Code
HAVING
MIN(product_name) <> MAX(product_name); -- index friendly
Demo
answered Dec 31 '18 at 14:05
Tim BiegeleisenTim Biegeleisen
225k1391143
edited Dec 31 '18 at 15:18
answered Dec 31 '18 at 14:05
Tim BiegeleisenTim Biegeleisen
225k1391143
answered Dec 31 '18 at 14:05
Tim BiegeleisenTim Biegeleisen
225k1391143
answered Dec 31 '18 at 14:05
Tim BiegeleisenTim Biegeleisen
225k1391143
225k1391143
Thanks for your reply. However, SQL is telling me I have an incorrect syntax near the keyword Order, in your answer. Also is it possible to get the answers in separate columns so that I can compare them? In reality, there will be under 4 product names. Some would be null when there is only 1 mismatch.
– Big_D
Dec 31 '18 at 14:34
@Big_D Try usingARRAY_AGGinstead, q.v. my updated answer. I don't recommend the separate column idea.
– Tim Biegeleisen
Dec 31 '18 at 14:37
Unfortunately my sql doesn’t have that function. Will look at getting it later. Why is separate columns a bad idea? Thanks for all your feedback!
– Big_D
Dec 31 '18 at 15:13
@Big_D I updated my answer with SQL Server code. For whatever reason, I thought you were using Postgres. My query should run now.
– Tim Biegeleisen
Dec 31 '18 at 15:22
add a comment |
Thanks for your reply. However, SQL is telling me I have an incorrect syntax near the keyword Order, in your answer. Also is it possible to get the answers in separate columns so that I can compare them? In reality, there will be under 4 product names. Some would be null when there is only 1 mismatch.
– Big_D
Dec 31 '18 at 14:34
@Big_D Try usingARRAY_AGGinstead, q.v. my updated answer. I don't recommend the separate column idea.
– Tim Biegeleisen
Dec 31 '18 at 14:37
Unfortunately my sql doesn’t have that function. Will look at getting it later. Why is separate columns a bad idea? Thanks for all your feedback!
– Big_D
Dec 31 '18 at 15:13
@Big_D I updated my answer with SQL Server code. For whatever reason, I thought you were using Postgres. My query should run now.
– Tim Biegeleisen
Dec 31 '18 at 15:22
Thanks for your reply. However, SQL is telling me I have an incorrect syntax near the keyword Order, in your answer. Also is it possible to get the answers in separate columns so that I can compare them? In reality, there will be under 4 product names. Some would be null when there is only 1 mismatch.
– Big_D
Dec 31 '18 at 14:34
Thanks for your reply. However, SQL is telling me I have an incorrect syntax near the keyword Order, in your answer. Also is it possible to get the answers in separate columns so that I can compare them? In reality, there will be under 4 product names. Some would be null when there is only 1 mismatch.
– Big_D
Dec 31 '18 at 14:34
@Big_D Try using
ARRAY_AGG instead, q.v. my updated answer. I don't recommend the separate column idea.– Tim Biegeleisen
Dec 31 '18 at 14:37
@Big_D Try using
ARRAY_AGG instead, q.v. my updated answer. I don't recommend the separate column idea.– Tim Biegeleisen
Dec 31 '18 at 14:37
Unfortunately my sql doesn’t have that function. Will look at getting it later. Why is separate columns a bad idea? Thanks for all your feedback!
– Big_D
Dec 31 '18 at 15:13
Unfortunately my sql doesn’t have that function. Will look at getting it later. Why is separate columns a bad idea? Thanks for all your feedback!
– Big_D
Dec 31 '18 at 15:13
@Big_D I updated my answer with SQL Server code. For whatever reason, I thought you were using Postgres. My query should run now.
– Tim Biegeleisen
Dec 31 '18 at 15:22
@Big_D I updated my answer with SQL Server code. For whatever reason, I thought you were using Postgres. My query should run now.
– Tim Biegeleisen
Dec 31 '18 at 15:22
add a comment |
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.
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%2f53988294%2flearning-pivoting-in-tsql%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
