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'
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 row into column using SQL
If you want for a fixed set of Datasource
, you can transpose rows to columns using PIVOT like the following query.
SELECT *
FROM
[YOUR_TABLE_NAME]
PIVOT
(
MAX(Amount)
FOR Datasource IN ([AB01], [AB02], [AB03])
) AS PivotTable;
If this list of DataSources is dynamic you can use Dynamic PIVOT like the following query.
DECLARE @cols AS NVARCHAR(max) = Stuff((SELECT DISTINCT ', ' + Quotename(Datasource)
FROM [YOUR_TABLE_NAME]
FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
DECLARE @query AS NVARCHAR(max) = ' SELECT *
FROM [YOUR_TABLE_NAME]
PIVOT ( MAX(Amount)
FOR Datasource IN ('+@cols+') ) pvt';
EXECUTE(@query)
EDIT:
Adding Total Coulmn in the last.
DECLARE @cols AS NVARCHAR(max) = Stuff((SELECT DISTINCT ', ' + Quotename(Datasource)
FROM TABLE_NAME
FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
DECLARE @sumcol AS NVARCHAR(max) = ','
+ Stuff((SELECT DISTINCT '+ ISNULL(' + Quotename(Datasource) + ',0)'
FROM TABLE_NAME
FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, '') + ' as Total';
DECLARE @query AS NVARCHAR(max) = ' SELECT *' + @sumcol + '
FROM TABLE_NAME
PIVOT ( MAX(Amount)
FOR Datasource IN ('+@cols+') ) pvt';
EXECUTE(@query)
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
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: How to convert rows to columns
Here i tried this sql which is throwing error for duplicate values in field name.
This is because your GROUP BY
is on FieldName, id,Ticker,ClientCode
. You are therefore telling the RDBMS you want a row for every distinct group of those columns, and very clearly that would result in multiple rows for the same value of FieldName
.
Very likely the GROUP BY
and ORDER BY
shouldn't be there at all:
SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(FieldName)
FROM dbo.DynamicForm
WHERE Ticker='X'
AND ClientCode='Z'
FOR XML PATH(''), TYPE).value('(./text())[1]', 'nvarchar(MAX)') ,1,1,'');
Now we have sample data, I can provide a full solution. Personally, as well, I would use a conditional aggregate, rather than the restrictive PIVOT
operator, and build my entire statement in one go. I continue to use FOR XML PATH
as I assume you used it (rather than STRING_AGG
) due to being on SQL Server 2016 or prior.
DECLARE @SQL nvarchar(MAX),
@CRLF nchar(2) = NCHAR(13) + NCHAR(10);
SET @SQL = N'SELECT ' + STUFF((SELECT N',' + @CRLF + N' ' +
N'MAX(CASE FieldName WHEN ' + QUOTENAME(FieldName,'''') + N' THEN Value END) AS ' + QUOTENAME(FieldName)
FROM dbo.DynamicForm
GROUP BY FieldName
ORDER BY MIN(ID)
FOR XML PATH(''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,10,N'') + @CRLF +
N'FROM dbo.DynamicForm' + @CRLF +
N'WHERE Ticker = @Ticker' + @CRLF +
N' AND ClientCode = @ClientCode' + @CRLF +
N'GROUP BY [Order]' + @CRLF + --ORDER is a reserved keyword, and should not be used for object names
N'ORDER BY [Order];'; --ORDER is a reserved keyword, and should not be used for object names
DECLARE @Ticker varchar(10) = 'X',
@ClientCode varchar(10) = 'Z';
--Print @SQL; -- Your best friend
EXEC sys.sp_executesql @SQL, N'@Ticker varchar(10), @ClientCode varchar(10)', @Ticker, @ClientCode;
db<>fiddle
Related Topics
Calculate Missing Date Ranges and Overlapping Date Ranges Between Two Dates
Incorrect Parameter Count in the Call to Native Function 'Datediff'
Ms Access 2010: "Cannot Open Any More Databases."
Date Calculation with Parameter in Ssis Is Not Giving the Correct Result
Duplicate Groups of Records to Fill Multiple Date Gaps in Google Bigquery
How to Pivot on an Xml Column's Attributes in T-Sql
Recursive Cte in Presence of Circular References
Sql: Finding the Closest Lat/Lon Record on Google Bigquery
Postgres Column Does Not Exist
Cursor in Procedure Returning More Values Than Query
SQL Server Convert Columns to Rows
Update a Single Row with T-Sql
MySQL - Concatenate Two Tables
How to Write Blob from Oracle Column to the File System
Ssis Hidden Sheets as Excel Destination
Dynamic Pivot Table with Multiple Columns in SQL Server
Handling Unicode Characters That Aren't Displayed Correctly in SQL Query