Efficiently Convert Rows To Columns In Sql Server
I'm looking for an efficient way to convert rows to columns in SQL server, I heard that PIVOT is not very fast, and I need to deal with lot of records. This is my example: -----
Solution 1:
There are several ways that you can transform data from multiple rows into columns.
Using PIVOT
In SQL Server you can use the PIVOT function to transform the data from rows to columns:
select Firstname, Amount, PostalCode, LastName, AccountNumber
from
(
selectvalue, columnname
from yourtable
) d
pivot
(
max(value)
for columnname in (Firstname, Amount, PostalCode, LastName, AccountNumber)
) piv;
See Demo.
Pivot with unknown number of columnnames
If you have an unknown number of columnnames that you want to transpose, then you can use dynamic SQL:
DECLARE@colsAS NVARCHAR(MAX),
@queryAS NVARCHAR(MAX)
select@cols= STUFF((SELECT','+ QUOTENAME(ColumnName)
from yourtable
groupby ColumnName, id
orderby 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;
See Demo.
Using an aggregate function
If you do not want to use the PIVOT function, then you can use an aggregate function with a CASE expression:
selectmax(casewhen columnname ='FirstName'thenvalueend) Firstname,
max(casewhen columnname ='Amount'thenvalueend) Amount,
max(casewhen columnname ='PostalCode'thenvalueend) PostalCode,
max(casewhen columnname ='LastName'thenvalueend) LastName,
max(casewhen columnname ='AccountNumber'thenvalueend) AccountNumber
from yourtable
See Demo.
Using multiple joins
This could also be completed using multiple joins, but you will need some column to associate each of the rows which you do not have in your sample data. But the basic syntax would be:
select fn.valueas FirstName,
a.valueas Amount,
pc.valueas PostalCode,
ln.valueas LastName,
an.valueas 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'Solution 2:
This is rather a method than just a single script but gives you much more flexibility.
First of all There are 3 objects:
- User defined TABLE type [
ColumnActionList] -> holds data as parameter - SP [
proc_PivotPrepare] -> prepares our data - SP [
proc_PivotExecute] -> execute the script
CREATE TYPE [dbo].[ColumnActionList] AS TABLE ( [ID] [smallint] NOT NULL, [ColumnName] nvarchar NOT NULL, [Action] nchar NOT NULL ); GO
CREATEPROCEDURE [dbo].[proc_PivotPrepare]
(
@DB_Name nvarchar(128),
@TableName nvarchar(128)
)
ASSELECT@DB_Name = ISNULL(@DB_Name,db_name())
DECLARE@SQL_Code nvarchar(max)
DECLARE@MyTabTABLE (ID smallintidentity(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_PivotExecute with parameters: columns and dimentions and main table name'' '+'UNION ALL '+'SELECT ''----------------------------------------------------------------------------------------------------'' '+'UNION ALL '+'SELECT ''EXEC [dbo].[sp_PivotExecute] @ColumnListWithActions, '+''''''+@TableName+''''''+';'''+'UNION ALL '+'SELECT ''----------------------------------------------------------------------------------------------------'' 'EXECUTE SP_EXECUTESQL @SQL_Code;
GO
CREATEPROCEDURE [dbo].[sp_PivotExecute]
(
@ColumnListWithActions ColumnActionList ReadOnly
,@TableName nvarchar(128)
)
AS--#######################################################################################################################--###| Step 1 - Select our user-defined-table-variable into temp table--#######################################################################################################################
IF OBJECT_ID('tempdb.dbo.#ColumnListWithActions', 'U') ISNOTNULLDROPTABLE #ColumnListWithActions;
SELECT*INTO #ColumnListWithActions FROM@ColumnListWithActions;
--#######################################################################################################################--###| Step 2 - Preparing lists of column groups as strings:--#######################################################################################################################DECLARE@ColumnName nvarchar(128)
DECLARE@Destinynchar(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 CURSORFORSELECT [ColumnName]
FROM #ColumnListWithActions
WHERE [Action] ='S'OPEN ColumnListStringCreator_S;
FETCH NEXT FROM ColumnListStringCreator_S
INTO@ColumnName
WHILE @@FETCH_STATUS =0BEGINSELECT@ListOfColumns_Stable = ISNULL(@ListOfColumns_Stable, '') +' ['+@ColumnName+'] ,';
FETCH NEXT FROM ColumnListStringCreator_S INTO@ColumnNameENDCLOSE ColumnListStringCreator_S;
DEALLOCATE ColumnListStringCreator_S;
--############################--###| Cursor for List of Dimension Columns--############################DECLARE ColumnListStringCreator_D CURSORFORSELECT [ColumnName]
FROM #ColumnListWithActions
WHERE [Action] ='D'OPEN ColumnListStringCreator_D;
FETCH NEXT FROM ColumnListStringCreator_D
INTO@ColumnName
WHILE @@FETCH_STATUS =0BEGINSELECT@ListOfColumns_Dimension = ISNULL(@ListOfColumns_Dimension, '') +' ['+@ColumnName+'] ,';
FETCH NEXT FROM ColumnListStringCreator_D INTO@ColumnNameENDCLOSE ColumnListStringCreator_D;
DEALLOCATE ColumnListStringCreator_D;
--############################--###| Cursor for List of Variable Columns--############################DECLARE ColumnListStringCreator_V CURSORFORSELECT [ColumnName]
FROM #ColumnListWithActions
WHERE [Action] ='V'OPEN ColumnListStringCreator_V;
FETCH NEXT FROM ColumnListStringCreator_V
INTO@ColumnName
WHILE @@FETCH_STATUS =0BEGINSELECT@ListOfColumns_Variable = ISNULL(@ListOfColumns_Variable, '') +' ['+@ColumnName+'] ,';
FETCH NEXT FROM ColumnListStringCreator_V INTO@ColumnNameENDCLOSE 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))
INSERTINTO@DIM_TAB
SELECT [DIM_ID] =ROW_NUMBER() OVER(ORDERBY [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') ISNOTNULLDROPTABLE ##ALL_Dimentions;
SELECT@SQL_Dimentions ='SELECT [xxx_ID_xxx] = 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 <= (SELECTMAX([DIM_ID]) FROM@DIM_TAB)
BEGINSELECT@SQL_Dimentions =@SQL_Dimentions +'AND '+ (SELECT [ColumnName] FROM@DIM_TAB WHERE [DIM_ID] =@DIM_ID) +' IS NOT NULL ';
SELECT@DIM_ID =@DIM_ID +1;
ENDSELECT@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))
INSERTINTO@StabPos_TAB
SELECT [StabPos_ID] =ROW_NUMBER() OVER(ORDERBY [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') ISNOTNULLDROPTABLE ##ALL_StableColumns;
SELECT@SQL_MainStableColumnTable ='SELECT xxx_ID_xxx = 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 <= (SELECTMAX([StabPos_ID]) FROM@StabPos_TAB)
BEGINSELECT@SQL_MainStableColumnTable =@SQL_MainStableColumnTable +'AND '+ (SELECT [ColumnName] FROM@StabPos_TAB WHERE [StabPos_ID] =@StabPos_ID) +' IS NOT NULL ';
SELECT@StabPos_ID =@StabPos_ID +1;
ENDSELECT@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@ismallint
IF OBJECT_ID('tempdb.dbo.##FinalTab', 'U') ISNOTNULLDROPTABLE ##FinalTab;
SELECT@FULL_SQL_1 ='SELECT t.*, dim.[xxx_ID_xxx] '+' 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<= (SELECTMAX([DIM_ID]) FROM@DIM_TAB)
BEGINSELECT@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+1ENDEXECUTE SP_EXECUTESQL @FULL_SQL_1
--#######################################################################################################################--###| Step 6 - Selecting final data--#######################################################################################################################DECLARE@STAB_TAB TABLE ([STAB_ID] smallint, [ColumnName] nvarchar(128))
INSERTINTO@STAB_TAB
SELECT [STAB_ID] =ROW_NUMBER() OVER(ORDERBY [ColumnName]), [ColumnName]
FROM #ColumnListWithActions WHERE [Action] ='S';
DECLARE@VAR_TAB TABLE ([VAR_ID] smallint, [ColumnName] nvarchar(128))
INSERTINTO@VAR_TAB
SELECT [VAR_ID] =ROW_NUMBER() OVER(ORDERBY [ColumnName]), [ColumnName]
FROM #ColumnListWithActions WHERE [Action] ='V';
DECLARE@ysmallint;
DECLARE@xsmallint;
DECLARE@zsmallint;
DECLARE@FinalCode nvarchar(max)
SELECT@FinalCode=' SELECT ID1.*'SELECT@y=1
WHILE @y<= (SELECTMAX([xxx_ID_xxx]) FROM ##FinalTab)
BEGINSELECT@z=1
WHILE @z<= (SELECTMAX([VAR_ID]) FROM@VAR_TAB)
BEGINSELECT@FinalCode=@FinalCode+', [ID'+CAST((@y) asvarchar(10)) +'.'+ (SELECT [ColumnName] FROM@VAR_TAB WHERE [VAR_ID] =@z) +'] = ID'+CAST((@y+1) asvarchar(10)) +'.'+ (SELECT [ColumnName] FROM@VAR_TAB WHERE [VAR_ID] =@z)
SELECT@z=@z+1ENDSELECT@y=@y+1ENDSELECT@FinalCode=@FinalCode+' FROM ( SELECT * FROM ##ALL_StableColumns)ID1';
SELECT@y=1
WHILE @y<= (SELECTMAX([xxx_ID_xxx]) FROM ##FinalTab)
BEGINSELECT@x=1SELECT@FinalCode=@FinalCode+' LEFT JOIN (SELECT '+@ListOfColumns_Stable +' , '+@ListOfColumns_Variable
+' FROM ##FinalTab WHERE [xxx_ID_xxx] = '+CAST(@yasvarchar(10)) +' )ID'+CAST((@y+1) asvarchar(10))
+' ON 1 = 1'
WHILE @x<= (SELECTMAX([STAB_ID]) FROM@STAB_TAB)
BEGINSELECT@FinalCode=@FinalCode+' AND ID1.'+ (SELECT [ColumnName] FROM@STAB_TAB WHERE [STAB_ID] =@x) +' = ID'+CAST((@y+1) asvarchar(10)) +'.'+ (SELECT [ColumnName] FROM@STAB_TAB WHERE [STAB_ID] =@x)
SELECT@x=@x+1ENDSELECT@y=@y+1ENDSELECT*FROM ##ALL_Dimentions;
EXECUTE SP_EXECUTESQL @FinalCode;
From executing the first query (by passing source DB and table name) you will get a pre-created execution query for the second SP, all you have to do is define is the column from your source: + Stable + Value (will be used to concentrate values based on that) + Dim (column you want to use to pivot by)
Names and datatypes will be defined automatically!
I cant recommend it for any production environments but does the job for adhoc BI requests.
Solution 3:
Please try
CREATETABLE pvt (Present int, [Absent] int);
GO
INSERTINTO pvt VALUES (10,40);
GO
--Unpivot the table.SELECT Code, ValueFROM
(SELECT Present, Absent
FROM pvt) p
UNPIVOT
(ValueFOR Code IN
(Present, [Absent])
)AS unpvt;
GO
DROPTABLE pvt
Post a Comment for "Efficiently Convert Rows To Columns In Sql Server"