pivot table sql server

Recently, a project was developed to replace the data table prefix with different schemas (database architecture) when creating a database. This design makes the data table name Friendly and logically clear and more compliant with the SQL Server database specifications. However, the problem occurred yesterday when the

when @xtype= $ Then "" "+convert (char),'+@name+')+ "" " --uniqueidentifier Else @name End End End Fetch Next fromSyscolumns_cursor into @name,@xtype End CloseSyscolumns_cursordeallocateSyscolumns_cursorSet @sql='SET NOCOUNT ON select"'Insert into'+@tablename+'('+@column+') VALUES ("' as"'--"','+@columndata+',"')"' from'+@tablename Print '--'+@

1. Build a table1 IF object_id (n ' table name ', n ' U ') is NULL CREATE table table name (2 ID INT IDENTITY (1, 1) PRIMARY KEY ,......);2. Query all tables that meet the criteria1 SELECT 2 NAME3from4 sys.objects5WHERE 6 type = ' u '7 and NAME like ' test_% ';3. Bulk delete a table that meets the crite

the partition field Drop Index L_testresult. Idx_l_testresult_measuretime --Create an index by partition scheme CREATE INDEX Idx_l_testresult_measuretime On L_testresult (Measuretime) On Monthdaterangescheme (Measuretime) 6. For a clustered index in a partitioned table, it is best to remove the rebuild: --Rebuilding the main building of clustered index ALTER TABLE L_testresult DROP CONSTRAINT Pk_l_testres

When a user changes the table structure in SQL Server 2008 Enterprise Manager, you must delete the original table and recreate the new table to complete the table changes, and if you forcibly change the following prompt: Save chan

: 2008-09-12 10: 32 created by: Xiaoqian*/Create procedure [DBO]. [orders_copartner_update] ...... 10. Register SQL cache Dependencies Aspnet_regsql-s 192.168.5.211-U sa-P 123456-D airticket-edAspnet_regsql-s 192.168.5.211-U sa-P 123456-D airticket-T airdistance-et Run the command in the Visual Studio 2005 Command Prompt window.(Table aspnet_sqlcachetablesforchangenotification automatic

Label:one of the fields in a table student (Sno, sname, age) sname type nchar (TEN) Change to int Save the Times wrong. This is the default setting for SQL Server itself, which defaults to a type that cannot be modified. You only need to remove prvent saving changes in the Tool->option->designer->table and Database d

In SQL server, how can I use SQL statements to add comments to the column of a table? I didn't use SQL statements to add them before, but I added them to Enterprise Manager: (I checked the information, microsoft introduced extended attributes in

\ ' database name \ ', \ ' autoshrink\ ', \ ' true\ '\\\ ' Add field general functionSub AddColumn (Tablename,columnname,columntype)Conn.execute (\ "Alter Table \" tablename\ "ADD \" columnname\ "\" columntype\ "\")End Sub\\\ ' Change field general functionSub Modcolumn (Tablename,columnname,columntype)Conn.execute (\ "Alter Table \" tablename\ "alter Column \" columnname\ "\" columntype\ "\")End Sub\\\ '

Today, I am struggling with this problem for a day. The following code is reproduced on the Internet. Create Partition Function pf_orders_orderdaterange (datetime)AsRange right for values ('2017-01-01 ','2017-01-01 ','2017-01-01')Go-- Create a partition schemeCreate Partition Scheme ps_ordersAsPartition pf_orders_orderdaterangeTo ([primary], [primary], [primary], [primary])Go-- Create a partition tableSp_rename 'dbo. Orders ', 'Orders _ from_sql2000_northwind'Create

Label: 1 SELECT( Case whenA.colorder=1 ThenD.nameElse NULL End) Table name,2 a.colorder field ordinal, a.name field name,3( Case when ColumnProperty(A.id,a.name,'isidentity')=1 Then '√'Else "' End) identification,4( Case when(SELECT Count(*) fromsysobjects5 WHERE(Nameinch(SELECTName fromsysindexes6 WHERE(ID=a.ID) and(indidinch 7(SELECTIndid fromSysindexkeys8 WHERE(ID=a.ID) and(colidinch 9(SELECTColid fromsyscolumnsWHERE(ID=a.ID) and(Name=( a.

Oracle database The process of creating temporary tables and the contrast with the different points of the SQL Server Temp table is the main content of this article, let's take a look at this part of the content, we hope to be able to help you. 1. Introduction In addition to saving permanent tables, Oracle databases can also create temporary tables temporary ta

SQL server table data change trigger mail sending method, SQL send mail Today, I encountered a problem where the original production system is running properly. Now I need to monitor a data table and send an email to the Administrator when adding data. After receiving this r

Transferred from: http://www.cnblogs.com/yugen/archive/2010/07/25/1784749.html1. Determine if the data table existsMethod One:Use Yourdb;GoIf OBJECT_ID (n ' tablename ', n ' U ') is not nullprint ' exists 'Elseprint ' does not exist 'For example:Use Fireweb;GoIf OBJECT_ID (n ' temp_tbl ', n ' U ') is not nullprint ' exists 'Elseprint ' does not exist 'Method Two:Use [instance name]GOIF EXISTS (SELECT * FROM dbo. SysObjects WHERE id = object_id (N ' [

property_name = 'Ms _ description', add a comment [@ Value =] {'value '}The value to be associated with the attribute. The data type of value is SQL _variant, with the default value null. The value size cannot exceed 7,500 bytes; otherwise, SQL Server will produce an error. [@ Level0type =] {'level0 _ object_type '}User or user-defined type. The data type of lev

Previous examples:Http://www.cnblogs.com/insus/articles/1969896.htmlHttp://www.cnblogs.com/insus/articles/1970577.htmlHttp://www.cnblogs.com/insus/articles/1970707.htmlThese are all examples of SQL Server row and column conversions or row conversions. However, at the beginning of the SQL Server2005 version, there is a new function

follows:KILL 53==================================================================sp_lock– query which process lock table, SPID: Process Id,objid: Object IDEXEC sp_executesql N ' kill [spid] ' – Kill processSelect object_name ([ObjId]) – query which table is locked and find the one whose ObjId is not 0– View with SQL statement, locked

Tags: style time span stat name not sel tab DUPI'm using a statement to save the queried data to a new table 1) using the into table statement, the new table that needs to be saved does not need to be created in advance Select * to from table-Insert new Table statement in

, Enddate DATE, Class CHAR (20 )) On commit preserve rows; EXAMPLE: Session 1: SQL> drop table admin_work_area; SQL> CREATE GLOBAL TEMPORARY TABLE admin_work_area 2 (startdate DATE, 3 enddate DATE, 4 class CHAR (20 )) 5 on commit preserve rows; SQL> insert into permernate v

--1, data table already created, Excel first row as table header--Enable AD Hoc distributed Queries:exec sp_configure ' show advanced options ', 1Reconfigureexec sp_configure ' Ad Hoc distributed Queries ', 1ReconfigureGo--Start querying dataIf object_id (' temp_xxx ') is not nulldrop table temp_xxx GoSELECT * into temp_xxx from OpenDataSource (' microsoft.ace.ol