MSSQL database table row column, column change final program

Copy CodeThe code is as follows:
--row-and-column issues
--Build a test environment
Create Table TEST
(Dates Varchar (6),
EMPNO Varchar (5),
Stype Varchar (1),
AMOUNT Int)
--Inserting data
Insert TEST Select ' 200605 ', ' 02436 ', ' A ', 5
Union all Select ' 200605 ', ' 02436 ', ' B ', 3
Union all Select ' 200605 ', ' 02436 ', ' C ', 3
Union all Select ' 200605 ', ' 02436 ', ' D ', 2
Union all Select ' 200605 ', ' 02436 ', ' E ', 9
Union all Select ' 200605 ', ' 02436 ', ' F ', 7
Union all Select ' 200605 ', ' 02436 ', ' G ', 6
Union all Select ' 200605 ', ' 02438 ', ' A ', 7
Union all Select ' 200605 ', ' 02438 ', ' B ', 8
Union all Select ' 200605 ', ' 02438 ', ' C ', 0
Union all Select ' 200605 ', ' 02438 ', ' D ', 3
Union all Select ' 200605 ', ' 02438 ', ' E ', 4
Union all Select ' 200605 ', ' 02438 ', ' F ', 5
Union all Select ' 200605 ', ' 02438 ', ' G ', 1
Go
--Tests
--If the stype is fixed, you can write this
Select
Dates,
EMPNO,
SUM (case stype when ' a ' Then AMOUNT Else 0) as A,
SUM (case stype when ' B ' Then AMOUNT Else 0) as B,
SUM (case stype when ' C ' Then AMOUNT Else 0) as C,
SUM (case stype if ' d ' Then AMOUNT Else 0 end) as D,
SUM (case stype when ' e ' Then AMOUNT Else 0) as E,
SUM (case stype when ' F ' Then AMOUNT Else 0) as F,
SUM (case stype when ' G ' Then AMOUNT Else 0) as G
From TEST
Group by Dates,empno
ORDER BY Dates,empno

--If the stype is not fixed, use the motion phrase
Declare @S Varchar (1000)
Set @s= '
Select @s=@s+ ', SUM (case stype when ' +stype+ "' Then AMOUNT Else 0 end) as" +stype from (select Distinct stype from TEST) A ORDER BY Stype
Set @s= ' Select dates,empno ' +@s+ ' from TEST Group by dates,empno order by Dates,empno '
EXEC (@S)
Go
--If the numeric type is being transferred, apply the following statement
DECLARE @S VARCHAR (1000)
SET @s= ' SELECT dates,empno '
SELECT @s=@s+ ', [' +stype+ ']=sum (case when stype= ' +stype+ ' ' THEN ' "AMOUNT ELSE 0 End ') '
From (Select Distinct stype from TEST) an order by stype
SET @s=@s+ ' from TEST GROUP by Dates,empno '
EXEC (@S)

If it's a career change, direct union ALL is OK.

For example:

City Style color 46 48 50 52
Changsha s6mf01002 152 1 2 2 1
Changsha s6mf01002 201 1 2 2 1
Up to the bottom.
City Style Color Size Qty
Changsha s6mf01002 152 46 1
Changsha s6mf01002 152 48 2
Changsha s6mf01002 152 50 2
Changsha s6mf01002 152 52 1
Changsha s6mf01002 201 46 1
Changsha s6mf01002 201 48 2
Changsha s6mf01002 201 50 2
Changsha s6mf01002 201 52 1

Select city,style,color,[46] from Test
Union All
Select city,style,color,[48] from Test
Union All
Select City,style,color,[50] from Test
Union All
Select city,style,color,[52] from Test

It's okay.