Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Pivot a table in SQL Server that contains yearly data
Language:
Ada
Assembly
Bash
C#
C++ (gcc)
C++ (clang)
C++ (vc++)
C (gcc)
C (clang)
C (vc)
Client Side
Clojure
Common Lisp
D
Elixir
Erlang
F#
Fortran
Go
Haskell
Java
Javascript
Kotlin
Lua
MySql
Node.js
Ocaml
Octave
Objective-C
Oracle
Pascal
Perl
Php
PostgreSQL
Prolog
Python
Python 3
R
Rust
Ruby
Scala
Scheme
Sql Server
Swift
Tcl
Visual Basic
Layout:
Vertical
Horizontal
IF OBJECT_ID('dbo.Expenses', 'U') IS NOT NULL BEGIN DROP TABLE dbo.Expenses END CREATE TABLE dbo.Expenses ( ExpenseId INT NOT NULL IDENTITY (1,1) PRIMARY KEY, ExpenseType VARCHAR(20) NOT NULL, Amount DECIMAL(6,2) NOT NULL, ExpenseDate DATE NOT NULL ) INSERT INTO Expenses ([ExpenseType], [Amount], [ExpenseDate]) VALUES ('Rent', 340.50, '2017-01-01'), ('Food', 23.50, '2017-01-03'), ('Food', 27.50, '2017-04-08'), ('Gasoline', 50.30, '2017-01-05'), ('Food', 27.50, '2016-01-08'), ('Rent', 340.50, '2016-01-01'), ('Food', 85.00, '2015-07-17'), ('Gasoline', 48.40, '2015-12-15'), ('Furniture', 540.00, '2015-09-22'), ('Rent', 335.00, '2015-01-01') ; SELECT ExpenseType, COALESCE([2015], 0) AS [2015], COALESCE([2016], 0) AS [2016], COALESCE([2017], 0) AS [2017] FROM (SELECT ExpenseType, YEAR(ExpenseDate) AS ExpenseYear, Amount FROM Expenses) AS SourceTable PIVOT ( SUM(Amount) FOR ExpenseYear IN ([2015], [2016], [2017]) ) AS PivotTable DROP TABLE dbo.Expenses
View schema
Execution time: 0,02 sec, rows selected: 4, rows affected: 10, absolute service time: 0,19 sec
edit mode
|
history
ExpenseType
2015
2016
2017
1
Food
85,00
27,50
51,00
2
Furniture
540,00
0,00
0,00
3
Gasoline
48,40
0,00
50,30
4
Rent
335,00
340,50
340,50