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)
Advanced convert rows to columns (pivot) in SQL Server
You can select the worker and if you don't want a duplicate row you can Group by the worker
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(workStation)
FROM TableName
group by workStation
ORDER BY workStation
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = N'SELECT worker,' + @cols + N' from
(
SELECT amount, worker, workStation
FROM TableName
) x
pivot
(
max(amount)
for workStationin (' + @cols + N')
) p '
exec sp_executesql @query;
Convert Rows to Columns SQL
You will have to go for a dynamic query, check if this will suit your needs.
I created a common table expression to be able to use distinct and then order by in the stuff function:
DECLARE @QUERY NVARCHAR(MAX)
DECLARE @Columns NVARCHAR(MAX)
WITH cte_unique_inspection_unit_number AS
(
SELECT DISTINCT QUOTENAME('TestResults' + CAST(inspection_unit_number AS VARCHAR)) TestResultsN,
inspection_unit_number
FROM IQC_Tensile_TF
)
SELECT @Columns = STUFF((SELECT ', ' + TestResultsN
FROM cte_unique_inspection_unit_number
ORDER BY inspection_unit_number
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,2,''),
@query = 'SELECT batch, node_number, characteristic, ' + @Columns + ' from
(
select batch,
node_number,
characteristic,
measured_value,
''TestResults'' + CAST(inspection_unit_number AS VARCHAR) TestResultsN
from IQC_Tensile_TF
) x
pivot
(
max(measured_value)
for TestResultsN in (' + @Columns + ')
) p '
EXEC(@query)
To view the execution in fiddle:
https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=7898422e4422faacb25d7f3c2285f14a
If you find my answer useful, i would appreciate if you vote up and mark as accepted =D
Convert rows to columns in SQL Server 2008
PIVOT isn't really what you're after, especially since you're trying to get multiple values from each row (which doesn't work very well with the aggregation PIVOT tries to offer).
I'm assuming here you want the most recent three licenses, in which case we can apply a row number per CustNum, ordered by ExpiryDate (newest first), then flip them so they are left-to-right oldest-to-newest:
;WITH cte AS
(
SELECT CustNum, LicenseAddress, License, ExpiryDate,
rn = ROW_NUMBER() OVER (PARTITION BY CustNum ORDER BY ExpiryDate DESC)
FROM dbo.Licenses
)
SELECT CustNum,
LicAddr1 = MAX(CASE WHEN rn = 3 THEN LicenseAddress END),
Lic1 = MAX(CASE WHEN rn = 3 THEN License END),
ExpiryDate1 = MAX(CASE WHEN rn = 3 THEN ExpiryDate END),
LicAddr2 = MAX(CASE WHEN rn = 2 THEN LicenseAddress END),
Lic2 = MAX(CASE WHEN rn = 2 THEN License END),
ExpiryDate2 = MAX(CASE WHEN rn = 2 THEN ExpiryDate END),
LicAddr3 = MAX(CASE WHEN rn = 1 THEN LicenseAddress END),
Lic3 = MAX(CASE WHEN rn = 1 THEN License END),
ExpiryDate3 = MAX(CASE WHEN rn = 1 THEN ExpiryDate END)
FROM cte
GROUP BY CustNum;
Results:
<div class="s-table-container"><table class="s-table">
<thead>
<th style="text-align: center;">CustNum</th>
<th style="text-align: center;">LicAddr1</th>
<th style="text-align: center;">Lic1</th>
<th style="text-align: center;">ExpiryDate1</th>
<th style="text-align: center;">LicAddr2</th>
<th style="text-align: center;">Lic2</th>
<th style="text-align: center;">ExpiryDate2</th>
<th style="text-align: center;">LicAddr3</th>
<th style="text-align: center;">Lic3</th>
<th style="text-align: center;">ExpiryDate3</th>
<td style="text-align: center;">123</td>
<td style="text-align: center;">Y32CA</td>
<td style="text-align: center;">2018-12-31</td>
<td style="text-align: center;">998</td>
<td style="text-align: center;">Y32CB</td>
<td style="text-align: center;">2020-12-31</td>
<td style="text-align: center;">568</td>
<td style="text-align: center;">Y32CC</td>
<td style="text-align: center;">2022-12-31</td>
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
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
Select Top X (Or Bottom) Percent for Numeric Values in MySQL
SQL Server Union - What Is the Default Order by Behaviour
Division of Integers Returns 0
Performance Issue in Using Select *
Function in SQL Server 2008 Similar to Greatest in MySQL
Convert Unixtime to Datetime SQL (Oracle)
SQL Server: Drop Table Cascade Equivalent
SQL Pivot and String Concatenation Aggregate
Does Postgres Support Nested or Autonomous Transactions
Comma Separated Values in a Database Field
How to Remove Redundant Namespace in Nested Query When Using for Xml Path
Why Is Null Not Equal to Null False
Preventing Adjacent/Overlapping Entries with Exclude in Postgresql
How to Import Text Files with the Same Name and Schema But Different Directories into Database