Efficiently convert rows to columns in sql server
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
(
select value, 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 @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 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:
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 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.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'
Convert Rows to columns using 'Pivot' in SQL Server
If you are using SQL Server 2005+, then you can use the PIVOT
function to transform the data from rows into columns.
It sounds like you will need to use dynamic sql if the weeks are unknown but it is easier to see the correct code using a hard-coded version initially.
First up, here are some quick table definitions and data for use:
CREATE TABLE yt
(
[Store] int,
[Week] int,
[xCount] int
);
INSERT INTO yt
(
[Store],
[Week], [xCount]
)
VALUES
(102, 1, 96),
(101, 1, 138),
(105, 1, 37),
(109, 1, 59),
(101, 2, 282),
(102, 2, 212),
(105, 2, 78),
(109, 2, 97),
(105, 3, 60),
(102, 3, 123),
(101, 3, 220),
(109, 3, 87);
If your values are known, then you will hard-code the query:
select *
from
(
select store, week, xCount
from yt
) src
pivot
(
sum(xcount)
for week in ([1], [2], [3])
) piv;
See SQL Demo
Then if you need to generate the week number dynamically, your code will be:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(Week)
from yt
group by Week
order by Week
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT store,' + @cols + ' from
(
select store, week, xCount
from yt
) x
pivot
(
sum(xCount)
for week in (' + @cols + ')
) p '
execute(@query);
See SQL Demo.
The dynamic version, generates the list of week
numbers that should be converted to columns. Both give the same result:
| STORE | 1 | 2 | 3 |
---------------------------
| 101 | 138 | 282 | 220 |
| 102 | 96 | 212 | 123 |
| 105 | 37 | 78 | 60 |
| 109 | 59 | 97 | 87 |
Convert row value in to column in SQL server (PIVOT)
Try this one:
SELECT *
FROM (SELECT Item, attribute, value FROM MyTable) AS t
PIVOT
(
MAX(value)
FOR attribute IN([Quality], [Color])
) AS p;
Output:
╔═══════╦═════════╦═══════╗
║ ITEM ║ QUALITY ║ COLOR ║
╠═══════╬═════════╬═══════╣
║ item1 ║ A ║ Red ║
║ item2 ║ B ║ Black ║
╚═══════╩═════════╩═══════╝
See this SQLFiddle
You can also use this dynamic query if you don't know the specific value of attribute
:
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(attribute)
from MyTable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT Item,' + @cols + '
from
(
Select Item, attribute , value
from MyTable
) dta
pivot
(
MAX(Value)
for attribute in (' + @cols + ')
) pvt '
execute(@query);
See this SQLFiddle
SQL Server - Pivot Convert rows to columns (with additional row data)
I think conditional aggregation does what you want:
select id, type, color, date,
max(case when country_code = 'US' then cost end) as us,
max(case when country_code = 'EU' then cost end) as eu,
max(case when country_code = 'RU' then cost end) as ru,
max(case when country_code = 'AP' then cost end) as AP
from t
group by id, type, color, date;
Converting 200+ rows to column in SQL Server using PIVOT
You can use dynamic sql to create the list of col_name:
declare @pivot_col varchar(max);
declare @sql varchar(max);
select @pivot_col = string_agg( cast(col_name as varchar(max)), ', ') within group ( order by col_name ) from ( select distinct col_name from tmp_table ) A;
set @sql = 'SELECT *
FROM (
SELECT [ID], [Col_Name], [Col_Value]
FROM tmp_table
) AS a
PIVOT
(
MAX([Col_Value])
FOR [Col_Name] in (' + @pivot_col + ' )
) AS p
ORDER BY [ID]';
exec ( @sql );
The PIVOT / UNPIVOT operators are built on the principles of an Entity Attribute Value model (EAV). The idea behind an EAV model is that you can extend database entities without performing database schema changes. For that reason an EAV model stores all attributes of an entity in one table as key/value pairs.
If that is the idea behind your design then use the dynamic sql query i posted above, otherwise use a regular record with 200+ columns for each id, as suggested by Gordon.
You can read about the performance of PIVOT / UNPIVOT operators here.
EDIT:
For sql server 2016 version:
declare @pivot_col varchar(max);
declare @sql varchar(max);
select @pivot_col = STUFF( (SELECT ',' + CAST(col_name AS VARCHAR(max)) AS [text()] FROM ( select distinct col_name from tmp_table ) A ORDER BY col_name FOR XML PATH('')), 1, 1, NULL);
set @sql = 'SELECT *
FROM ( SELECT [ID], [Col_Name], [Col_Value]
FROM tmp_table
) AS a
PIVOT
(
MAX([Col_Value])
FOR [Col_Name] in (' + @pivot_col + ' )
) AS p
ORDER BY [ID]';
exec ( @sql );
Convert Single Row To Single Column Using Pivot/unpivot In Sql Server
I would recommend using apply
:
select v.col
from t cross apply
(values (col1), (col2), (col3), (col4), (col5)) v(col);
I strongly recommend apply
over unpivot
. However, they are not 100% equivalent, because unpivot
filters out NULL
values.
Pivot/transpose rows into columns efficiently with multiple columns
SELECT
[Num1],
[Type1],
[Code],
[Group],
[DA],
[123],
[234]
FROM
yourTable
PIVOT
(
MAX([value])
FOR [account] IN ([123], [234])
)
AS PivotTable
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=7fbe16b9254aa5ee60a23e43eec9597f
Related Topics
How to View All the Metadata of Columns of a Table in Oracle Database
Is the 'As' Keyword Required in Oracle to Define an Alias
How to Find the Number of Occurrences of a Particular Character in a String Using SQL
Can the "In" Operator Use Like-Wildcards (%) in Oracle
How to Get a Value from Previous Result Row of a Select Statement
In Postgres, Can You Set the Default Formatting for a Timestamp, by Session or Globally
How to Read a Text File Using T-Sql
What Is the Effect of Omitting Size in Nvarchar Declaration
Tsql: How to Retrieve the Last Date of Each Month Between Given Date Range
Postgres Time with Time Zone Equality
Finding Rows with Consecutive Increase in the Values of a Column
What's the Difference Between "Like" and "=" in SQL
How to Use a SQL Update Statement to Add 1 Year to a Datetime Column
Oracle SQL - Sum and Group Data by Week
Querying More Than 65536 Rows Error in Excel 2013
SQL Server, Converting Seconds to Minutes, Hours, Days