Dynamic Query Needed

  • texpic

    SSCertifiable

    Points: 5882

    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
  • texpic

    SSCertifiable

    Points: 5882

    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)
  • pietlinden

    SSC Guru

    Points: 62816

    Could you explain in words what you're trying to do? What's your expected output?

    • This reply was modified 9 months, 3 weeks ago by  pietlinden.
  • Erland Sommarskog

    SSC-Insane

    Points: 23882

    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]

  • texpic

    SSCertifiable

    Points: 5882

    Yes that works perfect, thank you.

  • drew.allen

    SSC Guru

    Points: 76737

    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 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply