Mssql database table row-to-column, column-to-row Ultimate Solution

Copy codeThe Code is as follows:
-- Row-to-column Conversion
-- Establish a runtime environment
Create Table TEST
(DATES Varchar (6 ),
EMPNO Varchar (5 ),
STYPE Varchar (1 ),
AMOUNT Int)
-- Insert data records
Insert TEST Select '200', '200', 'A', 5
Union All Select '20180101', '20180101', 'B', 3
Union All Select '20180101', '20180101', 'C', 3
Union All Select '20180101', '20180101', 'D', 2
Union All Select '20180101', '20180101', 'E', 9
Union All Select '20180101', '20180101', 'F', 7
Union All Select '20180101', '20180101', 'G', 6
Union All Select '20180101', '20180101', 'A', 7
Union All Select '20180101', '20180101', 'B', 8
Union All Select '20180101', '20180101', 'C', 0
Union All Select '20180101', '20180101', 'D', 3
Union All Select '20180101', '20180101', 'E', 4
Union All Select '20180101', '20180101', 'F', 5
Union All Select '20180101', '20180101', 'G', 1
GO
-- Zookeeper
-- If STYPE is fixed, you can do so.
Select
DATES,
EMPNO,
SUM (Case STYPE When 'A' Then AMOUNT Else 0 End) As,
SUM (Case STYPE When 'B' Then AMOUNT Else 0 End) As B,
SUM (Case STYPE When 'C' Then AMOUNT Else 0 End) As C,
SUM (Case STYPE When 'D' Then AMOUNT Else 0 End) As D,
SUM (Case STYPE When 'E' Then AMOUNT Else 0 End) As E,
SUM (Case STYPE When 'F' Then AMOUNT Else 0 End) As F,
SUM (Case STYPE When 'G' Then AMOUNT Else 0 End) As G
From TEST
Group By DATES, EMPNO
Order By DATES, EMPNO

-- If STYPE is not fixed, use an animated sentence
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 transposed numeric type is used, 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) A Order By STYPE
SET @ S = @ S + 'from test group by dates, empno'
EXEC (@ S)

If it is a column-to-row operation, you can directly Union All.

For example:

City style color 46 48 50 52
Changsha S6MF01002 152 1 2 2 1
Changsha S6MF01002 201 1 2 2 1
From above to below
City style color size qty
Changsha S6MF01002 152 46 1
Changsha S6MF01002 152 48 2
Changsha S6MF01002 152 50 2
Changsha s6mf0100 2 152 52 1
Changsha S6MF01002 201 46 1
Changsha S6MF01002 201 48 2
Changsha S6MF01002 201 50 2
Changsha s6mf0100 2 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

You can.