September 23, 2019 at 11:55 pm
How would I dynamically create the last line? Thanks!
IF OBJECT_ID('tempdb..#data') IS NOT NULL DROP TABLE #data
CREATE TABLE #Data (AccountId int, YrMo varchar(6), Amount int)
INSERT INTO #data
SELECT 10001, 201906, 100 UNION ALL
SELECT 10001, 201907, 200 UNION ALL
SELECT 10001, 201908, 300 UNION ALL
SELECT 10002, 201906, 500 UNION ALL
SELECT 10002, 201907, 700 UNION ALL
SELECT 10002, 201908, 900
IF OBJECT_ID('tempdb..##tmp') IS NOT NULL DROP TABLE ##tmp
DECLARE @SQLStatement1 nVARCHAR(MAX) = 'SELECT AccountId'
SELECT @SQLStatement1 = @SQLStatement1 + ', ' + CHAR(13) + '['+ YrMo + '] = SUM(CASE WHEN YrMo = ''' + YrMo + ''' THEN Amount ELSE 0 END) '
FROM #data A
GROUP BY YrMo ORDER BY YrMo DESC
SELECT @SQLStatement1 = @SQLStatement1 + CHAR(13) + 'INTO ##tmp FROM #data GROUP BY AccountId'
EXEC (@SQLStatement1)
--need this statement to be created dynamically
SELECT AccountId, [201908]-[201907] as Differ, [201908], [201907], [201906] FROM ##tmp September 24, 2019 at 12:58 am
This is the best I was able to do. Is there a better way?
DECLARE @ColumnName2 varchar(99)SELECT @ColumnName2 = COLUMN_NAME FROM tempdb.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'##tmp' AND ORDINAL_POSITION IN (2)
DECLARE @ColumnName3 varchar(99)SELECT @ColumnName3 = COLUMN_NAME FROM tempdb.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'##tmp' AND ORDINAL_POSITION IN (3)
DECLARE @ColumnName4 varchar(99)SELECT @ColumnName4 = COLUMN_NAME FROM tempdb.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'##tmp' AND ORDINAL_POSITION IN (4)
DECLARE @SqlQuery nvarchar(MAX) = 'SELECT AccountId'
SELECT @SqlQuery = @SqlQuery + ', [' + @ColumnName2 + '] - [' + @ColumnName3 + '] as Differ, [' + @ColumnName2 + '], [' + @ColumnName3 + '], [' + @ColumnName4 + '] FROM ##tmp'
--PRINT @SqlQuery
EXEC (@SqlQuery)
September 24, 2019 at 1:20 am
Could you explain in words what you're trying to do? What's your expected output?
September 24, 2019 at 7:45 am
IF OBJECT_ID('tempdb..#data') IS NOT NULL DROP TABLE #data
IF OBJECT_ID('tempdb..#pivotcols') IS NOT NULL DROP TABLE #pivotcols
CREATE TABLE #Data (AccountId int, YrMo varchar(6), Amount int)
INSERT INTO #data
SELECT 10001, 201906, 100 UNION ALL
SELECT 10001, 201907, 200 UNION ALL
SELECT 10001, 201908, 300 UNION ALL
SELECT 10002, 201906, 500 UNION ALL
SELECT 10002, 201907, 700 UNION ALL
SELECT 10002, 201908, 900
CREATE TABLE #pivotcols (colno int NOT NULL PRIMARY KEY,
colname varchar(6) NOT NULL UNIQUE)
INSERT #pivotcols (colno, colname)
SELECT row_number() OVER (ORDER BY YrMo DESC), YrMo
FROM (SELECT DISTINCT YrMo FROM #data) AS c
DECLARE @lineend char(3) = ',' + char(13) + char(10),
@firstmon char(6),
@secondmon char(6),
@sql nvarchar(MAX)
SELECT @firstmon = colname FROM #pivotcols WHERE colno = 1
SELECT @secondmon = colname FROM #pivotcols WHERE colno = 2
SELECT @sql = 'SELECT AccountId,
SUM(CASE YrMo WHEN @firstmon THEN Amount
WHEN @secondmon THEN -Amount
END) AS Differ,'
SELECT @sql +=
(SELECT 'SUM(CASE YrMo WHEN ' + quotename(colname, '''') +
'THEN Amount END ) AS ' + quotename(colname) + @lineend
FROM #pivotcols
ORDER BY colno
FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)')
SELECT @sql = substring(@sql, 1, len(@sql) - len(@lineend))
SELECT @sql += 'FROM #data
GROUP BY AccountId
ORDER BY AccountId'
PRINT @sql
EXEC sp_executesql @sql, N'@firstmon char(6), @secondmon char(6)', @firstmon, @secondmon
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 26, 2019 at 8:36 am
Yes that works perfect, thank you.
September 26, 2019 at 4:12 pm
Also, you should ALWAYS use QUOTENAME() to insert brackets. Doing it the way you did opens you up to SQL injection.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply