.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
½ñÌì½Óµ½¿Í»§µç»°£¬Ëµ²Ù×÷Êý¾ÝÎÞ·¨±£´æ¡£¾¹ý·ÖÎö£¬·¢ÏÖËûµÄÊý¾Ý¿âÒѾÓÐ5G¶àµÄ´óС£¬¶ø×îÖÕ·¢ÏÖÓÐÕűíµÄË÷Òý³ö´íÁË£¬ÓÃDBCC CHECKÒ²ÎÞÁ¦»ØÌì¡£
ÿ´ÎÓÃselect * from ln003082 Óï¾ä²éѯ£¬¶¼±¨ÈçÏ´íÎó£º
·þÎñÆ÷: ÏûÏ¢ 605£¬¼¶±ð 21£¬×´Ì¬ 1£¬ÐÐ 1
ÊÔͼ´ÓÊý¾Ý¿â 'ln_fl0125' ÖÐÌáÈ¡µÄÂß¼ ......