SQL सर्वर PIVOT और UNPIVOT का एक साथ उपयोग कर रहा है

UNPIVOT की शक्ति का संयोजन, और फिर PIVOT को 12 कॉलम टेबल को 36 टेबल कॉलम या अधिक में बदलने के लिए

उद्देश्य

इन कार्यों को एक साथ एक बहुत शक्तिशाली प्रक्रिया में जोड़ा जा सकता है।

हम UNPIVOT लेख से डेटा सेट लेने जा रहे हैं, जिसमें 12 महीने का डेटा एक पंक्ति के आधार पर संग्रहीत है, हम UNPIVOT पर जा रहे हैं और इन कॉलमों को तिथियों में परिवर्तित करते हैं और अंत में इसे 36 कॉलम चौड़ी तालिका में PIVOT करते हैं।

मैंने इनमें से प्रत्येक पर अलग-अलग छोटे लेख लिखे हैं, इसलिए सुनिश्चित करें कि आपने उन्हें पढ़ लिया है, और फिर मैं कोड में कूद जाऊंगा।

UNPIVOT का उपयोग करना

UNPIVOT का उपयोग करने के लिए त्वरित मार्गदर्शिका।

अधिक: स्तंभों को पंक्तियों में बदलने के लिए UNPIVOT का उपयोग कर SQL सर्वर

PIVOT . का उपयोग करना

PIVOT का उपयोग करने के लिए त्वरित मार्गदर्शिका।

अधिक: PIVOT उदाहरण का उपयोग कर SQL सर्वर

SQL

CREATE TYPE c1bs_UnPivot AS TABLE(UnPivID INT,UnPivMonth DATE,UnPivData INT)GOCREATE FUNCTION c1bs_DateFromParts(@Year SMALLINT,@Month SMALLINT,@Day SMALLINT) RETURNS DATE AS BEGINDECLARE @Date DATE=CONVERT(NVARCHAR(4),@Year)+'-'+CONVERT(NVARCHAR(2),@Month)+'-'+CONVERT(NVARCHAR(2),@Day)RETURN @DateENDGODECLARE @UnPiv TABLE(UnPivID INT,UnPivYear SMALLINT,M1 INT,M2 INT,M3 INT,M4 INT,M5 INT,M6 INT,M7 INT,M8 INT,M9 INT,M10 INT,M11 INT,M12 INT)INSERT INTO @UnPiv(UnPivID,UnPivYear,M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12) SELECT 1,2010,1,2,3,4,5,6,7,8,9,10,11,12INSERT INTO @UnPiv(UnPivID,UnPivYear,M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12) SELECT 1,2011,1,2,3,4,5,6,7,8,9,10,11,12INSERT INTO @UnPiv(UnPivID,UnPivYear,M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12) SELECT 1,2012,1,2,3,4,5,6,7,8,9,10,11,12INSERT INTO @UnPiv(UnPivID,UnPivYear,M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12) SELECT 2,2010,1,2,3,4,5,6,7,8,9,10,11,12
DECLARE @Piv AS c1bs_UnPivotINSERT INTO @PivSELECT UnPivID,dbo.c1bs_DateFromParts(UnPivYear,REPLACE(col,'M',''),1) col,valFROM @UnPivUNPIVOT (Val FOR col IN (M1,M2,M3,M4,M5,M6,M7,M8,M9,M10,M11,M12)) unpiv

PIVOT . का उपयोग करना

पिछले कथन में हमने उसी प्रक्रिया का पालन किया है जैसे कि UNPIVOT लेख में, एक डमी डेटा सेट को सम्मिलित करने के लिए एक अस्थायी तालिका घोषित की, और फिर एक अस्थायी तालिका में डेटा को UNPIVOTED किया।

SQL

--Complex Pivot - unknown column names--Get Column Names for belowDECLARE @Cols NVARCHAR(MAX),@Sel NVARCHAR(MAX) SELECT @Cols=COALESCE(@Cols+',','')+'['+CONVERT(NVARCHAR(20),UnPivMonth)+']',--Concatenate the Columns@Sel =COALESCE(@Sel +',','')+'ISNULL(['+CONVERT(NVARCHAR(20),UnPivMonth)+'], 0) AS ['+CONVERT(NVARCHAR(20),UnPivMonth)+']' --Concatenate the Columns into a select listFROM @Piv GROUP BY UnPivMonth ORDER BY UnPivMonth
--Columns list displaySELECT @Cols ColumnList,@Sel SelectList
--Complex Pivot SQLDECLARE @Params NVARCHAR(MAX)='@Piv c1bs_UnPivot READONLY'DECLARE @SQL NVARCHAR(MAX)='SELECT [UnPivID],'+@Sel+'FROM @PivPIVOT (SUM(UnPivData)FOR UnPivMonthIN ('+@Cols+')) AS MyTable'
--Execute SQLEXECUTE sp_executesql @SQL,--SQL String from above@Params,--Parameter list@Piv--Temp Table needs to be passed in, can only be read only

PIVOT . का उपयोग करना

इस कथन में हमने उसी प्रक्रिया का अनुसरण किया है जैसा कि जटिल PIVOT उदाहरण में किया गया है। हमारी चुनिंदा सूची और कॉलम नामों को रखने के लिए घोषित चर, और डेटा सेट को वापस करने के लिए उन्हें PIVOT स्टेटमेंट में इंजेक्ट किया।

यह 36 तारीखों के साथ एक तालिका लौटाएगा जो हमने शुरुआत में डाली थी, नीचे दिए गए उदाहरण में केवल हम केवल पहले 6 कॉलम दिखाते हैं। हालांकि कई तिथियां प्रदान की गई थीं, तालिका बढ़ेगी।

UnPivID	01/01/2010	01/02/2010	01/03/2010	01/04/2010	01/05/2010	01/06/2010
1	1	2	3	4	5	6
2	1	2	3	4	5	6