pivot and unpivot in sql

A pivot table is a method for analyzing data. It contains a powerful data pivot function in Excel. What is data pivoting? It may be easier to give an example. Assume there is a data table: Sales volume of books ---------------------------------------- Wang Excel teaching material 10 Xiao Li Excel teaching material 15 Wang Word teaching material 8 Xiao Li Excel teaching material 7 John Excel teaching materia

]+ '] ' + ' as Y ' [email protected] SET @sql2 =rtrim (@sql2) + ', [' [Email protected]+ '] ' NEXT from Cur_expyear to @sn, @expyear END --2.4. Close the cursor and release the cursor CLOSE Cur_expyear Deallocate cur_expyear --3. Achieving goals SET @sql =rtrim (@sql) + ' from (SELECT dptno,year (indate) as Expyear from Tb_employee with (NOLOCK)) T PIVOT (' [emai

SQL Server row and column transformation hidden groupPivot has a hidden group group, except for pivot column and value column, and other columns as groupingsExample:IF not EXISTS (SELECT * from sys.tables where name = ' Pivot_test ')CREATE TABLE Pivot_test(ID1 int,Id2 int,Pivot_column varchar (50),Value char (50))INSERT into pivot_test values (A, ' A ', ' A_v '), (with the ' B ', ' B_v '), (with the ' C ',

aggregate function sum indicates how you want to handle the value of the converted column, whether it is the sum (sum), average (avg), Min,max, and so on. For example, if the Week_income table has two data and its week is "Monday", one of the income is 1000 and the other income is 500, then the sum is used here, and the value of the column "Monday" After row to column is of course 1500. After for [week] in ([Monday],[Tuesday] ...) In for [week] means that the values of the week column are conve

A pivot table is a method for analyzing data. It contains a powerful data pivot function in Excel. What is data pivoting? It may be easier to give an example. Assume there is a data table: Sales volume of books ---------------------------------------- Wang Excel teaching material 10 Xiao Li Excel teaching material 15 Wang Word teaching material 8 Xiao Li Excel teaching material 7 John Excel teaching materia

Today is just the data show, using the column change, row to column there are many ways, pivot is one of them, Povit is the function of SQL Server 2005,The following business scenario:Each month, the total quantity of the incoming channel is "totals", in Chinese, English, and other data columns,The original data is as follows:Demand:The data needs to be displayed as one line per year from January to Decembe

Tags: is sele into font an int BSP inline insertPivot data is actually a row state to an example stateAdd a test sheet firstIF object_id('dbo. Orders','U') is not NULL DROP TABLEdbo. Orders;GOCREATE TABLEdbo. Orders (OrderIDINT not NULL, OrderDate DATE not NULL, EmpidINT not NULL, CustIDVARCHAR(5) not NULL, QtyINT not NULL, CONSTRAINTPk_ordersPRIMARY KEY(OrderID));INSERT intodbo. Orders (OrderID, OrderDate, Empid, CustID, qty)VALUES (30001,'20070802',3,'A',Ten), (100

2400.00 3100.00 5003.00 The implementation steps are as follows: ----- Select Id,cname,sum (amount) as amount,left (CONVERT (varchar, actiondate,120), 7) as Actiondate Into #tmp From TB Group BY Id,cname,left (CONVERT (varchar, actiondate,120), 7) DECLARE @sql varchar (max), @sqlAll nvarchar (max) Select @sql = ' Select @sql = @

Project code in SQL Server:Select IsNull(Mi line,0) asF_mivalue,IsNull(SMT Line,0) asF_smtvalue,IsNull(Air compressor Station,0) asF_changepre,IsNull(Repair line,0) asF_repairvalue,IsNull(Assembly line,0) asF_assemblyvalue from(SelectF_name,CONVERT(decimal( -,2),sum(F_value)) asF_value fromEnergyvaluewhereF_name like '%mi%' orF_name like '%smt%'orF_name like '%repair%' orF_name like '%assembly%' orF_name= 'Air Compressor Station' Group byf_nam

]+ ', [' [emailprotected]+ '],[' [emailprotected]+ '] ' + ' from [' [email protected]+ ']) p PIVOT (SUM ([' [emailprotected]+ ']) for [' [emailprotected]+ '] in (' + @sql_col + ')) as Pvt Where Pvt.hx_fzone is not null for ORDER by ' [Emailprotected]--print (@sql_str) EXEC (@sql_str) drop table #t_estimateamo Untreasondrop Table #t_ Estimateamountreasontotalend---------------------------------------------------------------------------SELECT @ end_dat

Tags: Need convenient many year-over. com IDT Time Order salesIn general, when you use a form to view data, a pivot table is involved from a different dimension. For example, analyze sales data from product and time dimensions.When the need to analyze from the time dimension, but also want to have a year, the chain data, then the time dimension of the design column will greatly facilitate the writing of SQL.Such as: Products Week

Tags: SQL Server column career pivotToday is just the data show, using the column change, there are many ways to change the line, pivot is one of them, Povit is the function of SQL Server 2005, The following business scenario: Each month, the total quantity of the incoming channel is "totals", in Chinese, English, and other data columns, The original data is as f

= Folders.folderno Left joins PATIENTS PA on Pa. PID = orders. Pid Left join (select Ordno, results. Final,tests.testno, Tests.testcode From analytes Left joins Ordtask on Ordtask. Testcode = analytes. Testcode and Ordtask. METHOD = analytes. METHOD Left joins results on results. analyte = analytes. Analyte and results. Ordtaskno = Ordtask. Ordtaskno Left JOIN tests on tests.testcode = analytes. Testcode where Analytes.displayflag = ' [email protected]+ '] name on name.ordno = orders.ordno wher

Label:A data table in the field of years, months, days, amounts, payment methods and other fields, and now want to write a SQL statement, each day of the payment method amount (The payment method has multiple) row in the same line, Finally, add a column subtotal to the amount of all the current payment methods. Such as: The result of the original SQL query is this: ------------------------------------------

Label: -- query results before rotation SelectS.name Shiftname,h.businessentityid,d.name asDpartmentname fromhumanresources.employeedepartmenthistory HInner JoinHumanResources.Department D onH.departmentid=D.departmentidInner JoinHumanresources.shift S onS.shiftid=H.shiftidWHEREENDDATE is NULL andD.nameinch('Production','Engineering','Marketing') Order byShiftname --the result after rotation Selectshiftname,production,engineering,marketing from (SelectS.name Shiftname,h.businessentityid,

Analysis: In fact, it is the time of the month row to the columnTable structure Design:Results:Select Name,moth,isnull ([01],0) as Value1,isnull ([02],0) as Value2,isnull ([03],0) as Value3 from (Select A.name,convert (varchar (4), moth,120) as Moth,convert (varchar (2), moth,100) as Moth2,number from Table_type as aLeft join Table_shuju as B on a.id=b.idAs Table1 pivot (SUM (number) for moth2 in ([01],[02],[03])) as Table2：A

Previous examples:Http://www.cnblogs.com/insus/articles/1969896.htmlHttp://www.cnblogs.com/insus/articles/1970577.htmlHttp://www.cnblogs.com/insus/articles/1970707.htmlThese are all examples of SQL Server row and column conversions or row conversions. However, at the beginning of the SQL Server2005 version, there is a new function pivot () that can be easily impl