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
Ïà¹ØÎĵµ£º
SQL Server´´½¨ÁÙʱ±í£º
´´½¨ÁÙʱ±í
·½·¨Ò»£º
create table #ÁÙʱ±íÃû(×Ö¶Î1 Ô¼ÊøÌõ¼þ,
×Ö¶Î2 Ô¼ÊøÌõ¼þ,
.....)
create table ##ÁÙʱ±íÃû(×Ö¶Î1 Ô¼ÊøÌõ¼þ,
×Ö¶Î2 Ô¼ÊøÌõ¼þ,
.....)
·½·¨¶þ£º
select * into #ÁÙʱ±íÃû from ÄãµÄ±í;
select * into ##ÁÙʱ±íÃû from ÄãµÄ±í;
×¢£ºÒÔÉϵÄ#´ú±í¾Ö²¿ÁÙʱ±í£¬##´ú±íÈ«¾ ......
Çë½Ì¸ßÊÖÒ»¸öÎÊÌ⣬ÎÊÌâÃèÊöÈçÏ£º
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¸öºÅÂë
Ç ......
ÎÊÌâÃèÊö£º
Express°æÓÃsaÕÊ»§µÇ¼ʱʧ°Ü
½â¾ö·½°¸£º
µÚÒ»²½£ºÓÃWindowsÉí·ÝµÇ¼Management Studio£¬ÓÒ»÷·þÎñÆ÷Ñ¡ÔñÊôÐÔ£¬½«°²È«ÐÔÑ¡Ïî ¸ü¸ÄΪ:SQL Server ºÍ Windows
Éí·ÝÑéÖ¤
& ......
Ò»¡¢½¨Á¢Á´½Ó·þÎñÆ÷
ÓÐÈËϲ»¶µ÷ÓÃϵͳ¹ý³ÌÀ´½¨Á¢£¬µ«ÎÒ¸öÈ˶Ôϵͳ¹ý³ÌûÓÐÌرðµÄѧϰ £¬ËùÒÔÓõÄÊǽçÃæÉèÖ㬵±È»ÓÐÐËȤҲ¿ÉÒÔÑо¿Ò»Ïµģ¬ÒòΪ¿ÉÒÔ°ÑSQLÖ´Ðе¼³öÀ´¡£
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'TEST2', @srvproduct=N'ORCL', @provider=N ......
public class SqlCheck
{
public SqlCheck()
{
//
// TODO: ÔÚ´Ë´¦Ìí¼Ó¹¹Ô캯ÊýÂß¼
& ......