Need Help to transpose the result in SQL

Other SQL Server Topics
1

I'm new here and want to learn more in SQL. i'm trying to transpose and create new column for the result of my table.

From This :
| Final Score | COUNT(*) |

| A | 12 |
| B | 13 |
| C | 14 |
| D | 15 |
| E | 16 |
| F | 17 |

To This.
|Final Score | Player-A | Player-B | Player-C | Player-D | Player-E | Player-F |

| Count | 12 | 13 | 14 | 15 | 16 | 17 |

2

Hi,

I am new on this forum too. It does not seem to allow links, not even to another question on this very same forum. You will have to search for "another pivot query" yourself on this forum.
It addresses the same issue. Hope it helps.

With kind regard
Wim

3

create table #Test (Player varchar(255), Score int)
insert into #Test values
('A',1)
,('A',1)
,('A',1)
,('A',1)
,('A',1)
,('A',1)
,('A',1)
,('A',1)
,('A',1)
,('A',1)
,('A',1)
,('A',1)
,('B',1)
,('B',1)
,('B',1)
,('B',1)
,('B',1)
,('B',1)
,('B',1)
,('B',1)
,('B',1)
,('B',1)
,('B',1)
,('B',1)
,('B',1)
,('C',1)
,('C',1)
,('C',1)
,('C',1)
,('C',1)
,('C',1)
,('C',1)
,('C',1)
,('C',1)
,('C',1)
,('C',1)
,('C',1)
,('C',1)
,('C',1)

--original

Select
player
,count(*) as Score
from #Test
group by player

--Solution

select
sum(case when player = 'A' then 1 else 0 end) as 'player-A'
,sum(case when player = 'B' then 1 else 0 end) as 'player-B'
,sum(case when player = 'C' then 1 else 0 end) as 'player-C'
from #Test

5

thanks, i already transpose the output, can you help me again on how to add text in the results?

From This :
| Final Score | COUNT(*) |

| A | 12 |
| B | 13 |
| C | 14 |
| D | 15 |
| E | 16 |
| F | 17 |

To This :
| Final Score | COUNT(*) |

| A - Mark | 12 |
| B - Chris | 13 |
| C - Lauren | 14 |
| D - DJ | 15 |
| E - Ralph | 16 |
| F - Kim | 17 |

6

drop table #temp
create table #temp
(
FinalScore varchar(50),
A int,
B int,
C int,
D int,
E int,
F int
)
insert into #temp
select row_number() over(order by A) as FinalScore,A,B,C, D,E, F
from
(
select FinalScore,countall
from score
) src
pivot
(
max(countall)
for FinalScore in ([A], [B], [C], [D], [E], [F])
) piv

update #temp
set FinalScore = 'Count' where FinalScore = 1

select FinalScore, A as 'Player-A',B as 'Player-B',C as 'Player-C',D as 'Player-D',E as 'Player-E',F as 'player-F' from #temp

7

im using sqlDBX

8

This is my result and i want to add a text in the Final Score result (A - Mark).

| Final Score | COUNT(*) |

| A | 12 |
| B | 13 |
| C | 14 |
| D | 15 |
| E | 16 |
| F | 17 |

I think i need to use concat for this.

9

image

hahahha, why mark is all over the result of final,, how can i change it based on their Letter