There are multiple ways using which you can transform data from multiple rows into multiple columns.
In SQL Server, by using PIVOT function you can transform the data from rows to columns:
QUERY:
select Firstname, Amount, PostalCode, LastName, AccountNumber
from
(
select value, columnname
fromyourtable
) d
pivot
(
max(value)
for columnname in (Firstname, Amount, PostalCode, LastName, AccountNumber)
) piv;
You can also go through this Demo for better understanding.
- For an unknown number of column names that you want to transpose, you can use this dynamic SQL mentioned below:
QUERY:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName)
fromyourtable
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
fromyourtable
) x
pivot
(
max(value)
forColumnName in (' + @cols + N')
) p '
execsp_executesql @query;
You can also go through this Demo for better understanding.
Note: You can use an aggregate function with a CASE expression If you do not want to use the PIVOT function
Refer to this video to understand CASE STATEMENT in detail.
QUERY:
SELECT
MAX(CASE when Column_Name = 'First_Name' then value end) First_Name,
MAX (CASE when Column_Name = 'Amount' then value end) Amount,
MAX (CASE when Column_Name = 'Postal_Code' then value end) Postal_Code,
MAX(CASE when Column_Name = 'Last_Name' then value end) Last_Name,
MAX (CASE when Column_Name = 'Account_Number' then value end) Account_Number
fromMyTable
You can also refer to this Demo.
This can be completed only if you are using multiple joins, but you may need some columns to associate each of the rows which are not in the sample data. The basic syntax is like this:
QUERY:
selectfn.value as First_Name,
a.value as Amount,
pc.value as Postal_Code,
ln.value as Last_Name,
an.value as Account_Number
fromMyTablefn
left join MyTable a
onfn.some_col = a.some_col
anda.Column_Name = 'Amount'
left join MyTable pc
onfn.some_col = pc.some_col
andpc.Column_Name = 'Postal_Code'
left join MyTableln
onfn.some_col = ln.some_col
andln.Column_Name = 'Last_Name'
left join MyTable an
onfn.some_col = an.some_col
andan.Column_Name = 'Account_Number'
To master SQL statements, queries and become proficient in SQL queries, enroll in our industry-recognized SQL course.
Back