2013-04-01 14:11:17 8 Comments
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?
Related Questions
Sponsored Content
28 Answered Questions
[SOLVED] How can I prevent SQL injection in PHP?
- 2008-09-12 23:55:00
- Andrew G. Johnson
- 1528844 View
- 2779 Score
- 28 Answer
- Tags: php mysql sql security sql-injection
24 Answered Questions
[SOLVED] Find all tables containing column with specified name - MS SQL Server
- 2011-01-31 10:12:26
- gruber
- 1646041 View
- 960 Score
- 24 Answer
- Tags: sql sql-server tsql system-tables
44 Answered Questions
[SOLVED] How to concatenate text from multiple rows into a single text string in SQL server?
- 2008-10-11 23:49:59
- JohnnyM
- 1958232 View
- 1637 Score
- 44 Answer
- Tags: sql sql-server csv string-concatenation group-concat
4 Answered Questions
[SOLVED] Inserting multiple rows in a single SQL query?
- 2009-01-17 05:55:54
- rits
- 2336942 View
- 1484 Score
- 4 Answer
- Tags: sql sql-server tsql insert
32 Answered Questions
[SOLVED] How do I UPDATE from a SELECT in SQL Server?
- 2010-02-25 14:36:53
- jamesmhaley
- 3684896 View
- 3241 Score
- 32 Answer
- Tags: sql sql-server tsql select
15 Answered Questions
[SOLVED] How to Delete using INNER JOIN with SQL Server?
- 2013-05-10 11:38:21
- nettoon493
- 1066157 View
- 1003 Score
- 15 Answer
- Tags: sql sql-server sql-server-2008 inner-join sql-delete
38 Answered Questions
[SOLVED] How to return only the Date from a SQL Server DateTime datatype
- 2008-09-22 03:31:33
- eddiegroves
- 2432827 View
- 1550 Score
- 38 Answer
- Tags: sql sql-server tsql date datetime
24 Answered Questions
[SOLVED] How to check if a column exists in a SQL Server table?
- 2008-09-25 12:34:00
- Maciej
- 1058604 View
- 1674 Score
- 24 Answer
- Tags: sql-server sql-server-2008 tsql sql-server-2012 sql-server-2016
7 Answered Questions
[SOLVED] What are the options for storing hierarchical data in a relational database?
- 2010-10-29 00:23:33
- orangepips
- 203444 View
- 1166 Score
- 7 Answer
- Tags: sql database tree relational-database hierarchical-data
37 Answered Questions
[SOLVED] Add a column with a default value to an existing table in SQL Server
- 2008-09-18 12:30:04
- Mathias
- 2520003 View
- 2443 Score
- 37 Answer
- Tags: sql sql-server sql-server-2005 sql-server-2000
2 comments
@Bartosz X 2017-01-23 16:01:02
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 scriptFrom 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.
@DarXyde 2018-09-19 16:10:11
All good until
Could not find stored procedure 'dbo.sp_PivotIt'.
Any advice?@Bartosz X 2018-09-24 14:02:55
@DarXyde sory I must mixed 2 versions, please recompile and try again
@DarXyde 2018-09-25 18:35:40
Thanks Bartosz, managed to use some of the ideas from your script and done what I had on my mind already, but nevertheless, thanks for updating it :) . I should have thought to change that line, but honestly thought is a stored procedure you've forgot is not default in the system or something like that. I will give it a run when i get close to that project again, and update here!
@DarXyde 2018-09-26 10:02:03
Still the wrong name of the SP, but now that i know what's wrong, easy to fix:
sp_Pivot_Execute
change toproc_PivotExecute
.@Bartosz X 2018-09-26 10:36:31
@DarXyde - thanks, for spotting that - this has now been fixed
@Taryn 2013-04-01 14:13:33
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:See Demo.
If you have an unknown number of
columnnames
that you want to transpose, then you can use dynamic SQL:See Demo.
If you do not want to use the
PIVOT
function, then you can use an aggregate function with aCASE
expression:See Demo.
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:
@Gordon Linoff 2013-04-01 14:29:47
+1 . . . But in the last example, you can use
cross join
rather thanleft join
because each subquery returns one row.@tbag 2013-04-01 14:32:26
I need to build a dynamic query because I don't know the number of rows. Lets talk about converting a table with 10.000.000 millions of records
@Taryn 2013-04-01 14:33:41
@tbag If you have an unknown number of rows, then you would have to use dynamic sql but be aware that transforming millions of rows will not be efficient.
@tbag 2013-04-01 14:37:09
I can't use a view for that transformation, what about using a TFV? that's why I'm looking for an efficient solution.
@Taryn 2013-04-01 14:39:06
@tbag Dynamic SQL cannot be used in a view or a table valued function, it must be used in a stored procedure
@tbag 2013-04-01 14:58:54
Suppose that I need to convert my rows into columns, I should convert it to a stored procedure, but in that case, I can't use it as a main table for running queries... is there any other way?
@Taryn 2013-04-01 15:01:01
@tbag Well that is a different question, what exactly are you going to want to JOIN on once this is transposed? You could create a temp table that has this data loaded into it and then you can use the join in your joins.
@tbag 2013-04-01 15:18:19
I want to convert my rows into columns an then use it in queries, before I was using the cases with max() in a view, now I would like to replace it for something better}.
@tbag 2013-04-01 15:20:46
what is exactly doing this: .value('.', 'NVARCHAR(MAX)') in your previous query? thanks!
@Taryn 2013-04-01 15:21:17
@tbag Unfortunately if you want to use dynamic sql then you will have to use a stored procedure, then if you need to join on the result you will have to load the data into a table for use.
@Taryn 2013-04-01 15:22:06
@tbag this code
.value('.', 'NVARCHAR(MAX)')
is used with thestuff
function andfor xml path
to get the list of values to be converted to columns.@tbag 2013-04-01 15:23:51
thank you for your help!
@Haminteu 2014-08-07 04:58:30
@bluefeet, Well this is a good one. I've a question. In the first syntax of your answer, I tried that... But with 20 records, why its only show 1 - 5 records only?
@Taryn 2014-08-07 11:04:50
@Haminteu If you are pivoting a string and you have multiple rows for each item you are grouping by, then you'll need to use
row_number()
to return multiple rows. Check out this answer to see what I mean.@devinbost 2014-09-06 20:09:57
You want a clue? Try creating a function that generates the statement and using a cross-apply to apply it to all of your values.
@navbingo 2014-12-15 02:46:51
@bluefeet i am having the same table structure, and i used the 2nd solution as suggested above [Dynamic solution].. when i execute it , it returning me error : The column 'id' was specified multiple times for 'p'.
@Beytan Kurt 2017-02-13 09:14:51
remember to cover column names with brackets in pivot statement.
@xyzabc 2019-01-30 07:45:44
Is there a way to use above aggregate function example with unknown number of columns? or can someone suggest me a way to do above same thing using oracle
@Taryn 2019-01-30 12:54:10
@xyzabc Yes, it can be done in Oracle - here is another answer I wrote with how to do it. I haven't worked with Oracle in years, so there might be better ways to do it now.