Database Table row-to-column, column-to-row Ultimate Solution

From: http://blog.csdn.net/liangpei2008/archive/2006/07/08/893945.aspx

-- 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 1
above to below
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

You can.