-1

enter image description here

I have a table above and I want to convert it to a table as shown below

enter image description here

What methods can I use to do this?

I tried using PIVOT but I don't know how to change the Score column

So I hope I can find a solution to the above problem

I have done that But I want to use a table variable instead of a table. How to do it?

I want to replace Table your_table_name with a variable @table.

But SQL says Must declare the scalar variable "@table" but I have declared it before.

enter image description here

TCNJK
  • 1
  • 1
  • 1
    [Please don't post code or data as images](https://meta.stackoverflow.com/q/285551/20091109). – Stuck at 1337 Oct 25 '22 at 18:42
  • Posting images of technical information like code or sample data, instead of formatted text, is the fastest way I've seen here other than spam for a question to be downvoted and then closed or ignored. – Joel Coehoorn Oct 26 '22 at 02:29

3 Answers3

1

It's hard to tell what you tried with PIVOT and why it didn't work, but:

SELECT Name, aa, bb, cc
  FROM dbo.YourTableName
  PIVOT 
  (
    MAX(score) FOR subjects IN ([aa],[bb],[cc])
  ) AS p;

Working example in this fiddle.

Stuck at 1337
  • 1,900
  • 1
  • 3
  • 13
1

based on your comments, you want to use table variables. For this reason, you will need to create a table type

use below example

CREATE TYPE MyTableType AS TABLE 
(Name char(1), subjects char(2), score int);
GO


declare @sql as Nvarchar(max);

declare @your_table_name AS MyTableType; 

INSERT INTO @your_table_name VALUES
('a', 'aa', 5),
('a', 'bb', 6),
('a', 'cc', 3),
('b', 'bb', 7),
('b', 'cc', 8);

 
select @sql = N'select [name],' + stuff((
    select distinct 
    ',max(case [subjects] when ' + char(39) + [subjects] + char(39) 
    + 
' then [score] end) [' + [subjects] + ']'
    from @your_table_name
    for xml path('')
), 1, 1, '');

select @sql += N'from @your_table_name group by [name];';


PRINT @SQL
EXEC sp_executesql @SQL,
  N'@your_table_name MyTableType READONLY',
  @your_table_name=@your_table_name

I found this method here and this answer explains in a better way https://stackoverflow.com/a/12876775/13800469

Rand0m _
  • 31
  • 3
0

You can do this by executing a dynamically creates query.

Query

declare @sql as varchar(max);
select @sql = 'select [name],' + stuff((
    select distinct 
    ',max(case [subjects] when ' + char(39) + [subjects] + char(39) 
    + 
' then [score] end) [' + [subjects] + ']'
    from your_table_name
    for xml path('')
), 1, 1, '');

select @sql += ' from your_table_name group by [name];';
exec(@sql);

Find demo here

Ullas
  • 11,450
  • 4
  • 33
  • 50
  • I have done that, but I want to use a table variable instead of a table. How to do it? I want to replace Table your_table_name with a variable @table. But SQL say Must declare the scalar variable "@table" when I have it before. – TCNJK Oct 26 '22 at 02:13