Convert Rows to Columns in SQL

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;

MS SQL Convert rows to columns with Pivot

You coud try this:

With data (STOCKCODE, QTY, AGE) as (
select 'AIRFIL01', 3,1 union all
select 'AIRFIL01', 8,2  union all
select 'AIRFIL05', 4,1  union all
select 'AIRFIL05', 14,2  union all
select 'AIRPRE01', 4,1  union all
select 'AIRPRE01', 24,2  union all
select 'AIRSUS01', 1,2  union all
select 'ALARM01', 1,1  union all
select 'ALARM01', 6,2  union all
select 'ALARM01', 7,10  union all
select 'ALARM05', 2,1  union all
select 'ANTROL01', 5,2 
)
SELECT * from (
Select STOCKCODE, QTY, CONCAT('Age_' , AGE) comment  from data
)t
PIVOT
(
SUM(QTY)
FOR comment IN ( [Age_1],[Age_2],[Age_3],[Age_4],[Age_5],[Age_6],[Age_7],[Age_8],[Age_9],[Age_10])
) p

How to transpose ROW to Column in DB2

Short answer is you can't.

There's nothing in Db2 for IBM i that will do this with SELECT * and a dynamic table.

Long answer, you can build a stored procedure or user defined table function that dynamically builds and executes an old school statement that looks like so:

with firstRow as 
  (select F1, F2, F3 from table fetch first row only)
select F1
from firstRow
UNION ALL 
select F2
from firstRow
UNION ALL
select F3
from firstRow;

Alternately, since you're on v7.4, you could build and execute a dynamic statement that CONCAT the fields into string list and then use the SPLIT() table function to deconstruct the the list into rows.

Lastly, you might be able to build and execute a dynamic statement that uses the JSON functions to build a JSON array which could then be deconstructed into rows with the JSON_TABLE() function.

But as emphasized, in all cases you'll need to know column and table names for the actual SELECT. Thus the need to dynamically build the statement.

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 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

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

I need to convert rows to column for calendar app

This is just a simple pivot, or (what I and many others prefer) conditional aggregation:

SELECT wk,
       MAX(CASE day WHEN 'mon' THEN subj END) AS mon,
       MAX(CASE day WHEN 'tue' THEN subj END) AS tue,
       MAX(CASE day WHEN 'wed' THEN subj END) AS wed
FROM (VALUES(1,202225,'mon','subj1'),
            (2,202225,'mon','subj2'),
            (3,202225,'mon','subj3'),
            (1,202225,'tue','subj4'),
            (2,202225,'tue','subj5'),
            (1,202225,'wed','subj6'),
            (2,202225,'wed','subj7'),
            (1,202226,'mon','subj8'),
            (2,202226,'mon','subj9'),
            (1,202226,'tue','subj10'),
            (1,202226,'wed','subj11'),
            (2,202226,'wed','subj12'),
            (3,202226,'wed','subj13'))V(rn,wk,day,subj)
GROUP BY rn,
         wk
ORDER BY wk,
         rn;

db<>fiddle

---

Related Topics