CODEWITHSUNDEEP
sql-serverpivot-table
Steve H
Steve H

Reputation: 63

SQL Pivot Table combine multi rows

I have the following data

+----+-----------+---------+------+------------+-------------+------------+
| id | selection | against | runs | firstplace | secondplace | thirdplace |
+----+-----------+---------+------+------------+-------------+------------+
|  1 | a         | a       |    0 |          0 |           0 |          0 |
|  2 | a         | b       |   20 |          0 |           3 |          0 |
|  3 | a         | c       |   10 |          0 |           3 |          0 |
|  4 | a         | d       |   19 |          0 |           3 |          0 |
|  5 | b         | a       |   20 |          2 |           4 |          1 |
|  6 | b         | b       |    0 |          0 |           0 |          0 |
|  7 | b         | c       |   14 |          2 |           4 |          1 |
|  8 | b         | d       |   23 |          2 |           4 |          1 |
|  9 | c         | a       |   10 |          0 |           0 |          0 |
| 10 | c         | b       |   14 |          0 |           0 |          0 |
| 11 | c         | c       |    0 |          0 |           0 |          0 |
| 12 | c         | d       |   13 |          0 |           0 |          0 |
| 13 | d         | a       |   19 |          1 |           2 |          1 |
| 14 | d         | b       |   23 |          1 |           2 |          1 |
| 15 | d         | c       |   13 |          1 |           2 |          1 |
| 16 | d         | d       |    0 |          0 |           0 |          0 |
+----+-----------+---------+------+------------+-------------+------------+

As I understand it I need to create a pivot query in order to return the data as

+---+------------+-------------+------------+------------+
|   |     a      |      b      |      c     |      d     |
+---+------------+-------------+------------+------------+
| a | 0  [0/0/0] | 20 [0/2/0]  | 10 [0/3/0] | 19 [0/3/0] |
| b | 20 [2/4/1] | 0  [0/0/0]  | 14 [2/4/1] | 23 [2/4/1] |
| c | 10 [0/0/0] | 14 [0/0/0]  | 0  [0/0/0] | 13 [0/0/0] |
| d | 19 [1/2/1] | 23 [13/2/1] | 13 [1/2/1] | 0  [0/0/0] |
+---+------------+-------------+------------+------------+

The format is runs and then first, second and third data within the []

Selection the columns, against the rows.

I have looked though all the examples here regarding pivot tables but they all have a calculation and only seem to have 2 or 3 columns.

Am I going about this in the correct way i.e. pivot table or do I need to look at something else?

Many thanks.

Upvotes: 0

Views: 42

Answers (2)

xQbert
xQbert

Reputation: 35323

A slightly different approach from Gordon's more elegant one.

SELECT selection, 
max(case when against = 'a' then
  runs + " ["+ firstPlace +"/"+secondPlace+"/"+thirdPlace+"]" end) as "A",
max(case when against = 'b' then
  runs + " ["+ firstPlace +"/"+secondPlace+"/"+thirdPlace+"]" end) as "B",
max(case when against = 'c' then
  runs + " ["+ firstPlace +"/"+secondPlace+"/"+thirdPlace+"]" end) as "C",
max(case when against = 'D' then
  runs + " ["+ firstPlace +"/"+secondPlace+"/"+thirdPlace+"]" end) as "D",
FROM tableName
GROUP BY selection

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269583

If you have four groups, then you can write query to do the pivoting, either using pivot or conditional aggregation:

select selection,
       max(case when against = 'a' then val end) as a,
       max(case when against = 'b' then val end) as b,
       max(case when against = 'c' then val end) as c,
       max(case when against = 'd' then val end) as d
from (select t.*,
             replace(replace(replace(replace('@1 [@2/@3/@4]', @1, runs
                                            ), @2, firstplace
                                    ), @3, secondplace
                            ), @4, thirdplace
                    ) as val
      from t
     ) t
group by selection;

If you don't know the groups, then you need a dynamic pivot. I would suggest that you Google "SQL Server dynamic pivot" to get some approaches for solving that problem.

Upvotes: 1

Related Questions