0

Can anyone help me out. This is how I'm storing records in one of the tables in SQL Server.
How can I use Pivot/UnPivot to represent data from below table in expected format shown below. Thanks in advance.

<p>Table - Scores</p>
<table>
<tbody>
<tr>
<td>Name</td>
<td>Mode</td>
<td>&nbsp;Game</td>
</tr>
<tr>
<td>Player A</td>
<td>&nbsp;Easy</td>
<td>&nbsp;Game 1</td>
</tr>
<tr>
<td>Player A</td>
<td>&nbsp;Easy</td>
<td>&nbsp;Game 1</td>
</tr>
<tr>
<td>Player A</td>
<td>&nbsp;Easy</td>
<td>&nbsp;Game 2</td>
</tr>
<tr>
<td>Player B</td>
<td>&nbsp;Easy</td>
<td>&nbsp;Game 1</td>
</tr>
<tr>
<td>Player B</td>
<td>&nbsp;Medium</td>
<td>&nbsp;Game 1</td>
</tr>
<tr>
<td>Player C</td>
<td>&nbsp;Easy</td>
<td>&nbsp;Game 1</td>
</tr>
<tr>
<td>Player C</td>
<td>&nbsp;Easy</td>
<td>&nbsp;Game 2</td>
</tr>
<tr>
<td>Player C</td>
<td>&nbsp;Medium</td>
<td>&nbsp;Game 2</td>
</tr>
</tbody>
</table>
<p></p>
<p>Result after Pivot counts.</p>
<table>
<tbody>
<tr>
<td>Game</td>
<td>&nbsp;Mode</td>
<td>Player A</td>
<td>&nbsp;Player B</td>
<td>&nbsp;Player C</td>
</tr>
<tr>
<td>Game 1</td>
<td>&nbsp;Easy</td>
<td>&nbsp;2</td>
<td>&nbsp;1</td>
<td>&nbsp;1</td>
</tr>
<tr>
<td>Game 1</td>
<td>&nbsp;Medium</td>
<td>&nbsp;0</td>
<td>&nbsp;1</td>
<td>&nbsp;0</td>
</tr>
<tr>
<td>Game 2</td>
<td>&nbsp;Easy</td>
<td>&nbsp;1</td>
<td>&nbsp;0</td>
<td>&nbsp;1</td>
</tr>
<tr>
<td>Game 2</td>
<td>&nbsp;Medium</td>
<td>&nbsp;0</td>
<td>&nbsp;0</td>
<td>&nbsp;1</td>
</tr>
</tbody>
</table>
Robin Kedia
  • 283
  • 3
  • 16
  • possible duplicate of [Convert Rows to columns using 'Pivot' in SQL Server](http://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) – James Z Jul 22 '15 at 11:25
  • I agree..!! one should at least Google it, before putting a question. One should not spoon feed – Tirthak Shah Jul 22 '15 at 12:03

2 Answers2

0

You can do this using Dynamic Crosstab:

SQL Fiddle

DECLARE @sql1 VARCHAR(2000) = ''
DECLARE @sql2 VARCHAR(2000) = ''
DECLARE @sql3 VARCHAR(2000) = ''

SELECT @sql1 = 
'SELECT
    Game
    , Mode' + CHAR(10)

SELECT @sql2 = @sql2 +
'   , SUM(CASE WHEN Name = ''' + Name + ''' THEN 1 ELSE 0 END) AS ' + QUOTENAME(Name) + CHAR(10)
FROM(
    SELECT DISTINCT Name FROM Scores
)t
ORDER BY Name

SELECT @sql3 = 
'FROM Scores
GROUP BY Game, Mode
ORDER BY Game, Mode'

PRINT(@sql1 + @sql2 + @sql3)
EXEC(@sql1 + @sql2 + @sql3)
Felix Pamittan
  • 31,544
  • 7
  • 41
  • 67
0

This can be done using PIVOT. Below query - 

SELECT [Game],[Mode],[Player A],[Player B],[Player C]
FROM
(
SELECT Name,Mode,Game
FROM <TABLENAME>) AS SourceTable
PIVOT(
COUNT(Name) FOR Name IN ([Player A],[Player B],[Player C])
) AS PivotTable;
Abhishek
  • 2,482
  • 1
  • 21
  • 32