75713

Efficiently convert rows to columns in sql server

Question:

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:

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

This is my result:

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

How can I build the result?

Answer1:

There are several ways that you can transform data from multiple rows into columns. In SQL Server you can use the PIVOT function to transform the data from rows to columns:

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;

See <a href="https://data.stackexchange.com/stackoverflow/query/497432" rel="nofollow">Demo</a>.

If you have an unknown number of columnnames that you want to transpose, then you can use dynamic 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;

See <a href="https://data.stackexchange.com/stackoverflow/query/497433" rel="nofollow">Demo</a>.

If you do not want to use the PIVOT function, then you can use an aggregate function with a CASE expression:

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

See <a href="https://data.stackexchange.com/stackoverflow/query/497434" rel="nofollow">Demo</a>.

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.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'

Answer2:

as pivoting data is still a hot one I decided to add something form me. This is rather a method than just a single script but gives you much more possibilities. First of all There are 3 scripts you need to deploy: 1) User defined TABLE type [ColumnActionList] -> holds data as parameter 2) SP [proc_PivotPrepare] -> prepares our data 3) SP [proc_PivotExecute] -> execute the script

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_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 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; --#######################################################################################################################

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.

Recommend

  • Efficiently convert rows to columns in sql server
  • Postgres Insert Into View Rule with Returning Clause
  • Type of conditional expression cannot be determined because there is no implicit conversion between
  • intranet jsf application opening in compatibility mode in IE9
  • Sqlserver table Row to column data - pivot table
  • How to convert the ENUM value to String from view to a controller
  • tensorflow - softmax ignore negative labels (just like caffe) [duplicate]
  • what is the command to create a soft link with haskell turtle?
  • Rows to Columns using Sql Query
  • How to make local USB port to work with Azure virtual machine
  • containerless statements of knockoutjs is not working in hottowel SPA?
  • Add Special Case to Power BI Query (M)
  • InputDispatcher Error
  • Background image does not show on iPhone/iPad
  • what does prefix @- mean in makefile?
  • How can I make this modal persistent?
  • Getting error 'Cannot read property 'document' of undefined' while importing exp
  • quiver not drawing arrows just lots of blue, matlab
  • How to print columns containing value
  • Sending keystrokes/mouse clicks to a Java program with Autohotkey
  • Elasticsearch script query involving root and nested values
  • Execute scripts AJAX returns
  • Check for zero lines output from command over SSH
  • What and where is mdimport
  • Listbox within Listbox and scrolling trouble in Windows Phone 7 Silverlight
  • Disable Enter in editText android
  • Jetty Server not starting: Unable to establish loopback connection
  • How to install a .deb file on a jailbroken iphone programmatically?
  • Django: Count of Group Elements
  • DomPDF {PAGE_NUM} not on first page
  • Javascript simulate pressing enter in input box
  • How to check if every primary key value is being referenced as foreign key in another table
  • How to handle AllServersUnavailable Exception
  • Redux, normalised entities and lodash merge
  • Importing jscolor library in angular 2
  • Do create extension work in single-user mode in postgres?
  • How to get next/previous record number?
  • A cron job substitute?
  • python regex in pyparsing
  • Memory offsets in inline assembly