Use aggregate functions with case statements to implement row-to-column functionality:
Now share the specific implementation code:
Pre-conversion effects:
PlanName |
Plantype |
Planlimit |
Program 1 |
Plan Type 1 |
RMB 1,000,000 |
Program 1 |
Plan Type 2 |
RMB 1,000,000 |
Program 1 |
Plan Type 3 |
RMB 1,000,000 |
Program 2 |
Plan Type 1 |
RMB 1,000,000 |
Program 2 |
Plan Type 2 |
RMB 1,000,000 |
Program 2 |
Plan Type 3 |
RMB 1,000,000 |
Program 3 |
Plan Type 1 |
RMB 1,000,000 |
Program 3 |
Plan Type 2 |
RMB 1,000,000 |
Program 3 |
Plan Type 3 |
RMB 1,000,000 |
1. Static implementation of row to column
1With main as2 (3 Select 'Program 1' asPlanName,'plan Type 1' asPlantype,'$1,000,000' asPlanlimit4 Union All5 Select 'Program 1' asPlanName,'Plan Type 2' asPlantype,'$1,000,000' asPlanlimit6 Union All7 Select 'Program 1' asPlanName,'plan Type 3' asPlantype,'$1,000,000' asPlanlimit8 Union All9 Select 'Program 2' asPlanName,'plan Type 1' asPlantype,'$1,000,000' asPlanlimitTen Union All One Select 'Program 2' asPlanName,'Plan Type 2' asPlantype,'$1,000,000' asPlanlimit A Union All - Select 'Program 2' asPlanName,'plan Type 3' asPlantype,'$1,000,000' asPlanlimit - Union All the Select 'Program 3' asPlanName,'plan Type 1' asPlantype,'$1,000,000' asPlanlimit - Union All - Select 'Program 3' asPlanName,'Plan Type 2' asPlantype,'$1,000,000' asPlanlimit - Union All + Select 'Program 3' asPlanName,'plan Type 3' asPlantype,'$1,000,000' asPlanlimit - ) + SelectPlantype as[Plan] A,'Program 1'=max ( CasePlanName when'Program 1'Then PlanlimitElse NULLend) at,'Program 2'=max ( CasePlanName when'Program 2'Then PlanlimitElse NULLend) -,'Program 3'=max ( CasePlanName when'Program 3'Then PlanlimitElse NULLend) - fromMain - where 1=1 -GROUP BY Plantype
2. Dynamic implementation of row to column
1-- =============================================2--Author: <Anne>3--Create Date: < ./Geneva/ ->4--Description: < querying data for Sp_annetest tables >5-- =============================================6 CREATE PROCEDURE [dbo]. [Sp_annetest]7 (8 )9 asTenDECLARE @sql varchar (8000) One BEGIN A Set@sql ="' - - Select@[email protected]+','+" '"+[planname]+" '"+'=max (case PlanName when" "+[planname]+" "Then planlimit else null end)' the fromMain - where 1=1 - GROUP BY Plantype - Set@sql ='Select Plantype as ["'Plan"']'[Email protected]+' + fromMain - where 1=1 +GROUP BY Plantype' A at Print @sql - exec (@sql) - - END -GO
Implementation results:
Plan |
Program 1 |
Program 2 |
Program 3 |
Plan Type 1 |
RMB 1,000,000 |
RMB 1,000,000 |
RMB 1,000,000 |
Plan Type 2 |
RMB 1,000,000 |
RMB 1,000,000 |
RMB 1,000,000 |
Plan Type 3 |
RMB 1,000,000 |
RMB 1,000,000 |
RMB 1,000,000 |
SQL Server dynamic row to column