t sql derive column based on date of row
1
I apologise for asking a question I'm sure is answered many times over but I can't quite word it to search for it. Even a link to a similar problem would be much appreciated.
If I have a table like so:
+----+----------+-----+
|Name|Date |Score|
+----+----------+-----+
| Tom|2018-20-11| 100|
| Tom|2018-13-11| 95|
+----+----------+-----+
how would I query this so that each row would show a column for score today, score one week ago etc.? In this example with sample data it would be one row e.g.
+----+----------+-----------+
|Name|ScoreToday|Score-7Days|
+----+----------+-----------+
| Tom| 100| 95|
+----+----------+-----------+
I've seen similar problems solved via cross apply buy I can't work it out. Thanks for your help :)
Some sample code:
if exists (select * from sys.tables where name = 'test') drop table test
go
create table test (
Name varchar(128)
, AsAt datetime
, Score int
)
insert test values ('Tom', getdate(), 87)
, ('Tom', getdate() - 2, 100)
, ('Tom', getdate() - 7, 95)
, ('Tom', getdate() - 1, 81)
, ('Tom', getdate() - 30, 95)
sql
sql-server tsql pivot asked Nov 20 '18 at 19:40
beehivebeehive
266
add a comment |
1
I apologise for asking a question I'm sure is answered many times over but I can't quite word it to search for it. Even a link to a similar problem would be much appreciated.
If I have a table like so:
+----+----------+-----+
|Name|Date |Score|
+----+----------+-----+
| Tom|2018-20-11| 100|
| Tom|2018-13-11| 95|
+----+----------+-----+
how would I query this so that each row would show a column for score today, score one week ago etc.? In this example with sample data it would be one row e.g.
+----+----------+-----------+
|Name|ScoreToday|Score-7Days|
+----+----------+-----------+
| Tom| 100| 95|
+----+----------+-----------+
I've seen similar problems solved via cross apply buy I can't work it out. Thanks for your help :)
Some sample code:
if exists (select * from sys.tables where name = 'test') drop table test
go
create table test (
Name varchar(128)
, AsAt datetime
, Score int
)
insert test values ('Tom', getdate(), 87)
, ('Tom', getdate() - 2, 100)
, ('Tom', getdate() - 7, 95)
, ('Tom', getdate() - 1, 81)
, ('Tom', getdate() - 30, 95)
sql
sql-server tsql pivot asked Nov 20 '18 at 19:40
beehivebeehive
266
1
How many dates can be there per Name in the input table?
– Pawan Kumar
Nov 20 '18 at 19:46
Are you wanting to combine and aggregate? Or do you just want to pivot rows into columns?
– pmbAustin
Nov 20 '18 at 19:48
Would it always be 7 days back from today? Or todays score and the score within the last 7 days or that?
– Tim Mylott
Nov 20 '18 at 19:53
This is called Pivot. Plenty of examples on stackoverflow.
– Zohar Peled
Nov 20 '18 at 20:11
Thanks for suggestions, there will be many dates, I want a few samples, I don't think I want to aggregate, just want a row taking certain dates as columns e.g. one week ago, one month ago, 3 months ago If it can be done in either pivot/unpivot/cross apply I'd like to see it in this example I would appreciate that. Thanks
– beehive
Nov 20 '18 at 20:59
add a comment |
1
1
1
I apologise for asking a question I'm sure is answered many times over but I can't quite word it to search for it. Even a link to a similar problem would be much appreciated.
If I have a table like so:
+----+----------+-----+
|Name|Date |Score|
+----+----------+-----+
| Tom|2018-20-11| 100|
| Tom|2018-13-11| 95|
+----+----------+-----+
how would I query this so that each row would show a column for score today, score one week ago etc.? In this example with sample data it would be one row e.g.
+----+----------+-----------+
|Name|ScoreToday|Score-7Days|
+----+----------+-----------+
| Tom| 100| 95|
+----+----------+-----------+
I've seen similar problems solved via cross apply buy I can't work it out. Thanks for your help :)
Some sample code:
if exists (select * from sys.tables where name = 'test') drop table test
go
create table test (
Name varchar(128)
, AsAt datetime
, Score int
)
insert test values ('Tom', getdate(), 87)
, ('Tom', getdate() - 2, 100)
, ('Tom', getdate() - 7, 95)
, ('Tom', getdate() - 1, 81)
, ('Tom', getdate() - 30, 95)
sql
sql-server tsql pivot asked Nov 20 '18 at 19:40
beehivebeehive
266
I apologise for asking a question I'm sure is answered many times over but I can't quite word it to search for it. Even a link to a similar problem would be much appreciated.
If I have a table like so:
+----+----------+-----+
|Name|Date |Score|
+----+----------+-----+
| Tom|2018-20-11| 100|
| Tom|2018-13-11| 95|
+----+----------+-----+
how would I query this so that each row would show a column for score today, score one week ago etc.? In this example with sample data it would be one row e.g.
+----+----------+-----------+
|Name|ScoreToday|Score-7Days|
+----+----------+-----------+
| Tom| 100| 95|
+----+----------+-----------+
I've seen similar problems solved via cross apply buy I can't work it out. Thanks for your help :)
Some sample code:
if exists (select * from sys.tables where name = 'test') drop table test
go
create table test (
Name varchar(128)
, AsAt datetime
, Score int
)
insert test values ('Tom', getdate(), 87)
, ('Tom', getdate() - 2, 100)
, ('Tom', getdate() - 7, 95)
, ('Tom', getdate() - 1, 81)
, ('Tom', getdate() - 30, 95)
sql
sql-server tsql pivot sql
sql-server tsql pivot asked Nov 20 '18 at 19:40
beehivebeehive
266
asked Nov 20 '18 at 19:40
beehivebeehive
266
edited Nov 20 '18 at 20:55
beehive
asked Nov 20 '18 at 19:40
beehivebeehive
266
asked Nov 20 '18 at 19:40
beehivebeehive
266
asked Nov 20 '18 at 19:40
beehivebeehive
266
266
1
How many dates can be there per Name in the input table?
– Pawan Kumar
Nov 20 '18 at 19:46
Are you wanting to combine and aggregate? Or do you just want to pivot rows into columns?
– pmbAustin
Nov 20 '18 at 19:48
Would it always be 7 days back from today? Or todays score and the score within the last 7 days or that?
– Tim Mylott
Nov 20 '18 at 19:53
This is called Pivot. Plenty of examples on stackoverflow.
– Zohar Peled
Nov 20 '18 at 20:11
Thanks for suggestions, there will be many dates, I want a few samples, I don't think I want to aggregate, just want a row taking certain dates as columns e.g. one week ago, one month ago, 3 months ago If it can be done in either pivot/unpivot/cross apply I'd like to see it in this example I would appreciate that. Thanks
– beehive
Nov 20 '18 at 20:59
add a comment |
1
How many dates can be there per Name in the input table?
– Pawan Kumar
Nov 20 '18 at 19:46
Are you wanting to combine and aggregate? Or do you just want to pivot rows into columns?
– pmbAustin
Nov 20 '18 at 19:48
Would it always be 7 days back from today? Or todays score and the score within the last 7 days or that?
– Tim Mylott
Nov 20 '18 at 19:53
This is called Pivot. Plenty of examples on stackoverflow.
– Zohar Peled
Nov 20 '18 at 20:11
Thanks for suggestions, there will be many dates, I want a few samples, I don't think I want to aggregate, just want a row taking certain dates as columns e.g. one week ago, one month ago, 3 months ago If it can be done in either pivot/unpivot/cross apply I'd like to see it in this example I would appreciate that. Thanks
– beehive
Nov 20 '18 at 20:59
1
1
How many dates can be there per Name in the input table?
– Pawan Kumar
Nov 20 '18 at 19:46
How many dates can be there per Name in the input table?
– Pawan Kumar
Nov 20 '18 at 19:46
Are you wanting to combine and aggregate? Or do you just want to pivot rows into columns?
– pmbAustin
Nov 20 '18 at 19:48
Are you wanting to combine and aggregate? Or do you just want to pivot rows into columns?
– pmbAustin
Nov 20 '18 at 19:48
Would it always be 7 days back from today? Or todays score and the score within the last 7 days or that?
– Tim Mylott
Nov 20 '18 at 19:53
Would it always be 7 days back from today? Or todays score and the score within the last 7 days or that?
– Tim Mylott
Nov 20 '18 at 19:53
This is called Pivot. Plenty of examples on stackoverflow.
– Zohar Peled
Nov 20 '18 at 20:11
This is called Pivot. Plenty of examples on stackoverflow.
– Zohar Peled
Nov 20 '18 at 20:11
Thanks for suggestions, there will be many dates, I want a few samples, I don't think I want to aggregate, just want a row taking certain dates as columns e.g. one week ago, one month ago, 3 months ago If it can be done in either pivot/unpivot/cross apply I'd like to see it in this example I would appreciate that. Thanks
– beehive
Nov 20 '18 at 20:59
Thanks for suggestions, there will be many dates, I want a few samples, I don't think I want to aggregate, just want a row taking certain dates as columns e.g. one week ago, one month ago, 3 months ago If it can be done in either pivot/unpivot/cross apply I'd like to see it in this example I would appreciate that. Thanks
– beehive
Nov 20 '18 at 20:59
add a comment |
1 Answer
1
active
oldest
votes
0
I think you are looking for
CREATE TABLE T
([Name] varchar(4), [Date] datetime, [Score] int);
INSERT INTO T
([Name], [Date], [Score])
VALUES
('Tom', '2018-11-20 00:00:00', 100),
('Tom', '2018-11-13 00:00:00', 95),
('Sami', '2018-11-13 00:00:00', 55),
('Sami', '2018-11-15 00:00:00', 44);
DECLARE @Dates NVARCHAR(MAX) = '';
DECLARE @SQL NVARCHAR(MAX);
SELECT @Dates = @Dates + ',' + QUOTENAME([Date])
FROM T
GROUP BY [Date];
SET @Dates = STUFF(@Dates, 1, 1, '');
SET @SQL = N' SELECT * FROM(SELECT * FROM T) TT PIVOT( MAX(Score) FOR [Date] IN ('+ @Dates +')) PVT;';
EXECUTE sp_executesql @SQL;
Returns:
+------+---------------------+---------------------+---------------------+
| Name | Nov 13 2018 12:00AM | Nov 15 2018 12:00AM | Nov 20 2018 12:00AM |
+------+---------------------+---------------------+---------------------+
| Sami | 55 | 44 | |
| Tom | 95 | | 100 |
+------+---------------------+---------------------+---------------------+
Demo
answered Nov 20 '18 at 21:00
SamiSami
8,99831242
This is almost it, is the dynamic sql for picking up the dates? Because if it can be done without dynamic sql, I will always be picking a certain number of days previous to today. Thanks for your help
– beehive
Nov 20 '18 at 22:14
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%2f53400380%2ft-sql-derive-column-based-on-date-of-row%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
0
I think you are looking for
CREATE TABLE T
([Name] varchar(4), [Date] datetime, [Score] int);
INSERT INTO T
([Name], [Date], [Score])
VALUES
('Tom', '2018-11-20 00:00:00', 100),
('Tom', '2018-11-13 00:00:00', 95),
('Sami', '2018-11-13 00:00:00', 55),
('Sami', '2018-11-15 00:00:00', 44);
DECLARE @Dates NVARCHAR(MAX) = '';
DECLARE @SQL NVARCHAR(MAX);
SELECT @Dates = @Dates + ',' + QUOTENAME([Date])
FROM T
GROUP BY [Date];
SET @Dates = STUFF(@Dates, 1, 1, '');
SET @SQL = N' SELECT * FROM(SELECT * FROM T) TT PIVOT( MAX(Score) FOR [Date] IN ('+ @Dates +')) PVT;';
EXECUTE sp_executesql @SQL;
Returns:
+------+---------------------+---------------------+---------------------+
| Name | Nov 13 2018 12:00AM | Nov 15 2018 12:00AM | Nov 20 2018 12:00AM |
+------+---------------------+---------------------+---------------------+
| Sami | 55 | 44 | |
| Tom | 95 | | 100 |
+------+---------------------+---------------------+---------------------+
Demo
answered Nov 20 '18 at 21:00
SamiSami
8,99831242
This is almost it, is the dynamic sql for picking up the dates? Because if it can be done without dynamic sql, I will always be picking a certain number of days previous to today. Thanks for your help
– beehive
Nov 20 '18 at 22:14
add a comment |
0
I think you are looking for
CREATE TABLE T
([Name] varchar(4), [Date] datetime, [Score] int);
INSERT INTO T
([Name], [Date], [Score])
VALUES
('Tom', '2018-11-20 00:00:00', 100),
('Tom', '2018-11-13 00:00:00', 95),
('Sami', '2018-11-13 00:00:00', 55),
('Sami', '2018-11-15 00:00:00', 44);
DECLARE @Dates NVARCHAR(MAX) = '';
DECLARE @SQL NVARCHAR(MAX);
SELECT @Dates = @Dates + ',' + QUOTENAME([Date])
FROM T
GROUP BY [Date];
SET @Dates = STUFF(@Dates, 1, 1, '');
SET @SQL = N' SELECT * FROM(SELECT * FROM T) TT PIVOT( MAX(Score) FOR [Date] IN ('+ @Dates +')) PVT;';
EXECUTE sp_executesql @SQL;
Returns:
+------+---------------------+---------------------+---------------------+
| Name | Nov 13 2018 12:00AM | Nov 15 2018 12:00AM | Nov 20 2018 12:00AM |
+------+---------------------+---------------------+---------------------+
| Sami | 55 | 44 | |
| Tom | 95 | | 100 |
+------+---------------------+---------------------+---------------------+
Demo
answered Nov 20 '18 at 21:00
SamiSami
8,99831242
This is almost it, is the dynamic sql for picking up the dates? Because if it can be done without dynamic sql, I will always be picking a certain number of days previous to today. Thanks for your help
– beehive
Nov 20 '18 at 22:14
add a comment |
0
0
0
I think you are looking for
CREATE TABLE T
([Name] varchar(4), [Date] datetime, [Score] int);
INSERT INTO T
([Name], [Date], [Score])
VALUES
('Tom', '2018-11-20 00:00:00', 100),
('Tom', '2018-11-13 00:00:00', 95),
('Sami', '2018-11-13 00:00:00', 55),
('Sami', '2018-11-15 00:00:00', 44);
DECLARE @Dates NVARCHAR(MAX) = '';
DECLARE @SQL NVARCHAR(MAX);
SELECT @Dates = @Dates + ',' + QUOTENAME([Date])
FROM T
GROUP BY [Date];
SET @Dates = STUFF(@Dates, 1, 1, '');
SET @SQL = N' SELECT * FROM(SELECT * FROM T) TT PIVOT( MAX(Score) FOR [Date] IN ('+ @Dates +')) PVT;';
EXECUTE sp_executesql @SQL;
Returns:
+------+---------------------+---------------------+---------------------+
| Name | Nov 13 2018 12:00AM | Nov 15 2018 12:00AM | Nov 20 2018 12:00AM |
+------+---------------------+---------------------+---------------------+
| Sami | 55 | 44 | |
| Tom | 95 | | 100 |
+------+---------------------+---------------------+---------------------+
Demo
answered Nov 20 '18 at 21:00
SamiSami
8,99831242
I think you are looking for
CREATE TABLE T
([Name] varchar(4), [Date] datetime, [Score] int);
INSERT INTO T
([Name], [Date], [Score])
VALUES
('Tom', '2018-11-20 00:00:00', 100),
('Tom', '2018-11-13 00:00:00', 95),
('Sami', '2018-11-13 00:00:00', 55),
('Sami', '2018-11-15 00:00:00', 44);
DECLARE @Dates NVARCHAR(MAX) = '';
DECLARE @SQL NVARCHAR(MAX);
SELECT @Dates = @Dates + ',' + QUOTENAME([Date])
FROM T
GROUP BY [Date];
SET @Dates = STUFF(@Dates, 1, 1, '');
SET @SQL = N' SELECT * FROM(SELECT * FROM T) TT PIVOT( MAX(Score) FOR [Date] IN ('+ @Dates +')) PVT;';
EXECUTE sp_executesql @SQL;
Returns:
+------+---------------------+---------------------+---------------------+
| Name | Nov 13 2018 12:00AM | Nov 15 2018 12:00AM | Nov 20 2018 12:00AM |
+------+---------------------+---------------------+---------------------+
| Sami | 55 | 44 | |
| Tom | 95 | | 100 |
+------+---------------------+---------------------+---------------------+
Demo
answered Nov 20 '18 at 21:00
SamiSami
8,99831242
edited Nov 20 '18 at 21:10
answered Nov 20 '18 at 21:00
SamiSami
8,99831242
answered Nov 20 '18 at 21:00
SamiSami
8,99831242
answered Nov 20 '18 at 21:00
SamiSami
8,99831242
8,99831242
This is almost it, is the dynamic sql for picking up the dates? Because if it can be done without dynamic sql, I will always be picking a certain number of days previous to today. Thanks for your help
– beehive
Nov 20 '18 at 22:14
add a comment |
This is almost it, is the dynamic sql for picking up the dates? Because if it can be done without dynamic sql, I will always be picking a certain number of days previous to today. Thanks for your help
– beehive
Nov 20 '18 at 22:14
This is almost it, is the dynamic sql for picking up the dates? Because if it can be done without dynamic sql, I will always be picking a certain number of days previous to today. Thanks for your help
– beehive
Nov 20 '18 at 22:14
This is almost it, is the dynamic sql for picking up the dates? Because if it can be done without dynamic sql, I will always be picking a certain number of days previous to today. Thanks for your help
– beehive
Nov 20 '18 at 22:14
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.
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%2f53400380%2ft-sql-derive-column-based-on-date-of-row%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
1
How many dates can be there per Name in the input table?
– Pawan Kumar
Nov 20 '18 at 19:46
Are you wanting to combine and aggregate? Or do you just want to pivot rows into columns?
– pmbAustin
Nov 20 '18 at 19:48
Would it always be 7 days back from today? Or todays score and the score within the last 7 days or that?
– Tim Mylott
Nov 20 '18 at 19:53
This is called Pivot. Plenty of examples on stackoverflow.
– Zohar Peled
Nov 20 '18 at 20:11
Thanks for suggestions, there will be many dates, I want a few samples, I don't think I want to aggregate, just want a row taking certain dates as columns e.g. one week ago, one month ago, 3 months ago If it can be done in either pivot/unpivot/cross apply I'd like to see it in this example I would appreciate that. Thanks
– beehive
Nov 20 '18 at 20:59