Çà°ú ¿ÀÌ Â¥¿©Áø Åë°èÇ¥¶ó¸é Äõ¸® ÇѹøÀ¸·Î °¡´ÉÇѵ¥, Ä÷³º°·Î record ¼ö ÀÚü°¡ ´Ù¸£¹Ç·Î Äõ¸®¸¸À¸·Î´Â Èûµé°Í °°Àºµ¥¿ä? À§¿¡ ¸»¾¸ÇϽŠpivot °°Àº ¹æ¹ýµéµµ °á±¹¿£ Çà°ú ¿ÀÌ °íÁ¤ÀûÀÏ °æ¿ì¿¡¸¸ °¡´ÉÇÑ°Ì´Ï´Ù. (»ç½Ç Àú´Â ƯÁ¤ DBMS Á¾¼ÓÀûÀ롃 º°·Î ¾ÈÁÁ¾ÆÇؼ, pivotÀº º°·Î ½áº¸Áú ¾Ê¾ÒÁö¸¸, ±×·²°Ì´Ï´Ù^^;) °ü°èÇüDB´Â °á±¹ SELECTÇÑ Ä÷³¿¡ ´ëÇÑ ·¹Äڵ尡 Ãâ·ÂµÇ´Â °ÍÀ̱⠶§¹®¿¡, Ä÷³º°·Î ·¹ÄÚµå¼ö°¡ ´Ù¸£´Ù´Â°Ç ÀÖÀ» ¼ö ¾øÁÒ. ÇÏÁö¸¸ ÇÁ·Î±×·¥ÀÇ µµ¿òÀ» ¹Þ°í Äõ¸® 2¹ø Á¤µµ¸é °¡´ÉÇÏ°Ú½À´Ï´Ù ÀÌ·± ½ÄÀ¸·Î¿ä¡¦
ÀÏ´Ü Çì´õ·Î »ç¿ëµÉ ¸ñ·ÏÀ» ±¸ÇÕ´Ï´Ù. ¿©±â¼± ÆÄÆ®¸íÀÌ°ÚÁÒ
SELECT ÆÄÆ®¸í FROM Á¶Á÷Á¤º¸;
±¸ÇØÁø Çì´õ¸¦ ÇÁ·Î±×·¥À» LOOP µ¹¸ç µ¿ÀûÀ¸·Î Äõ¸®¸¦ ¸¸µì´Ï´Ù. [] ¾ÈÀº ÇÁ·Î±×·¥ ÄÚµåÀÔ´Ï´Ù.
SELECT [for i=0 to ÆÄÆ®¸í ¼ö] [if i>0 then print ,] P[print i].¸â¹ö¸í [end for] FROM ( /* ¼·Î ´Ù¸¥ ·¹ÄÚµå¼ö¸¦ Çϳª·Î JOIN ½ÃÅ°±â À§ÇØ ·¹ÄÚµå¼ö°¡ Á¦ÀÏ ¸¹Àº ÆÄÆ®¸¦ ±âÁØÀ¸·Î ¸¶½ºÅͼº ·¹Äڵ带 ¸¸µé¾îÁÝ´Ï´Ù */ SELECT ROWNUM as rn FROM ¸â¹öÁ¤º¸ WHERE ÆÄÆ®¸í = ( SELECT ÆÄÆ®¸í FROM Á¶Á÷Á¤º¸ GROUP BY ÆÄÆ®¸í HAVING COUNT(*) = ( SELECT MAX(COUNT(*)) FROM Á¶Á÷Á¤º¸ GROUP BY ÆÄÆ®¸í ) ) mas /* ¿©±â¼ºÎÅÍ ÆÄÆ®º° ¸â¹ö¸¦ JOINÇØ ³ª°©´Ï´Ù */ [for i=0 to ÆÄÆ®¸í ¼ö] LEFT JOIN ( SELECT ROWNUM as rn , ¸â¹ö¸í FROM ¸â¹öÁ¤º¸ WHERE ÆÄÆ®¸í = [i¹ø° ÆÄÆ®¸í] ) P[print i] ON mas.rn = P[print i].rn [end for]
ÀÌ·¸°Ô ÇÏ¸é µÇ°Ú³×¿ä¡¦ ¾ð¾î°¡ ¹ºÁö ¸ô¶ó¼ ÀÏ´Ü ±×³É [~] ·Î ÇÁ·Î±×·¥ µé¾î°¥ ºÎºÐ ¼³¸íÇѰű¸¿ä¡¦ °á°úÀûÀ¸·Î ÆÄÆ®°¡ 2°³¸é ÀÌ·±½ÄÀ¸·Î Äõ¸®°¡ »ý¼ºµÇ¾î¾ßÁÒ¡¦ (Çì´õ´Â ÃÖ»ó´Ü¿¡ Äõ¸®·Î ºÙ¿©¼ UNION Çìµµ µÇ°í, µû·Î ÇÁ·Î±×·¥À¸·Î »Ñ·Áµµ µÇ°Ú½À´Ï´Ù)
SELECT P1.¸â¹ö¸í , P2.¸â¹ö¸í FROM ( /* ¼·Î ´Ù¸¥ ·¹ÄÚµå¼ö¸¦ Çϳª·Î JOIN ½ÃÅ°±â À§ÇØ ·¹ÄÚµå¼ö°¡ Á¦ÀÏ ¸¹Àº ÆÄÆ®¸¦ ±âÁØÀ¸·Î ¸¶½ºÅͼº ·¹Äڵ带 ¸¸µé¾îÁÝ´Ï´Ù */ SELECT ROWNUM as rn FROM ¸â¹öÁ¤º¸ WHERE ÆÄÆ®¸í = ( SELECT ÆÄÆ®¸í FROM Á¶Á÷Á¤º¸ GROUP BY ÆÄÆ®¸í HAVING COUNT(*) = ( SELECT MAX(COUNT(*)) FROM Á¶Á÷Á¤º¸ GROUP BY ÆÄÆ®¸í ) ) mas /* ¿©±â¼ºÎÅÍ ÆÄÆ®º° ¸â¹ö¸¦ JOINÇØ ³ª°©´Ï´Ù */ LEFT JOIN ( SELECT ROWNUM as rn , ¸â¹ö¸í FROM ¸â¹öÁ¤º¸ WHERE ÆÄÆ®¸í = 'ÆÄÆ®¸í1' ) P1 ON mas.rn = P1.rn LEFT JOIN ( SELECT ROWNUM as rn , ¸â¹ö¸í FROM ¸â¹öÁ¤º¸ WHERE ÆÄÆ®¸í = 'ÆÄÆ®¸í2' ) P2 ON mas.rn = P2.rn