The XML option to transposing rows into columns is basically an optimal version of the PIVOT in that it addresses the dynamic column limitation. The XML version of the script addresses this limitation by using a combination of XML Path, dynamic T-SQL and some built-in functions (i.e. STUFF, QUOTENAME). I want to get results which are in rows in the form of columns. I'm quite new to pivot in SQL would like to know how can I apply pivot in below case. CODE: SELECT T.NAME, T.CODE, RN FROM( SELECT NAME, CODE, SUM(PROFIT), ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY NAME, SUM(PROFIT) DESC, CODE)RN FROM TABLE1 GROUP BY NAME, CODE)T WHERE RN <= 3 SUM() can only operate on the numeric data types. The numeric data types have a higher precedence than any textual data type 1.. So the -is converted to a number, e.g.:. select CONVERT(int,'-') Step 1 - Select the base dataset that you want to pivot. Let us first select the base dataset. For this, we can use the original query that we used to retrieve data from the AdventureWorksDW 4. This answer is not useful. Save this answer. Show activity on this post. You need to use aggregate on top of case statements. SELECT id, Max (CASE WHEN LEFT (areaID, 1) = 1 THEN 'Yes' END) Head, Max (CASE WHEN LEFT (areaID, 1) = 2 THEN 'Yes' END) Face, Max (CASE WHEN LEFT (areaID, 1) = 3 THEN 'Yes' END) Neck, Max (CASE WHEN LEFT (areaID, 1 QUOTENAME Function. The “QUOTENAME” function formats selected results. Before explaining dynamic pivot it is worth looking at a quick working example of “QUOTENAME” function. Take a look at the following query. USE schooldb SELECT QUOTENAME (city)+ ',' FROM student. By default, the “QUOTENAME” function wraps the selected items with The UNPIVOT will turn the PurchasingValue and SellingValue columns into rows. Once this is done, then you can pivot the data into your result. The code will be: select * from ( select itemid, case when col = 'PurchasingValue' then 'P' when col = 'SellingValue' then 'S' end + cast (year as varchar (4)) new_col, value from yourtable unpivot A literal expression with a type that shares a least common type with the respective column_name. The number of expressions in each tuple must match the number of column_names in column_list. column_alias. An optional alias specifying the name of the generated column. If no alias is specified PIVOT generates an alias based on the expression s. So 1st table will have the pivot table above counting only those records which have GROUP=S 2nd table will have the pivot table above counting only those records which have CODE=1. and so on, I wish to put multiple conditions. and generate several tables one by one and export them. If this is possible in SQL please let me know! 1. First thing to understand about pivots, you pick a single column in a result set to act as the as the PIVOT anchor, the hinge that the data will be pivoted around, this is specified in the FOR clause. You can only PIVOT FOR a single column, but you can construct this column in a subquery or from joins or views as your target data query, OP famC.