Create a dynamic view

Http://www.cbf107.com/CBF107Item.aspx?ID=79c98c3e-aea2-46f9-baa7-1ccd067e2a81

SQLServer2005 Pivot Transpose using dynamic columns (apply to view)

SQLServer2005 Pivot Transpose using dynamic columns (apply to view)

The recent project used pivot to transpose the table, encountered some problems, mainly pivot transpose when there is no way to dynamically generate transpose column name, and when the view is also very need to dynamically produce these columns, Baidu does not seem to find a very satisfactory answer, Google on the search for a foreigner's solution, Now I summed up a bit, I hope to give some help to the friends on the use.

1. Create a table script

if exists (select 1

From sysobjects

WHERE id = object_id (' insurances ')

and type = ' U ')

drop table Insurances

Go

/*==============================================================*/

/* Table:insurances */

/*==============================================================*/

CREATE TABLE Insurances (

RefID uniqueidentifier NOT NULL,

HRMS nvarchar () null,

Name nvarchar () null,

Insurancemoney money NULL,

Insurancename nvarchar (+) NOT NULL,

Constraint Pk_insurances primary KEY (RefID)

)

Go

2. Test Data Script

INSERT into insurances values (NEWID (), 1, ' Zhang San ', 200, ' Endowment insurance ')

INSERT into insurances values (NEWID (), 1, ' Zhang San ', 300, ' medical insurance ')

INSERT into insurances values (NEWID (), 2, ' John Doe ', 250, ' Endowment insurance ')

INSERT into insurances values (NEWID (), 2, ' John Doe ', 350, ' medical insurance ')

INSERT into insurances values (NEWID (), 3, ' King II ', 150, ' Endowment Insurance ')

INSERT into insurances values (NEWID (), 3, ' King II ', 300, ' medical insurance ')

3. Querying table Data

Select Hrms,name,insurancemoney,insurancename from Insurances

HRMS Name Insurancemoney Insurancename

-------------------- -------------------- --------------------- ----------

1 three 200.00 old-age insurance

2 John Doe 350.00 Medical Insurance

2 John Doe 250.00 Endowment Insurance

1 sheets of 300.00 Medical insurance

3 Wang II 300.00 Medical Insurance

3 Wang II 150.00 Endowment Insurance

4. Transpose table Data

SELECT * FROM

(

Select Hrms,name,insurancemoney,insurancename from Insurances

) p

Pivot (

SUM (Insurancemoney)

For Insurancename in

([Medical insurance], [endowment insurance]))

As Pvt

HRMS Name Medical Insurance Endowment Insurance

-------------------- -------------------- --------------------- ---------------------

2 John Doe 350.00 250.00

3 Wang II 300.00 150.00

1 sheets of three 300.00 200.00

5. Occasional questions

In this statement, Medicare, pension insurance is written in SQL statements, and Sql2005 in this code does not have the means to use dynamic query result set

5. The stored procedure solves the problem

So if you want to dynamically complete a script, you can first spell out the SQL and then execute it via exec sp_executesql.

Implementing Stored Procedures

CREATE PROCEDURE Insurancepivot

As

Begin

DECLARE @ColumnNames VARCHAR (3000)

SET @ColumnNames = ' '

SELECT

@ColumnNames = @ColumnNames + ' [' + Insurancename + '], '

From

(

SELECT DISTINCT Insurancename from Insurances

) T

SET @ColumnNames = Left (@ColumnNames, LEN (@ColumnNames)-1)

DECLARE @selectSQL NVARCHAR (3000)

SET @selectSQL =

' SELECT hrms,name,{0} from

(

SELECT Hrms,name,insurancemoney,insurancename from Insurances

) p

Pivot (Max (Insurancemoney) for Insurancename in ({0})) as Pvt

ORDER by HRMS '

SET @selectSQL = REPLACE (@selectSQL, ' {0} ', @ColumnNames)

EXEC sp_executesql @selectSQL

End

To test the stored procedure:

EXEC Insurancepivot

HRMS Name Endowment Insurance Medical Insurance

-------------------- -------------------- --------------------- ---------------------

1 sheets of three 200.00 300.00

2 John Doe 250.00 350.00

3 Wang II 150.00 300.00

6. New issues and solutions for views

There is no way to call this stored procedure directly in the view, but we need it when we do the program and make the report.

Can actually be achieved through OPENQUERY (this is an informal solution, but can now be achieved)

(You can also use OPENROWSET, but the parameters are too many and I give up)

The format for using OPENQUERY is: OPENQUERY ([linked server], ' SQL statement ')

Because it is a view of the current data, the linked server can be viewed through properties, MSCBF107 is the linked server I tested

You can also view it through sp_helpserver

The following sentence is also very important, using a friend to replace [MSCBF107] is OK, otherwise use openquery will appear not to configure the server ' MSCBF107 ' for data ACCESS

sp_serveroption [MSCBF107], ' Data Access ', ' True '

Create the view as follows:

CREATE VIEW Insurancepivotview

As

Select *from OPENQUERY ([mscbf107],n ' SET fmtonly off;exec test.dbo.InsurancePivot ')

Test the view to get the results you want.

Select *from Insurancepivotview

That ' s all

Create a dynamic view