Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

SQL ServerÖÐpivot and unpivotµÄÓ÷¨ £¨ÐÐÁл¥×ª£©

 .PivotµÄÓ÷¨Ìå»á:
Óï¾ä·¶Àý:
select PN,[2006/5/30] as [20060530],[2006/6/2] as [20060602]
from consumptiondata a
Pivot (sum(a.M_qty) FOR a.M_date in ([2006/5/30],[2006/6/2])) as PVT 
order by PN
 
Table½á¹¹ Consumptiondata (PN,M_Date,M_qty)
order by PN¿ÉÒª¿É²»Òª,²¢²»ÖØÒª,Ö»ÊÇÅÅÐòµÄ×÷ÓÃ
 
¹Ø¼üµÄÊǺìÉ«²¿·Ö,½âÎöÈçÏÂ,select ´ó¼Ò¶¼ÖªµÀ,PNÊÇ ConsumptionData±íÖеÄÒ»¸öColumn,
[2006/5/30]Ò²ÊÇÒ»¸öColumn,ËûÐèÒªÏÔʾ³É[20060530],×¢Òâ[2006/5/30]²»ÊÇÒ»¸öValue,¶øÊÇÒ»¸öColumn.[2006/6/2]Óë[2006/5/30]Ò»Ñù.
Pivot ( ........... ) as PVTÕâ¸ö½á¹¹Êǹ̶¨¸ñʽ,ûÓÐʲôÐèÒªÌØÊâ˵Ã÷µÄ,µ±È»PVTËæ±ãÄã¸øËûÒ»¸ö NICKNAME ,it doesn't make any differences.
sum(a.M_qty) ÊÇÎÒÃÇÏ£ÍûÏÔʾ³öÀ´µÄÖµ,×¢ÒâÕâ¸öµØ·½±ØÐëÓûã×ܺ¯Êý,·ñÔòÓï·¨²»»á¹ý.
FOR a.M_date in ([2006/5/30],[2006/6/2])for ±íʾ»ã×ܵÄÖµÒªÏÔʾÔÚÄÄÒ»¸öColumnÏÂÃæ
Èç¹ûÎÒÃÇÏëÈÃSum(M_qty)ÏÔʾÔÚPNת»»µÄColumnÏÂÃæ,Ôò¿ÉдΪFor PN, in µÄÇåµ¥±íʾÎÒÃǹØ×¢ÄÄЩҪ²é¿´µÄColumn,×¢ÒâÔÙ´ÎÇ¿µ÷ÊÇColumn,²»ÊÇValue. inµÄÇåµ¥ÊÇColumnÇåµ¥,²»ÊÇValueÇåµ¥,ÊÇM_dateµÄValueת»»³ÉµÄColumnÇåµ¥. 
 
2.UnPivot
--´Ë¶Î¿ÉÒÔÖ±½ÓÔÚSql 2005ÖÐÖ´ÐÐ
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int)
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4)
INSERT INTO pvt VALUES (2,4,1,5,5,5)
INSERT INTO pvt VALUES (3,4,3,5,4,4)
INSERT INTO pvt VALUES (4,4,2,5,5,4)
INSERT INTO pvt VALUES (5,5,1,5,5,5)
GO
--select * from PVT
--Unpivot the table.
SELECT VendorID, Employee, Orders
from PVT
UNPIVOT (
 Orders FOR Employee IN ([Emp1], [Emp2], [Emp3], [Emp4], [Emp5])
)AS unpvt
GO


Ïà¹ØÎĵµ£º

SQL ServerÖб¸·ÝÎļþÀÛ»ýµÄÎÊÌâ

Äã´´½¨µÄÿһ¸ö±¸·Ý¶¼ÊÇÒ»¸ö±¸·ÝÉ豸£¬¹ØÓÚËüµÄϸ½ÚÐÅÏ¢¶¼´æ´¢ÔÚmsdb..backupset±íÖС£Ò»¸ö±¸·ÝÉ豸¿ÉÒÔ±»´æ´¢ÔÚµ¥Ò»Îļþ£¬»òÊǶà¸öÎļþÖС£Í¬Ñù£¬Ò»¸öÎļþÒ²¿ÉÒÔ´æ´¢¶à¸ö±¸·ÝÉ豸¡£
ËùÒÔ£¬¼ÙÈçÄãÿ´Î±¸·Ý¶¼Ê¹ÓÃÏàͬµÄÎļþÃû£¬Îļþ¾Í»áÒ»Ö±Ôö³¤¡£Ò»¸öÆÕ±éµÄÎó½âÊÇ£ºÈç¹ûÄãÿ´ÎʹÓÃÏàͬµÄÎļþÃû£¬ÄǾɵı¸·ÝÉ豸¾Í»á±»¸²¸Ç¡ ......

C#——·ÃÎÊSQL Server 2005¹«¹²Àà

ÏÂÃæÊÇÎÒ×ܽá³öÀ´µÄÒ»¸öÊý¾Ý¿â·ÃÎʹ«¹²À࣬»ùÓÚADO.NET£¬C#µÄ£¬ÆäÖУ¬ÒÔÖØÔØµÄ·½Ê½ÊµÏÖʹÓôæÊô¹ý³ÌµÄ½Ó¿ÚºÍ²»Óô洢¹ý³ÌµÄ½Ó¿Ú£¬ÈçÓв»Í×Çë´ó¼ÒÖ¸Õý£¬Ð»Ð»~
×÷Õߣºshinehoo
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace shineh ......

SQL Server Linked Server ·ÃÎÊÔ¶³ÌÊý¾Ý¿â

ÔÚʹÓÃSQL ServerÊý¾Ý¿âʱ, Èç¹ûÐèÒª·ÃÎÊÔ¶³ÌµÄÁíÍâÒ»¸öÊý¾Ý¿â, ÄÇô¿ÉÒÔʹÓÃLinked ServerÀ´ÊµÏÖÕâ¸ö¹¦ÄÜ, ºÜ·½±ã. ±¾ÎÄ×ܽáÁË×Ô¼ºÔÚʹÓÃLinked Server¹¦ÄÜʱÓöµ½µÄ¼¸¸öÎÊÌâ, Èç¹ûÉÔ²»×¢Òâ,ºÜ¿ÉÄܳöÏÖ¸÷ÖÖ´íÎó.
1)´´½¨ÁËÒ»¸öеÄLinked Serverºó, ÐèÒªÊ×ÏÈÉèÖÃlogin ÕË»§, ÓÒ¼ü->ÊôÐÔ->Security, Èçͼ
......

Sql½á´æÓà

select ÐÕÃû,סַ,ÆÚ³õÓà¶î=isnull(ÆÚ³õÔö¼Ó,0)-isnull(ÆÚ³õ¼õÉÙ,0),±¾ÆÚÔö¼Ó,±¾ÆÚ¼õÉÙ,
±¾ÆÚ½áÓà=(isnull(ÆÚ³õÔö¼Ó,0)-isnull(ÆÚ³õ¼õÉÙ,0)+isnull(±¾ÆÚÔö¼Ó,0)-isnull(±¾ÆÚ¼õÉÙ,0)) from (
select ÐÕÃû,סַ,
ÆÚ³õÔö¼Ó=(select ÆÚ³õÔö¼Ó=sum(Ôö¼Ó»ý·Ö) from b where ·¢ÉúÈÕÆÚ<'2006-5-1' and ¿¨ºÅ=a.¿¨ºÅ),
ÆÚ³õ¼õÉ ......

¶ÁÈ¡SQL Server ±í½á¹¹

ÒýÓÃ×Þ½¨µÄ£º   
Cod
  SELECT    
  ±íÃû=case   when   a.colorder=1   then   d.name   else   ''   end,    
  ×Ö¶ÎÐòºÅ=a.colorder,     ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ