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

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.