Dynamic SQL PIVOT: create table temp ( date datetime, category varchar(3), amount money ) insert into temp values (‘1/1/2012’, ‘ABC’, 1000.00) insert into temp values (‘2/1/2012’, ‘DEF’, 500.00) insert into temp values (‘2/1/2012’, ‘GHI’, 800.00) insert into temp values (‘2/10/2012’, ‘DEF’, 700.00) insert into temp values (‘3/1/2012’, ‘ABC’, 1100.00) DECLARE @cols AS NVARCHAR(MAX), @query AS … Read more
SQL Server 2017 does introduce a new aggregate function STRING_AGG ( expression, separator). Concatenates the values of string expressions and places separator values between them. The separator is not added at the end of string. The concatenated elements can be ordered by appending WITHIN GROUP (ORDER BY some_expression) For versions 2005-2016 I typically use the … Read more
This feature is now made easy in SQL Server 2012. This is working from SQL Server 2012 onwards. Limit with offset to select 11 to 20 rows in SQL Server: SELECT email FROM emailTable WHERE user_id=3 ORDER BY Id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY; ORDER BY: required OFFSET: optional number of skipped … Read more
I would avoid using SELECT *. Specify columns you actually want even though it may be all of them. SQL Server 2005+ SELECT col1, col2 FROM ( SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum FROM MyTable ) AS MyDerivedTable WHERE MyDerivedTable.RowNum BETWEEN @startRow AND @endRow SQL Server 2000 Efficiently Paging Through Large … Read more
You could try using the Lead() and Lag() SQL Analytic function for this SELECT * from (SELECT *, LAG(AMOUNT) over (partition by user_id order by datetime) as Yesterday_AMOUNT from SALES s ) x where cast(s.datetime as date) = cast(getdaate() as date) and x.AMOUNT <> s.Yesterday_AMOUNT;