In this article we learn creating executing dynamic sql statement, distinct clause, coalesce function and pivot clause. Some times we need to get reports on variation based tables. Sample data are bellow:

This case, we need to show all members per games level by level. We can use pivot clause in normal senarios(with expcilit pivot data) but this case we must get like following data:

We must generate dynamic sql for MemberID and GameID gorups and show order and point data with grouping. Now lets coding.

SELECT DISTINCT CAST(GameID AS VARCHAR(11)) As GameID FROM GameResults

Result:

    GameID
    256
    268
    302
    549

and declare following variables

DECLARE @strSQL varchar(8000) -- Stores all dynamic sql string

DECLARE @columnNames VARCHAR(8000) -- Stores all column grouping string

We can generate sql statement now

DECLARE @strSQL varchar(8000) 
DECLARE @columnNames VARCHAR(8000) 

SELECT @columnNames = COALESCE(@columnNames+',', '') + -- if column name is null then return empty string and concat itself
'MAX(CASE WHEN [GameID] = ' + GameID + ' THEN [Order] ELSE 0 END) AS [' + GameID + '_ORDER], ' + -- GameID_ORDER
'MAX(CASE WHEN [GameID] = ' + GameID + ' THEN Point ELSE 0 END) AS [' + GameID + '_POINT]' -- GameID_POINT
FROM (SELECT DISTINCT CAST(GameID AS VARCHAR(11)) As GameID FROM GameResults) AS GameReports

SET @strSQL = 'SELECT MemberID, ' + @columnNames + ' FROM GameResults GROUP BY MemberID' -- Setting @sqlSql variable
EXEC(@strSQL) -- Execute generated sql string

Result:

Dynamicaly generated sql string output:

SELECT MemberID, 
MAX(CASE WHEN [GameID] = 256 THEN [Order] ELSE 0 END) AS [256_ORDER], MAX(CASE WHEN [GameID] = 256 THEN Point ELSE 0 END) AS [256_POINT],
MAX(CASE WHEN [GameID] = 268 THEN [Order] ELSE 0 END) AS [268_ORDER], MAX(CASE WHEN [GameID] = 268 THEN Point ELSE 0 END) AS [268_POINT],
MAX(CASE WHEN [GameID] = 302 THEN [Order] ELSE 0 END) AS [302_ORDER], MAX(CASE WHEN [GameID] = 302 THEN Point ELSE 0 END) AS [302_POINT],
MAX(CASE WHEN [GameID] = 549 THEN [Order] ELSE 0 END) AS [549_ORDER], MAX(CASE WHEN [GameID] = 549 THEN Point ELSE 0 END) AS [549_POINT]
FROM GameResults GROUP BY MemberID