score:4
Accepted answer
Here's one way:
;WITH LagCTE AS (
SELECT id, fruit, price, qty, created,
LAG(price) OVER (PARTITION BY fruit
ORDER BY created) AS prevPrice,
LAG(qty) OVER (PARTITION BY fruit
ORDER BY created) AS prevQty
FROM mytable
)
SELECT ROW_NUMBER() OVER (ORDER BY changed) AS id,
fk, col, oldval, newval, changed
FROM (
SELECT id AS fk, fruit, 'price' AS col,
prevPrice AS oldval, price AS newval,
created AS changed
FROM LagCTE
WHERE prevPrice <> price
UNION ALL
SELECT id AS fk, fruit, 'qty' AS col,
prevQty AS oldval, qty AS newval,
created AS changed
FROM LagCTE
WHERE prevQty <> qty) AS t
score:1
Preconditions for the following answer are that price
and qty
have the same datatype and id
is an IDENTITY
column.
The first step is to find changes. You can do that by numering all records of the same fruit ordered by it's id
and then join subsequent records. Then you can UNPIVOT
the result and filter unchanged colums out.
WITH
SourceNumbered AS
(
SELECT
ROW_NUMBER() OVER(PARTITION BY fruit ORDER BY id) AS nr,
id, fruit, price, qty, created
FROM
SourceTable
),
SourceUnpivoted AS
(
SELECT
U.id, U.fk, U.col
FROM
(
SELECT
L.id, R.id AS fk,
L.price - R.price AS price,
L.qty - R.qty AS qty
FROM
SourceNumbered L
INNER JOIN SourceNumbered R
ON R.fruit = L.fruit
AND R.nr = L.nr + 1
) D
UNPIVOT (value FOR col IN (price, qty)) U
WHERE
value != 0
)
SELECT
U.id, U.fk, U.col,
CASE U.col
WHEN 'price'
THEN O.price
WHEN 'qty'
THEN O.qty
END AS oldval,
CASE U.col
WHEN 'price'
THEN N.price
WHEN 'qty'
THEN N.qty
END AS oldval,
N.created AS changed
FROM
SourceUnpivoted U
INNER JOIN SourceTable O
ON O.id = U.id
INNER JOIN SourceTable N
ON N.id = U.fk;
Since you can't unpivot more than one column, the case in the final SELECT
is unavoidable.
More questions
- Tracking data changes per table column in T-SQL
- SQL Server - Update column from data in the same table
- How to change data type of a column in an SQL table from integer to decimal
- SQL Server performance for alter table alter column change data type
- Can I use special characters in column name in SQL Server and C# data table
- Ranking Sql Server Table Rows When a Column Value Changes
- In SQL Server, how can I insert data into a table that has just one column which is of identity type?
- SQL Azure - Low performance of External Data Table with nvarchar(max) column
- Convert table into JSON per column value in SQL Server 2016
- Fetch specific column and row data based on row values from another table in SQL server
- How to update a SQL table column with XML data
- SQL syntax for same column from a table being used at 2 different places in the data entry
- Exporting data from MS Access to MS SQL with schema and table changes
- Parsing JSON data from SQL Server table column
- Column names in each table must be unique. while copy table data from one table to another in sql server
- Populate a sql table with duplicate data except for one column
- Powerpivot sql query fails - Unable to convert a value to the data type requested for table column
- SQL Server table data to xml file with column value
- LogParser: SQL table column "X" data type is not compatible with SELECT clause item "cs-username" (type STRING)
- Querying SQL Server / Oracle Database Table Data - Identity Column
- SQL dynamic query: check whether table column has any data
- SQL Server : get all data from one table but condition on 2nd tables column
- In SQL Server, how do I retrieve data values from table column names identified in a sub-query?
- Update data in a SQL Server temp table where the column names are unknown
- SQL Server 2005: Need advice on handling multiple-language data in one table column
- Filtering data values in one column based on another column and then inserting values into different columns in same SQL Table
- Get data from every column in every table in a database | SQL Server
- TSQL - Get maximum length of data in every column in every table without Dynamic SQL
- SQL Server's Change Tracking and Change Data Capture for a table at the same time
- Remove 1 Duplicate row from SQL Table if two column data matches
More questions with similar tag
- Combining 2 rows into one
- How to build a dynamic connection string in SSIS with variable number of whitespaces
- How to execute a task when a new element is INSERTED into a table in Microsoft SQL Management Studio
- Is there a way to set a nullubule Timstamp2 back on null?
- SQL Server - Joining two tables directly or, sometimes, through a third table
- DateTime accepts null value
- How to add multiple conditions in sql?
- MS SQL Server trigger to update item rating and number of votes
- Query showing records that do NOT match in between tables
- Transform an entire database varchar to uppercase
- Left Outer Join in SQL Server
- Add missing rows to a result set
- Count all entries of child tables in single query
- SELECT 2 VALUES with different requirement
- SQL Server : How to treat nulls as zeros
- C# windows Application attachmentFilename
- Transaction scope always escalates to MSDTC and erros when calling save changes on and entity context. Even with only one entity context
- Adding a Table Trigger in SQL Server for a webhook
- scalablelist index outside the allowed range
- Executing a trigger?
- Stored procedure to update a table based on data from table parameter
- php 7.1.1 drivers for SQL server
- Count of records of table in mssql server
- Joining tables on foreign key
- SQL Server max and min date in row
- SQL Query for extracting rate and date started and ended for the table and data shown
- connecting a java app to external microsoft sql server 2012
- SQL Server and SSIS Permission issue?
- What are Covering Indexes and Covered Queries in SQL Server?
- SQL Server 2016 Express Management Studio cannot design a new or existing table
Source:
stackoverflow.com