दिलचस्प पोस्ट
जब तक आवश्यकता नहीं होती तब तक 3 लाइब्रेरी लोड नहीं हो जाती एंड्रॉइड में विलंब के बाद एक विधि कैसे कॉल करें मैं php के साथ एक फ़ाइल का माइम-प्रकार कैसे प्राप्त करूं? एंड्रॉइड में सॉफ्टवेयर कीबोर्ड की दृश्यता कैसे जांचनी है? SQL में EXISTS और IN के बीच का अंतर? यूआरएल में बैक बटन / हैश बदलें का पता लगा रहा है नेटबीन्स में जार फ़ाइल कैसे बनाएं वेबकिट क्या है और यह सीएसएस से कैसे संबंधित है? RichTextBox में चुनिंदा रंगीन टेक्स्ट अंतराल के बीच डेटासेट की एक सूची जेनरेट करें मैं विजुअल स्टूडियो 2008 में एक नया CUDA प्रोजेक्ट कैसे शुरू करूं? एंड्रॉइड 4.0: विगेट्स दिखाई नहीं दे रहे हैं? Django ORM में select_related और prefetch_related में क्या अंतर है? मैं अनुरोध पुस्तकालय से लॉग संदेशों को कैसे अक्षम करूं? मैं Node.js के साथ कैसे आरंभ करूं

एसक्यूएल सर्वर में कॉलम में कुशलता से कन्वर्ट करें

मैं SQL सर्वर में कॉलम के लिए पंक्तियों को कनवर्ट करने के लिए एक कुशल तरीके की तलाश कर रहा हूं, मैंने सुना है कि PIVOT बहुत तेज नहीं है, और मुझे कई रिकॉर्डों से निपटना होगा।

यह मेरा उदाहरण है:

---------------------------- | Id | Value |ColumnName | ---------------------------- | 1 |John |FirstName | | 2 |2.4 | Amount | | 3 |ZH1E4A |PostalCode | | 4 |Fork |LastName | | 5 |857685 |AccountNumber| ---------------------------- 

यह मेरा परिणाम है:

 -------------------------------------------------------------------- |FirstName |Amount| PostalCode | LastName | AccountNumber| -------------------------------------------------------------------- |John | 2.4 | ZH1E4A | Fork | 857685 | -------------------------------------------------------------------- 

मैं परिणाम कैसे बना सकता हूं?

वेब के समाधान से एकत्रित समाधान "एसक्यूएल सर्वर में कॉलम में कुशलता से कन्वर्ट करें"

कई तरीके हैं कि आप डेटा को कई पंक्तियों से कॉलम में बदल सकते हैं। SQL सर्वर में आप पंक्तियों से डेटा कॉलम में बदलने के लिए PIVOT फ़ंक्शन का उपयोग कर सकते हैं:

 select Firstname, Amount, PostalCode, LastName, AccountNumber from ( select value, columnname from yourtable ) d pivot ( max(value) for columnname in (Firstname, Amount, PostalCode, LastName, AccountNumber) ) piv; 

डेमो देखें

अगर आपके पास अज्ञात संख्या में columnnames जो आप स्थानांतरित करना चाहते हैं, तो आप गतिशील SQL का उपयोग कर सकते हैं:

 DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName) from yourtable group by ColumnName, id order by id FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = N'SELECT ' + @cols + N' from ( select value, ColumnName from yourtable ) x pivot ( max(value) for ColumnName in (' + @cols + N') ) p ' exec sp_executesql @query; 

डेमो देखें

यदि आप PIVOT फ़ंक्शन का उपयोग नहीं करना चाहते हैं, तो आप एक सामान्य कार्य के साथ एक CASE अभिव्यक्ति का उपयोग कर सकते हैं:

 select max(case when columnname = 'FirstName' then value end) Firstname, max(case when columnname = 'Amount' then value end) Amount, max(case when columnname = 'PostalCode' then value end) PostalCode, max(case when columnname = 'LastName' then value end) LastName, max(case when columnname = 'AccountNumber' then value end) AccountNumber from yourtable 

डेमो देखें

यह एकाधिक जुड़ने का उपयोग करके भी पूरा किया जा सकता है, लेकिन आपको प्रत्येक कॉलम की आवश्यकता होगी जो आपके नमूने डेटा में नहीं है। लेकिन मूल वाक्य रचना होगी:

 select fn.value as FirstName, a.value as Amount, pc.value as PostalCode, ln.value as LastName, an.value as AccountNumber from yourtable fn left join yourtable a on fn.somecol = a.somecol and a.columnname = 'Amount' left join yourtable pc on fn.somecol = pc.somecol and pc.columnname = 'PostalCode' left join yourtable ln on fn.somecol = ln.somecol and ln.columnname = 'LastName' left join yourtable an on fn.somecol = an.somecol and an.columnname = 'AccountNumber' where fn.columnname = 'Firstname' 

जैसे कि पिवट डेटा अभी भी एक गर्म है, मैंने कुछ भी जोड़ने का फैसला किया, यह सिर्फ एक स्क्रिप्ट की बजाय एक विधि है, लेकिन आपको अधिक संभावनाएं प्रदान करता है। सबसे पहले, आप को तैनात करने की जरूरत 3 स्क्रिप्ट हैं: 1) यूजर परिभाषित टेबल प्रकार [ ColumnActionList ] -> पैरामीटर के रूप में डेटा रखता है) सपा [ proc_PivotPrepare ] -> हमारे डेटा तैयार करता है 3) सपा [ proc_PivotExecute ] -> स्क्रिप्ट निष्पादित करें

  CREATE TYPE [dbo].[ColumnActionList] AS TABLE( [ID] [smallint] NOT NULL, [ColumnName] [nvarchar](128) NOT NULL, [Action] [nchar](1) NOT NULL ); GO CREATE PROCEDURE [dbo].[proc_PivotPrepare] ( @DB_Name nvarchar(128), @TableName nvarchar(128) ) AS ---------------------------------------------------------------------------------------------------- -----| Author: Bartosz ---------------------------------------------------------------------------------------------------- SELECT @DB_Name = ISNULL(@DB_Name,db_name()) DECLARE @SQL_Code nvarchar(max) DECLARE @MyTab TABLE (ID smallint identity(1,1), [Column_Name] nvarchar(128), [Type] nchar(1), [Set Action SQL] nvarchar(max)); SELECT @SQL_Code = 'SELECT [<| SQL_Code |>] = '' '' ' + 'UNION ALL ' + 'SELECT ''----------------------------------------------------------------------------------------------------'' ' + 'UNION ALL ' + 'SELECT ''-----| Declare user defined type [ID] / [ColumnName] / [PivotAction] '' ' + 'UNION ALL ' + 'SELECT ''----------------------------------------------------------------------------------------------------'' ' + 'UNION ALL ' + 'SELECT ''DECLARE @ColumnListWithActions ColumnActionList;''' + 'UNION ALL ' + 'SELECT ''----------------------------------------------------------------------------------------------------'' ' + 'UNION ALL ' + 'SELECT ''-----| Set [PivotAction] (''''S'''' as default) to select dimentions and values '' ' + 'UNION ALL ' + 'SELECT ''-----|''' + 'UNION ALL ' + 'SELECT ''-----| ''''S'''' = Stable column || ''''D'''' = Dimention column || ''''V'''' = Value column '' ' + 'UNION ALL ' + 'SELECT ''----------------------------------------------------------------------------------------------------'' ' + 'UNION ALL ' + 'SELECT ''INSERT INTO @ColumnListWithActions VALUES ('' + CAST( ROW_NUMBER() OVER (ORDER BY [NAME]) as nvarchar(10)) + '', '' + '''''''' + [NAME] + ''''''''+ '', ''''S'''');''' + 'FROM [' + @DB_Name + '].sys.columns ' + 'WHERE object_id = object_id(''[' + @DB_Name + ']..[' + @TableName + ']'') ' + 'UNION ALL ' + 'SELECT ''----------------------------------------------------------------------------------------------------'' ' + 'UNION ALL ' + 'SELECT ''-----| Execute sp_PivotIT with parameters: columns and dimentions and main table name'' ' + 'UNION ALL ' + 'SELECT ''----------------------------------------------------------------------------------------------------'' ' + 'UNION ALL ' + 'SELECT ''EXEC [dbo].[sp_PivotIt] @ColumnListWithActions, ' + '''''' + @TableName + '''''' + ';''' + 'UNION ALL ' + 'SELECT ''----------------------------------------------------------------------------------------------------'' ' EXECUTE SP_EXECUTESQL @SQL_Code; GO CREATE PROCEDURE [dbo].[proc_PivotExecute] ( @ColumnListWithActions ColumnActionList ReadOnly ,@TableName nvarchar(128) ) AS --####################################################################################################################### --###| Author: Bartosz --####################################################################################################################### --####################################################################################################################### --###| Step 1 - Select our user-defined-table-variable into temp table --####################################################################################################################### IF OBJECT_ID('tempdb.dbo.#ColumnListWithActions', 'U') IS NOT NULL DROP TABLE #ColumnListWithActions; SELECT * INTO #ColumnListWithActions FROM @ColumnListWithActions; --####################################################################################################################### --###| Step 2 - Preparing lists of column groups as strings: --####################################################################################################################### DECLARE @ColumnName nvarchar(128) DECLARE @Destiny nchar(1) DECLARE @ListOfColumns_Stable nvarchar(max) DECLARE @ListOfColumns_Dimension nvarchar(max) DECLARE @ListOfColumns_Variable nvarchar(max) --############################ --###| Cursor for List of Stable Columns --############################ DECLARE ColumnListStringCreator_S CURSOR FOR SELECT [ColumnName] FROM #ColumnListWithActions WHERE [Action] = 'S' OPEN ColumnListStringCreator_S; FETCH NEXT FROM ColumnListStringCreator_S INTO @ColumnName WHILE @@FETCH_STATUS = 0 BEGIN SELECT @ListOfColumns_Stable = ISNULL(@ListOfColumns_Stable, '') + ' [' + @ColumnName + '] ,'; FETCH NEXT FROM ColumnListStringCreator_S INTO @ColumnName END CLOSE ColumnListStringCreator_S; DEALLOCATE ColumnListStringCreator_S; --############################ --###| Cursor for List of Dimension Columns --############################ DECLARE ColumnListStringCreator_D CURSOR FOR SELECT [ColumnName] FROM #ColumnListWithActions WHERE [Action] = 'D' OPEN ColumnListStringCreator_D; FETCH NEXT FROM ColumnListStringCreator_D INTO @ColumnName WHILE @@FETCH_STATUS = 0 BEGIN SELECT @ListOfColumns_Dimension = ISNULL(@ListOfColumns_Dimension, '') + ' [' + @ColumnName + '] ,'; FETCH NEXT FROM ColumnListStringCreator_D INTO @ColumnName END CLOSE ColumnListStringCreator_D; DEALLOCATE ColumnListStringCreator_D; --############################ --###| Cursor for List of Variable Columns --############################ DECLARE ColumnListStringCreator_V CURSOR FOR SELECT [ColumnName] FROM #ColumnListWithActions WHERE [Action] = 'V' OPEN ColumnListStringCreator_V; FETCH NEXT FROM ColumnListStringCreator_V INTO @ColumnName WHILE @@FETCH_STATUS = 0 BEGIN SELECT @ListOfColumns_Variable = ISNULL(@ListOfColumns_Variable, '') + ' [' + @ColumnName + '] ,'; FETCH NEXT FROM ColumnListStringCreator_V INTO @ColumnName END CLOSE ColumnListStringCreator_V; DEALLOCATE ColumnListStringCreator_V; SELECT @ListOfColumns_Variable = LEFT(@ListOfColumns_Variable, LEN(@ListOfColumns_Variable) - 1); SELECT @ListOfColumns_Dimension = LEFT(@ListOfColumns_Dimension, LEN(@ListOfColumns_Dimension) - 1); SELECT @ListOfColumns_Stable = LEFT(@ListOfColumns_Stable, LEN(@ListOfColumns_Stable) - 1); --####################################################################################################################### --###| Step 3 - Preparing table with all possible connections between Dimension columns excluding NULLs --####################################################################################################################### DECLARE @DIM_TAB TABLE ([DIM_ID] smallint, [ColumnName] nvarchar(128)) INSERT INTO @DIM_TAB SELECT [DIM_ID] = ROW_NUMBER() OVER(ORDER BY [ColumnName]), [ColumnName] FROM #ColumnListWithActions WHERE [Action] = 'D'; DECLARE @DIM_ID smallint; SELECT @DIM_ID = 1; DECLARE @SQL_Dimentions nvarchar(max); IF OBJECT_ID('tempdb.dbo.##ALL_Dimentions', 'U') IS NOT NULL DROP TABLE ##ALL_Dimentions; SELECT @SQL_Dimentions = 'SELECT ID = ROW_NUMBER() OVER (ORDER BY ' + @ListOfColumns_Dimension + '), ' + @ListOfColumns_Dimension + ' INTO ##ALL_Dimentions ' + ' FROM (SELECT DISTINCT' + @ListOfColumns_Dimension + ' FROM ' + @TableName + ' WHERE ' + (SELECT [ColumnName] FROM @DIM_TAB WHERE [DIM_ID] = @DIM_ID) + ' IS NOT NULL '; SELECT @DIM_ID = @DIM_ID + 1; WHILE @DIM_ID <= (SELECT MAX([DIM_ID]) FROM @DIM_TAB) BEGIN SELECT @SQL_Dimentions = @SQL_Dimentions + 'AND ' + (SELECT [ColumnName] FROM @DIM_TAB WHERE [DIM_ID] = @DIM_ID) + ' IS NOT NULL '; SELECT @DIM_ID = @DIM_ID + 1; END SELECT @SQL_Dimentions = @SQL_Dimentions + ' )x'; EXECUTE SP_EXECUTESQL @SQL_Dimentions; --####################################################################################################################### --###| Step 4 - Preparing table with all possible connections between Stable columns excluding NULLs --####################################################################################################################### DECLARE @StabPos_TAB TABLE ([StabPos_ID] smallint, [ColumnName] nvarchar(128)) INSERT INTO @StabPos_TAB SELECT [StabPos_ID] = ROW_NUMBER() OVER(ORDER BY [ColumnName]), [ColumnName] FROM #ColumnListWithActions WHERE [Action] = 'S'; DECLARE @StabPos_ID smallint; SELECT @StabPos_ID = 1; DECLARE @SQL_MainStableColumnTable nvarchar(max); IF OBJECT_ID('tempdb.dbo.##ALL_StableColumns', 'U') IS NOT NULL DROP TABLE ##ALL_StableColumns; SELECT @SQL_MainStableColumnTable = 'SELECT ID = ROW_NUMBER() OVER (ORDER BY ' + @ListOfColumns_Stable + '), ' + @ListOfColumns_Stable + ' INTO ##ALL_StableColumns ' + ' FROM (SELECT DISTINCT' + @ListOfColumns_Stable + ' FROM ' + @TableName + ' WHERE ' + (SELECT [ColumnName] FROM @StabPos_TAB WHERE [StabPos_ID] = @StabPos_ID) + ' IS NOT NULL '; SELECT @StabPos_ID = @StabPos_ID + 1; WHILE @StabPos_ID <= (SELECT MAX([StabPos_ID]) FROM @StabPos_TAB) BEGIN SELECT @SQL_MainStableColumnTable = @SQL_MainStableColumnTable + 'AND ' + (SELECT [ColumnName] FROM @StabPos_TAB WHERE [StabPos_ID] = @StabPos_ID) + ' IS NOT NULL '; SELECT @StabPos_ID = @StabPos_ID + 1; END SELECT @SQL_MainStableColumnTable = @SQL_MainStableColumnTable + ' )x'; EXECUTE SP_EXECUTESQL @SQL_MainStableColumnTable; --####################################################################################################################### --###| Step 5 - Preparing table with all options ID --####################################################################################################################### DECLARE @FULL_SQL_1 NVARCHAR(MAX) SELECT @FULL_SQL_1 = '' DECLARE @i smallint IF OBJECT_ID('tempdb.dbo.##FinalTab', 'U') IS NOT NULL DROP TABLE ##FinalTab; SELECT @FULL_SQL_1 = 'SELECT t.*, dim.[ID] ' + ' INTO ##FinalTab ' + 'FROM ' + @TableName + ' t ' + 'JOIN ##ALL_Dimentions dim ' + 'ON t.' + (SELECT [ColumnName] FROM @DIM_TAB WHERE [DIM_ID] = 1) + ' = dim.' + (SELECT [ColumnName] FROM @DIM_TAB WHERE [DIM_ID] = 1); SELECT @i = 2 WHILE @i <= (SELECT MAX([DIM_ID]) FROM @DIM_TAB) BEGIN SELECT @FULL_SQL_1 = @FULL_SQL_1 + ' AND t.' + (SELECT [ColumnName] FROM @DIM_TAB WHERE [DIM_ID] = @i) + ' = dim.' + (SELECT [ColumnName] FROM @DIM_TAB WHERE [DIM_ID] = @i) SELECT @i = @i +1 END EXECUTE SP_EXECUTESQL @FULL_SQL_1 --####################################################################################################################### --###| Step 6 - Selecting final data --####################################################################################################################### DECLARE @STAB_TAB TABLE ([STAB_ID] smallint, [ColumnName] nvarchar(128)) INSERT INTO @STAB_TAB SELECT [STAB_ID] = ROW_NUMBER() OVER(ORDER BY [ColumnName]), [ColumnName] FROM #ColumnListWithActions WHERE [Action] = 'S'; DECLARE @VAR_TAB TABLE ([VAR_ID] smallint, [ColumnName] nvarchar(128)) INSERT INTO @VAR_TAB SELECT [VAR_ID] = ROW_NUMBER() OVER(ORDER BY [ColumnName]), [ColumnName] FROM #ColumnListWithActions WHERE [Action] = 'V'; DECLARE @y smallint; DECLARE @x smallint; DECLARE @z smallint; DECLARE @FinalCode nvarchar(max) SELECT @FinalCode = ' SELECT ID1.*' SELECT @y = 1 WHILE @y <= (SELECT MAX([ID]) FROM ##FinalTab) BEGIN SELECT @z = 1 WHILE @z <= (SELECT MAX([VAR_ID]) FROM @VAR_TAB) BEGIN SELECT @FinalCode = @FinalCode + ', [ID' + CAST((@y) as varchar(10)) + '.' + (SELECT [ColumnName] FROM @VAR_TAB WHERE [VAR_ID] = @z) + '] = ID' + CAST((@y + 1) as varchar(10)) + '.' + (SELECT [ColumnName] FROM @VAR_TAB WHERE [VAR_ID] = @z) SELECT @z = @z + 1 END SELECT @y = @y + 1 END SELECT @FinalCode = @FinalCode + ' FROM ( SELECT * FROM ##ALL_StableColumns)ID1'; SELECT @y = 1 WHILE @y <= (SELECT MAX([ID]) FROM ##FinalTab) BEGIN SELECT @x = 1 SELECT @FinalCode = @FinalCode + ' LEFT JOIN (SELECT ' + @ListOfColumns_Stable + ' , ' + @ListOfColumns_Variable + ' FROM ##FinalTab WHERE [ID] = ' + CAST(@y as varchar(10)) + ' )ID' + CAST((@y + 1) as varchar(10)) + ' ON 1 = 1' WHILE @x <= (SELECT MAX([STAB_ID]) FROM @STAB_TAB) BEGIN SELECT @FinalCode = @FinalCode + ' AND ID1.' + (SELECT [ColumnName] FROM @STAB_TAB WHERE [STAB_ID] = @x) + ' = ID' + CAST((@y+1) as varchar(10)) + '.' + (SELECT [ColumnName] FROM @STAB_TAB WHERE [STAB_ID] = @x) SELECT @x = @x +1 END SELECT @y = @y + 1 END SELECT * FROM ##ALL_Dimentions; EXECUTE SP_EXECUTESQL @FinalCode; --####################################################################################################################### 

पहली क्वेरी निष्पादित करने से (स्रोत डीबी और तालिका का नाम देकर) आपको दूसरी सपा के लिए एक पूर्व निर्मित निष्पादन क्वेरी मिल जाएगी, आपको जो करना है उसे परिभाषित करना आपके स्रोत से है: + स्थिर + मूल्य (उपयोग किया जाएगा उस पर आधारित मूल्यों को ध्यान केंद्रित करने के लिए) + मंद (स्तंभ जिसे आप धुएं से उपयोग करना चाहते हैं)

नाम और डेटाटाइप स्वचालित रूप से परिभाषित किया जाएगा!

मैं इसे किसी भी उत्पादन वातावरण के लिए अनुशंसा नहीं कर सकता लेकिन एडीहोक बीआई अनुरोधों के लिए काम करता है

इसके बारे में भी एक अच्छा लेख देखें: स्तंभों को पंक्तियों में परिवर्तित करना (पीआईवीओटी) और पंक्तियों के स्तंभ (यूएनपीआईवीओटी) SQL सर्वर में