MS SQL¿¡¼ Áߺ¹µÈ µ¥ÀÌÅ͸¦ Çϳª·Î ¹´Â ¹æ¹ý Áú¹®µå¸³´Ï´Ù.
´Ù½Ã Áú¹®À» µå¸±²²¿ä. ¼ºÀÇ ÀÖ°Ô ´äº¯ÇØÁֽźв² ¹Ù·Î äÅà Çϴϱñ Ãß°¡ ´äº¯ÀÌ ºÒ°¡´ÉÇÏ°Ô µÇ¹ö¸®³×¿ä.
SQL Ãʺ¸¶ó¼ ¾ÆÁ÷ Àß ¸ð¸£´Â°Ô ³Ê¹« ¸¹Àºµ¥¿ä. ¾Æ·¡¿Í °°Àº Å×À̺íÀÌ ÀÖ´Ù°í °¡Á¤ÇÏ°Ú½À´Ï´Ù.
¹®Ç× ´äº¯ÀÚ ´äº¯³»¿ë
------------------------------------------
1 È«±æµ¿ 1¹ø
1 °©µ¹ÀÌ 2¹ø
1 È«±æµ¿ 3¹ø
2 È«±æµ¿ 4¹ø
.
.
.
º¸½Ã´Â ¹Ù¿Í °°ÀÌ ¾î¶² ¹®Ç׿¡ ´ëÇÑ °á°ú ´äº¯À» ³Ö´Â Å×À̺íÀ̸ç, 1¹ø¹®Ç×Àº º¹¼ö ´ë´äÀÌ °¡´ÉÇÑ
(È«±æµ¿ÀÌ 1,3¹øÀ¸·Î µÎ¹ø ´ë´äÇÑ) ¹®Ç×ÀÔ´Ï´Ù.
Àú Å×À̺íµéÀ» ¾Æ·¡¿Í °°Àº Çü½ÄÀ¸·Î º¸¿©ÁÙ·Á°í ÇÕ´Ï´Ù.
1 2 3 4 5 6
------------------------------------------------------------------------
È«±æµ¿ 1,3 4 1,2,3
°©µ¹ÀÌ 2 ... ...
ÀÏ´Ü º¹¼ö ÀÀ´ä¿¡ Á¦ÇÑÀº ¾ø°í¿ä. À̸¦ À§Çؼ µÎ°¡Áö°¡ ÇÊ¿äÇÒ °Í °°Àºµ¥
1. ÇöÀç Å×ÀÌºí¿¡ Áߺ¹ ¹®Ç×À» ÇϳªÀÇ µ¥ÀÌÅÍ·Î Á¾ÇÕ
ex)
¹®Ç× ´äº¯ÀÚ ´äº¯³»¿ë
------------------------------------------
1 È«±æµ¿ 1,3
1 °©µ¹ÀÌ 2
2 È«±æµ¿ 4
3 È«±æµ¿ 1,2,3
2. pivotÀ» ÀÌ¿ëÇÑ µ¥ÀÌÅÍ Á¤·Ä
ÀÌ·¸°Ô »ý°¢À» ÇÏ°í Àִµ¥¿ä. ÇǺ¿À» ÀÌ¿ëÇؼ col,row¸¦ ³ª´©´Â °ÍÀº ¾Ë°Ú´Âµ¥
1¹øó·³ Áߺ¹´äº¯¿¡ ´ëÇÑ µ¥ÀÌÅ͸¦ ÇÕÄ¡´Â °ÍÀ» ¸ð¸£°Ú³×¿ä. Àç±Í·Î ÇØ¾ß ÇÒ°Í °°±âµµ Çѵ¥....
°í¼ö´Ôµé µµ¿ò ºÎŹµå¸³´Ï´Ù.
(¸¸¾à Á¦°¡ »ý°¢ÇÑ ¼ø¼°¡ Ʋ¸®´Ù¸é Á¶¾ð ºÎŹµå¸±²²¿ä. ..select,insert,delete¸¸ ¾²´Ùº¸´Ï Á¶±Ý¸¸ ±í°Ôµé¾î°¡¸é
SQL ³Ê¹« ¾î·Æ³×¿ä)
°µÀûÀ¸·Î Àúµµ ³Ê¹« ¸¹Àº °í¹ÎÀ» Çؼ...¸íÄèÇÑ ´äº¯À» ¾ò¾îº¸°í ½Í³×¿ä.
ÀÏ´ÜÀº ºñÈ¿À²ÀûÀÎ °ÍÀº ¾ËÁö¸¸ DB´Ü¿¡¼ 1¹ø ºÎºÐÀ» ÇØ°áÇÒ ¼ö ÀÖ´Â Á¶¾ðÀ» ºÎŹµå¸±²²¿ä.
(¿¹Àü¿£ Ä¿¼·Î µÇ¾î ÀÖ´Â ºÎºÐÀ» °³¼±Çϱâ À§ÇÑ °Å¶ó Ä¿¼¸¸Å ºñÈ¿À²ÀûÀÌÁø ¾ÊÀ¸¸®¶õ »ý°¢ÀÌ µå³×¿ä.)
re: MS SQL¿¡¼ Áߺ¹µÈ µ¥ÀÌÅ͸¦ Çϳª·Î ¹´Â ¹æ¹ý Áú¹®µå¸³´Ï´Ù.
ÀÌ°ÍÀº Ä¿¼¹Û¿¡ ÇÒ¼ö ¾øÀ» °ÍÀ̶ó°í »ý°¢µË´Ï´Ù....
±×·¡¼ Á¦°¡ ±×³É ÇÁ·Î±×·¥´Ü¿¡¼ ó¸®Ç϶ó°í Çß´ø °ÍÀÌ°í¿ä..
Ȥ½Ã ¸ð¸£´Ï 1¹øºÎºÐÀ» Ä¿¼·Î ¸¸µç Äõ¸®¹®À» ¿Ã·Áµå¸³´Ï´Ù. Âü°íÇϼ¼¿ä
--------------------------------------------------------------------------------------------
/*
Å×½ºÆ® Å×ÀÌºí »ý¼º ¹× µ¥ÀÌÅÍ ÀÔ·Â
create table TEST(¹®Ç×int, ´äº¯ÀÚvarchar(10), ´äº¯³»¿ëint)
insert into test values (1,'È«±æµ¿',1)
insert into test values (1,'°©µ¹ÀÌ',2)
insert into test values (1,'È«±æµ¿',3)
insert into test values (2,'È«±æµ¿',4)
insert into test values (3,'È«±æµ¿',1)
insert into test values (3,'È«±æµ¿',2)
insert into test values (3,'È«±æµ¿',3)
*/
----------------------------------------------------------------
-- Ä¿¼¸¦ »ç¿ë
----------------------------------------------------------------
-- °á°ú¸¦ÀÔ·ÂÇÒÀÓ½ÃÅ×À̺í
create table #temp(¹®Ç× int, ´äº¯ÀÚ varchar(10), ´äº¯³»¿ë VARCHAR(100))
-- Ä¿¼
DECLARE @¹®Ç× int, @´äº¯ÀÚ varchar(10), @´äº¯³»¿ë int
DECLARE @CNT INT, @º¹Çմ亯 VARCHAR(100)
DECLARE MYCUR CURSOR FOR
SELECT ¹®Ç×,´äº¯ÀÚ,´äº¯³»¿ë
FROM TEST
ORDER BY ¹®Ç×,´äº¯ÀÚ,´äº¯³»¿ë
OPEN MYCUR
FETCH NEXT FROM MYCUR INTO @¹®Ç×, @´äº¯ÀÚ, @´äº¯³»¿ë
WHILE (@@FETCH_STATUS=0)
BEGIN
SET @CNT= ISNULL((SELECT COUNT(*) FROM #temp WHERE ¹®Ç×=@¹®Ç× AND ´äº¯ÀÚ=@´äº¯ÀÚ),0)
IF @CNT=0
BEGIN
INSERT INTO #temp(¹®Ç×,´äº¯ÀÚ,´äº¯³»¿ë) values (@¹®Ç×, @´äº¯ÀÚ, convert(varchar,@´äº¯³»¿ë))
END
ELSE
BEGIN
UPDATE #temp
SET ´äº¯³»¿ë=´äº¯³»¿ë+','+convert(varchar,@´äº¯³»¿ë)
WHERE ¹®Ç×=@¹®Ç× AND ´äº¯ÀÚ=@´äº¯ÀÚ
END
FETCH NEXT FROM MYCUR INTO @¹®Ç×, @´äº¯ÀÚ, @´äº¯³»¿ë
END
CLOSE MYCUR
DEALLOCATE MYCUR
--°á°ú°¡ÀԷµÈÀÓ½ÃÅ×À̺íÁ¶È¸
SELECT * FROM #TEMP ORDER BY ´äº¯ÀÚ,¹®Ç×
°á°ú
¹®Ç×
´äº¯ÀÚ
´äº¯³»¿ë
-----------
---------- -----------
1
°©µ¹ÀÌ 2
1 È«±æµ¿ 1,3
2 È«±æµ¿
4
3 È«±æµ¿ 1,2,3