SQL Server dynamic row to column

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