SQL pivot/transpose/row-column non-proprietary
Maybe this question has been asked already (it should
be quite simple). I've Googled loads of phrases
(PIVOT, ROWS to COLUMNS, TRANSPOSE &c.) and all I get is
TSQL this, TSQL that and or Oracle this, Oracle
that, proprietary function this, proprietary function that well, you get the picture.
I have a simple table structure - if it was any simpler, it'd
be a list :-) - i.e. it's two columns.
stock_date sku
---------- ---
2016-06-01 0
2016-06-02 123
2016-06-03 123
2016-06-04 123
2016-06-06 123
2016-06-07 123
2016-06-03 245
<... &c...>
There may be many thousands of sku's but the date range
will be at most a month or so, but can vary, so the code needs to cater for that.
So, I want to make the stock_date field a column.
sku 1 2 3 4 5 6 7
--- - - - - - - -
123 0 Y Y Y 0 Y Y
245 0 0 Y.... &c.
Ideally, the code would be dynamic and would work with any server (even MySQL).
But if it has to use common table expressions and/or window functions, that's
OK but not proprietary crosstab or pivot functions.
pivot
edited Jul 29 '16 at 6:16
Vérace
15.9k33349
bumped to the homepage by Community♦ 21 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
Maybe this question has been asked already (it should
be quite simple). I've Googled loads of phrases
(PIVOT, ROWS to COLUMNS, TRANSPOSE &c.) and all I get is
TSQL this, TSQL that and or Oracle this, Oracle
that, proprietary function this, proprietary function that well, you get the picture.
I have a simple table structure - if it was any simpler, it'd
be a list :-) - i.e. it's two columns.
stock_date sku
---------- ---
2016-06-01 0
2016-06-02 123
2016-06-03 123
2016-06-04 123
2016-06-06 123
2016-06-07 123
2016-06-03 245
<... &c...>
There may be many thousands of sku's but the date range
will be at most a month or so, but can vary, so the code needs to cater for that.
So, I want to make the stock_date field a column.
sku 1 2 3 4 5 6 7
--- - - - - - - -
123 0 Y Y Y 0 Y Y
245 0 0 Y.... &c.
Ideally, the code would be dynamic and would work with any server (even MySQL).
But if it has to use common table expressions and/or window functions, that's
OK but not proprietary crosstab or pivot functions.
pivot
edited Jul 29 '16 at 6:16
Vérace
15.9k33349
bumped to the homepage by Community♦ 21 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
There's no standard for dynamic SQL. Every vendor implements their own solution to that. Partly the reason is that SQL proper is a declarative language. You aren't telling the server how to achieve the result, only what to achieve, but the what (e.g. how many columns and what they should be called) should be stated explicitly. So, in order to be vague about the result ("hey server, figure out the number and names of the columns yourself"), you build a query dynamically and that's where you cease being declarative. The non-declarative part of SQL is platform-specific, no way around that.
– Andriy M
Jul 29 '16 at 8:21
As for platform-independent method of pivoting, conditional aggregation may be what you are looking for.
– Andriy M
Jul 29 '16 at 8:22
add a comment |
Maybe this question has been asked already (it should
be quite simple). I've Googled loads of phrases
(PIVOT, ROWS to COLUMNS, TRANSPOSE &c.) and all I get is
TSQL this, TSQL that and or Oracle this, Oracle
that, proprietary function this, proprietary function that well, you get the picture.
I have a simple table structure - if it was any simpler, it'd
be a list :-) - i.e. it's two columns.
stock_date sku
---------- ---
2016-06-01 0
2016-06-02 123
2016-06-03 123
2016-06-04 123
2016-06-06 123
2016-06-07 123
2016-06-03 245
<... &c...>
There may be many thousands of sku's but the date range
will be at most a month or so, but can vary, so the code needs to cater for that.
So, I want to make the stock_date field a column.
sku 1 2 3 4 5 6 7
--- - - - - - - -
123 0 Y Y Y 0 Y Y
245 0 0 Y.... &c.
Ideally, the code would be dynamic and would work with any server (even MySQL).
But if it has to use common table expressions and/or window functions, that's
OK but not proprietary crosstab or pivot functions.
pivot
edited Jul 29 '16 at 6:16
Vérace
15.9k33349
Maybe this question has been asked already (it should
be quite simple). I've Googled loads of phrases
(PIVOT, ROWS to COLUMNS, TRANSPOSE &c.) and all I get is
TSQL this, TSQL that and or Oracle this, Oracle
that, proprietary function this, proprietary function that well, you get the picture.
I have a simple table structure - if it was any simpler, it'd
be a list :-) - i.e. it's two columns.
stock_date sku
---------- ---
2016-06-01 0
2016-06-02 123
2016-06-03 123
2016-06-04 123
2016-06-06 123
2016-06-07 123
2016-06-03 245
<... &c...>
There may be many thousands of sku's but the date range
will be at most a month or so, but can vary, so the code needs to cater for that.
So, I want to make the stock_date field a column.
sku 1 2 3 4 5 6 7
--- - - - - - - -
123 0 Y Y Y 0 Y Y
245 0 0 Y.... &c.
Ideally, the code would be dynamic and would work with any server (even MySQL).
But if it has to use common table expressions and/or window functions, that's
OK but not proprietary crosstab or pivot functions.
pivot
pivot
edited Jul 29 '16 at 6:16
Vérace
15.9k33349
edited Jul 29 '16 at 6:16
Vérace
15.9k33349
edited Jul 29 '16 at 6:16
Vérace
15.9k33349
edited Jul 29 '16 at 6:16
Vérace
15.9k33349
edited Jul 29 '16 at 6:16
Vérace
15.9k33349
15.9k33349
asked Jul 29 '16 at 6:03
user100406
bumped to the homepage by Community♦ 21 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 21 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
There's no standard for dynamic SQL. Every vendor implements their own solution to that. Partly the reason is that SQL proper is a declarative language. You aren't telling the server how to achieve the result, only what to achieve, but the what (e.g. how many columns and what they should be called) should be stated explicitly. So, in order to be vague about the result ("hey server, figure out the number and names of the columns yourself"), you build a query dynamically and that's where you cease being declarative. The non-declarative part of SQL is platform-specific, no way around that.
– Andriy M
Jul 29 '16 at 8:21
As for platform-independent method of pivoting, conditional aggregation may be what you are looking for.
– Andriy M
Jul 29 '16 at 8:22
add a comment |
There's no standard for dynamic SQL. Every vendor implements their own solution to that. Partly the reason is that SQL proper is a declarative language. You aren't telling the server how to achieve the result, only what to achieve, but the what (e.g. how many columns and what they should be called) should be stated explicitly. So, in order to be vague about the result ("hey server, figure out the number and names of the columns yourself"), you build a query dynamically and that's where you cease being declarative. The non-declarative part of SQL is platform-specific, no way around that.
– Andriy M
Jul 29 '16 at 8:21
As for platform-independent method of pivoting, conditional aggregation may be what you are looking for.
– Andriy M
Jul 29 '16 at 8:22
There's no standard for dynamic SQL. Every vendor implements their own solution to that. Partly the reason is that SQL proper is a declarative language. You aren't telling the server how to achieve the result, only what to achieve, but the what (e.g. how many columns and what they should be called) should be stated explicitly. So, in order to be vague about the result ("hey server, figure out the number and names of the columns yourself"), you build a query dynamically and that's where you cease being declarative. The non-declarative part of SQL is platform-specific, no way around that.
– Andriy M
Jul 29 '16 at 8:21
There's no standard for dynamic SQL. Every vendor implements their own solution to that. Partly the reason is that SQL proper is a declarative language. You aren't telling the server how to achieve the result, only what to achieve, but the what (e.g. how many columns and what they should be called) should be stated explicitly. So, in order to be vague about the result ("hey server, figure out the number and names of the columns yourself"), you build a query dynamically and that's where you cease being declarative. The non-declarative part of SQL is platform-specific, no way around that.
– Andriy M
Jul 29 '16 at 8:21
As for platform-independent method of pivoting, conditional aggregation may be what you are looking for.
– Andriy M
Jul 29 '16 at 8:22
As for platform-independent method of pivoting, conditional aggregation may be what you are looking for.
– Andriy M
Jul 29 '16 at 8:22
add a comment |
1 Answer
1
active
oldest
votes
A PIVOT can be written as a CASE statement. So for your example:
SELECT
sku,
MAX (CASE WHEN stock_date = '2016-06-01' THEN 'Y' ELSE 'N' END) AS [1],
MAX (CASE WHEN stock_date = '2016-06-02' THEN 'Y' ELSE 'N' END) AS [2],
MAX (CASE WHEN stock_date = '2016-06-03' THEN 'Y' ELSE 'N' END) AS [3],
...
FROM
dbo.mytable
GROUP BY sku
Note that this can (and should) be dynamically generated.
(Source: https://avaldes.com/pivot_using_case/)
answered Jul 30 '16 at 3:20
Randolph WestRandolph West
2,548215
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f145299%2fsql-pivot-transpose-row-column-non-proprietary%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
A PIVOT can be written as a CASE statement. So for your example:
SELECT
sku,
MAX (CASE WHEN stock_date = '2016-06-01' THEN 'Y' ELSE 'N' END) AS [1],
MAX (CASE WHEN stock_date = '2016-06-02' THEN 'Y' ELSE 'N' END) AS [2],
MAX (CASE WHEN stock_date = '2016-06-03' THEN 'Y' ELSE 'N' END) AS [3],
...
FROM
dbo.mytable
GROUP BY sku
Note that this can (and should) be dynamically generated.
(Source: https://avaldes.com/pivot_using_case/)
answered Jul 30 '16 at 3:20
Randolph WestRandolph West
2,548215
add a comment |
A PIVOT can be written as a CASE statement. So for your example:
SELECT
sku,
MAX (CASE WHEN stock_date = '2016-06-01' THEN 'Y' ELSE 'N' END) AS [1],
MAX (CASE WHEN stock_date = '2016-06-02' THEN 'Y' ELSE 'N' END) AS [2],
MAX (CASE WHEN stock_date = '2016-06-03' THEN 'Y' ELSE 'N' END) AS [3],
...
FROM
dbo.mytable
GROUP BY sku
Note that this can (and should) be dynamically generated.
(Source: https://avaldes.com/pivot_using_case/)
answered Jul 30 '16 at 3:20
Randolph WestRandolph West
2,548215
add a comment |
A PIVOT can be written as a CASE statement. So for your example:
SELECT
sku,
MAX (CASE WHEN stock_date = '2016-06-01' THEN 'Y' ELSE 'N' END) AS [1],
MAX (CASE WHEN stock_date = '2016-06-02' THEN 'Y' ELSE 'N' END) AS [2],
MAX (CASE WHEN stock_date = '2016-06-03' THEN 'Y' ELSE 'N' END) AS [3],
...
FROM
dbo.mytable
GROUP BY sku
Note that this can (and should) be dynamically generated.
(Source: https://avaldes.com/pivot_using_case/)
answered Jul 30 '16 at 3:20
Randolph WestRandolph West
2,548215
A PIVOT can be written as a CASE statement. So for your example:
SELECT
sku,
MAX (CASE WHEN stock_date = '2016-06-01' THEN 'Y' ELSE 'N' END) AS [1],
MAX (CASE WHEN stock_date = '2016-06-02' THEN 'Y' ELSE 'N' END) AS [2],
MAX (CASE WHEN stock_date = '2016-06-03' THEN 'Y' ELSE 'N' END) AS [3],
...
FROM
dbo.mytable
GROUP BY sku
Note that this can (and should) be dynamically generated.
(Source: https://avaldes.com/pivot_using_case/)
answered Jul 30 '16 at 3:20
Randolph WestRandolph West
2,548215
answered Jul 30 '16 at 3:20
Randolph WestRandolph West
2,548215
answered Jul 30 '16 at 3:20
Randolph WestRandolph West
2,548215
answered Jul 30 '16 at 3:20
Randolph WestRandolph West
2,548215
2,548215
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f145299%2fsql-pivot-transpose-row-column-non-proprietary%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
There's no standard for dynamic SQL. Every vendor implements their own solution to that. Partly the reason is that SQL proper is a declarative language. You aren't telling the server how to achieve the result, only what to achieve, but the what (e.g. how many columns and what they should be called) should be stated explicitly. So, in order to be vague about the result ("hey server, figure out the number and names of the columns yourself"), you build a query dynamically and that's where you cease being declarative. The non-declarative part of SQL is platform-specific, no way around that.
– Andriy M
Jul 29 '16 at 8:21
As for platform-independent method of pivoting, conditional aggregation may be what you are looking for.
– Andriy M
Jul 29 '16 at 8:22