SQL Server2005 ÖÐÓà Pivot Éú³É½»²æ±í
×î½üÔÚ×öÒ»¸öÊý¾Ýͳ¼Æ£¬ÒªÇó¶ÔÒ»¸ö±íÖеÄÊý¾Ý°´ÕÕÁ½¸öά¶È³ÊÏÖ£¬Ò²¾ÍÊÇ´«Í³µÄ½»²æ±í
±ÈÈ磬ÓÐÒ»¸öÎÊÌâ±í£¬ÓÐÈý¸ö×ֶΣ¬£¨±êÌâ¡¢ÎÊÌâÀà±ð¡¢ÎÊÌâ״̬£©
ÒªÇó°´ÕÕ²»Í¬µÄÀà±ð£¬·Ö±ðͳ¼Æ´¦¸÷¸ö״̬µÄÎÊÌâÊýÁ¿£¨È磺²úÆ·ÎÊÌâÖÐδ´¦ÀíµÄÊýÁ¿¡¢·þÎñÎÊÌâÖÐÒÅÁôÎÊÌâÊýÁ¿µÈµÈ£©¡£
¾¹ý²éÕҺͳ¢ÊÔ£¬ÖÕÓÚÉú³ÉÁ˽á¹û£¬ÏÖÔÚ·ÖÏí¸ø´ó¼Ò¡£
ͨ¹ý Sql 2005 ÖÐµÄ Pivot º¯Êý£¬¿ÉÒÔ·½±ãµÄÖÆ×÷½»²æ±í¡£
¹ØÓÚPivotµÄÓ÷¨£¬¾Í²»Ïêϸ˵ÁË£¬´ó¼Ò¿ÉÒÔ°Ù¶Èһϣ¬»òÕß¿´sql serverµÄ°ïÖúÎĵµ
ÏÂÃæÊÇ´úÂ벿·Ö
--ÎÊÌâ·ÖÀà±í
CREATE TABLE [dbo].[QuestionClass](
[QuestionClassID] [int] IDENTITY(1,1) NOT NULL,
[QuestionClassName] [nvarchar](50) NULL,
CONSTRAINT [PK_QuestionClass] PRIMARY KEY CLUSTERED
(
[QuestionClassID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--ÎÊÌâ״̬±í
CREATE TABLE [dbo].[QuestionState](
[QuestionStateID] [int] IDENTITY(1,1) NOT NULL,
[QuestionStateName] [nvarchar](50) NULL,
CONSTRAINT [PK_QuestionState] PRIMARY KEY CLUSTERED
(
[QuestionStateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--ÎÊÌâ¼Ç¼±í
CREATE TABLE [dbo].[Question](
[QuestionID] [int] IDENTITY(1,1) NOT NULL,
[Topic] [nvarchar](50) NULL,
[ClassID] [int] NULL,
[StateID] [int] NULL,
CONSTRAINT [PK_Question] PRIMARY KEY CLUSTERED
(
[QuestionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--³õʼ»¯×´Ì¬Êý¾Ý
insert into QuestionState(QuestionStateName)
select 'δ´¦Àí' union all
select 'ÒÑ´¦Àí' union all
select 'ÎÞЧ·´À¡'
--³õʼ»¯Àà±ðÊý¾Ý
insert into QuestionClass(QuestionClassName)
select '²úÆ·ÎÊÌâ' union all
select 'ÏúÊÛÎÊÌâ' union all
select '·þÎñÎÊÌâ' union all
select 'ÆäËûÎÊÌâ'
--³õʼ»¯ÎÊÌâÊý¾Ý
insert into Question(Topic,ClassID,St
Ïà¹ØÎĵµ£º
declare @tablename varchar(20)
select @tablename = 'o_ems'
-------------Éú³É±äÁ¿,ʹÓñí¸ñÐÎʽ(ctrl + D)Êä³ö£¬È¡µÚÒ»¸ö×ֶεÄÖµ----------------------------------
select 'private '+
Case when t.name='varchar' Then 'string'
when t.name='char' Then 'string' &nb ......
Çë½Ì¸ßÊÖÒ»¸öÎÊÌ⣬ÎÊÌâÃèÊöÈçÏ£º
A±íÊǸ÷¸öµ¥Î»µÄÃû³Æ±í ×Ö¶ÎΪ org_id ºÍ org_name
B±íÊÇÕâЩµ¥Î»µÄµç»°ºÅÂë±í ×Ö¶ÎΪ org_idºÍ tel
A±í B±í¹ØÁª·½Ê½ÎªA.ORG_ID=B.ORG_ID
org_idÊǸ÷¸öµ¥Î»µÄ´úÂë
org_nameÊǸ÷¸öµ¥Î»µÄÃû³Æ
telÊǸ÷¸öµ¥Î»µÄµç»°ºÅÂë
±¾ÈËÏÖÔÚÏëÕë¶Ôÿ¸öµ¥Î»£¨Ã¿Ìõorg_id£©È¡Æä10¸öºÅÂë
Ç ......
±¾ÎĽéÉÜÁËSQL Server 2005ÖÐÉÙÊýÈËÓõ½µÄÁ½Ìõ¾«Æ·ÐÂÓï·¨£¬´ó¼Ò¿´¿´×Ô¼ºÊÇ·ñÖªµÀÄØ……
¡¡¡¡1. OUTPUT ... INTO
¡¡¡¡ÓÃÓÚ½«Ò»Ìõ¼Ç¼´Ó±íÒ»ÒÆ¶¯µ½±í¶þʱ·Ç³£ºÃÓ㬳£¼ûÓÚ±¸·Ý¼Ç¼µÄÓ¦ÓÃ
¡¡¡¡ÀýÒ»£º
¡¡¡¡DELETE [TableUseing]
¡¡¡¡OUTPUT *
¡¡¡¡INTO [TableBak]
¡¡¡¡Àý¶þ£º(ÓÃÓÚÒÆ¶¯Ê±ÐÞ ......
SELECTÓï¾äµÄÍêÕûÓ﷨Ϊ£º
SELECT[ALL|DISTINCT|DISTINCTROW|TOP]
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}
from tableexpression[,…][IN externaldatabase]
[WHERE…]
[GROUP BY…]
[HAVING…]
[ORDER BY…]
[WITH OWNERACCESS OPTION]
×¢ ......
Èç¹ûÄÜ´Ó±¸·ÝÎļþÖÐÖ»»Ö¸´Ò»¸ö±íµÄÊý¾Ý£¬ÄDz»ÊǺܺÃÂ𣿱ÈÈ磬Ä㱸·ÝÁËAdventureWorksÊý¾Ý¿â£¬ÏÖµÄÄãÖ»»Ö¸´ÀïÃæVendor±íÊý¾Ý¡£²»ÐÒµÄÊÇ£¬SQL Server±¾Éí²¢²»Ö§³ÖÕâÑù»¹Ô£¬ÄãÐèÒª´ÓµÚÈý·½ÌṩµÄ¹¤¾ßÖÐÀ´Ö´ÐÐÕâÑùµÄÈÎÎñ¡£
ÌṩÕâÖÖ¹¦ÄܵijÌÐò¶¼ÊÇһЩSQL ServerµÚÈý·½±¸·Ý¹¤¾ß¡£ËüÃÇ¿ÉÒÔÈÃÄã´Ó±¸·ÝÎļþÖгéÈ¡»òÊǶÁÈ¡µ¥¸ö±í ......