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 the data, split rows into columns in SQL
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.CountryName)
FROM #temptest c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT ObjectName, ' + @cols + ' from
(
select ObjectName,CountryName,counts
from #temptest
) x
pivot
(
max(counts)
for CountryName in (' + @cols + ')
) p '
execute(@query)
How to convert row into columns in SQL?
You want to unpivot the data. The challenge is dealing with the datatypes. You need to convert them all to the same type. Presumably, this only applies to amount
and perhaps to accountnumber
:
select firstName as anyName from t
union all
select cast(Amount as char) from t
union all
select PostalCode from t
union all
select LastName from t
union all
select cast(AccountNumber as char) from t;
If your table is very large or is really a complicated view, then there are other methods that don't require scanning the table once for each column.
You can also use cross join
and case
:
select (case when n.n = 1 then firstName
when n.n = 2 then cast(Amount as char)
when n.n = 3 then PostalCode
when n.n = 4 then lastName
when n.n = 5 then cast(AccountNumber as char)
end) as anyName
from t cross join
(select 1 as n union all select 2 union all select 3 union all select 4 union all select 5
) n
SQL convert rows to columns and flatten
Is this what you want?
select t1.partnum, t2.width, t2.height, t2.depth, count(*) as cnt
from t1 join
(select t2.orderid,
sum(case when ccode = 'width' then value end) as width,
sum(case when ccode = 'height' then value end) as height,
sum(case when ccode = 'depth' then value end) as depth
from t2
group by t2.orderid
) t2
on t2.orderid = t1.orderid
group by t1.partnum, t2.width, t2.height, t2.depth;
I might speculate that you want:
sum(t2.width * t2.height * t2.depth) as area
but the numbers disagree with the values in your question.
Here is a db<>fiddle.
How can I convert these four rows into columns using SQL
Do a GROUP BY
. Use case
expressions to do conditional aggregation:
select id,
max(case when Steps = 'Create' then Steps_Date end) as create_date,
max(case when Steps = 'Request' then Steps_Date end) as Request_date,
max(case when Steps = 'Schedule' then Steps_Date end) as Schedule_date,
max(case when Steps = 'Complete' then Steps_Date end) as Complete_date
from Steps_table
group by id
Related Topics
How to Find the Last Modified Date, Modified User of an Stored Procedure in SQL Server 2008
Retrieve the Maximum Length of a Varchar Column in SQL Server
Database Design for 'Followers' and 'Followings'
Find Working Days Based on Weekly or Monthly Schedule
Improving Query Speed: Simple Select in Big Postgres Table
Teradata, Reset When, Partition By, Order By
SQL Query Question: Select ... Not in
Returning the Distinct First Character of a Field (Mysql)
Rows to Comma Separated Values Using Xml Tag
Pivot a Table on a Value But Group the Data on One Line by Another
How to Parse JSON in Oracle SQL? (Version:11.2.0)
T-SQL Query:Getting Child Nodes of a Parent
Which Table Exactly Is the "Left" Table and "Right" Table in a Join Statement (Sql)
Schedule Import CSV to SQL Server 2014 Express Edition
Understanding the Differences Between Cube and Rollup