Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö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 Êý¾Ý¿âÏà¹Ø²Ù×÷

1£º
Sql server 2005ÈÕÖ¾ÎļþÌ«´ó,ʹÆä¼õСµÄ·½·¨
ÔËÐÐÏÂÃæµÄÈýÐÐ PMDataCenter  ΪÊý¾Ý¿âÃû:
backup log PMDataCenter with NO_LOG
backup log PMDataCenter with TRUNCATE_ONLY
DBCC SHRINKDATABASE(PMDataCenter) ......

SQL Union ÓëUnion AllÏê½â

1.Union
UNION Ö¸ÁîµÄÄ¿µÄÊǽ«Á½¸ö SQL Óï¾äµÄ½á¹ûºÏ²¢ÆðÀ´¡£´ÓÕâ¸ö½Ç¶ÈÀ´¿´£¬ UNION ¸ú JOIN ÓÐЩÐíÀàËÆ£¬ÒòΪÕâÁ½¸öÖ¸Áî¶¼¿ÉÒÔÓɶà¸ö±í¸ñÖÐߢȡ×ÊÁÏ¡£ UNION µÄÒ»¸öÏÞÖÆÊÇÁ½¸ö SQL Óï¾äËù²úÉúµÄÀ¸Î»ÐèÒªÊÇͬÑùµÄ×ÊÁÏÖÖÀà¡£ÁíÍ⣬µ±ÎÒÃÇÓà UNIONÕâ¸öÖ¸Áîʱ£¬ÎÒÃÇÖ»»á¿´µ½²»Í¬µÄ×ÊÁÏÖµ (ÀàËÆ SELECT DISTINCT)¡£
UNION µ ......

powerDesignerÉú³ÉsqlºÍ·´ÏòÉú³ÉERͼµÄÎÊÌâ

Ò»¡¢PowerDesignerÉú³ÉsqlÎÊÌâ
Éú³ÉsqlµÄ·½·¨ÊÇ Database -->Generate Database (Ctrl + G ) µ«ÊÇÌáʾ
Generation aborted due to errors detected during the verification of the model.
½â¾ö·½·¨: ½«check model È¥µô¾Í¿ÉÒÔÁË.ÆäÖУ¬one file onÊÇ·ñÐèÒª°´ÕÕ±íÉú³ÉÐí¶à¸ösql£¨Ä¬ÈÏÑ¡ÉÏ£¬¼´²»ÐèÒª£©
  & ......

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

ÒýÓÃ×Þ½¨µÄ£º   
Cod
  SELECT    
  ±íÃû=case   when   a.colorder=1   then   d.name   else   ''   end,    
  ×Ö¶ÎÐòºÅ=a.colorder,     ......

ʹÓù«Óñí±í´ïʽ£¨CTE£©¼ò»¯Ç¶Ì×SQL ºÍ½øÐеݹéµ÷ÓÃ

1.ʹÓÃCTE¼ò»¯Ç¶Ì×sql
   ÏÈ¿´ÏÂÃæÒ»¸öǶÌ׵IJéѯÓï¾ä£º
select * from person.StateProvince where CountryRegionCode in 
         (select CountryRegionCode from person.CountryRegion where  ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ