Simple way to transpose columns and rows in SQL?
There are several ways that you can transform this data. In your original post, you stated that PIVOT
seems too complex for this scenario, but it can be applied very easily using both the UNPIVOT
and PIVOT
functions in SQL Server.
However, if you do not have access to those functions this can be replicated using UNION ALL
to UNPIVOT
and then an aggregate function with a CASE
statement to PIVOT
:
Create Table:
CREATE TABLE yourTable([color] varchar(5), [Paul] int, [John] int, [Tim] int, [Eric] int);
INSERT INTO yourTable
([color], [Paul], [John], [Tim], [Eric])
VALUES
('Red', 1, 5, 1, 3),
('Green', 8, 4, 3, 5),
('Blue', 2, 2, 9, 1);
Union All, Aggregate and CASE Version:
select name,
sum(case when color = 'Red' then value else 0 end) Red,
sum(case when color = 'Green' then value else 0 end) Green,
sum(case when color = 'Blue' then value else 0 end) Blue
from
(
select color, Paul value, 'Paul' name
from yourTable
union all
select color, John value, 'John' name
from yourTable
union all
select color, Tim value, 'Tim' name
from yourTable
union all
select color, Eric value, 'Eric' name
from yourTable
) src
group by name
See SQL Fiddle with Demo
The UNION ALL
performs the UNPIVOT
of the data by transforming the columns Paul, John, Tim, Eric
into separate rows. Then you apply the aggregate function sum()
with the case
statement to get the new columns for each color
.
Unpivot and Pivot Static Version:
Both the UNPIVOT
and PIVOT
functions in SQL server make this transformation much easier. If you know all of the values that you want to transform, you can hard-code them into a static version to get the result:
select name, [Red], [Green], [Blue]
from
(
select color, name, value
from yourtable
unpivot
(
value for name in (Paul, John, Tim, Eric)
) unpiv
) src
pivot
(
sum(value)
for color in ([Red], [Green], [Blue])
) piv
See SQL Fiddle with Demo
The inner query with the UNPIVOT
performs the same function as the UNION ALL
. It takes the list of columns and turns it into rows, the PIVOT
then performs the final transformation into columns.
Dynamic Pivot Version:
If you have an unknown number of columns (Paul, John, Tim, Eric
in your example) and then an unknown number of colors to transform you can use dynamic sql to generate the list to UNPIVOT
and then PIVOT
:
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX)
select @colsUnpivot = stuff((select ','+quotename(C.name)
from sys.columns as C
where C.object_id = object_id('yourtable') and
C.name <> 'color'
for xml path('')), 1, 1, '')
select @colsPivot = STUFF((SELECT ','
+ quotename(color)
from yourtable t
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'select name, '+@colsPivot+'
from
(
select color, name, value
from yourtable
unpivot
(
value for name in ('+@colsUnpivot+')
) unpiv
) src
pivot
(
sum(value)
for color in ('+@colsPivot+')
) piv'
exec(@query)
See SQL Fiddle with Demo
The dynamic version queries both yourtable
and then the sys.columns
table to generate the list of items to UNPIVOT
and PIVOT
. This is then added to a query string to be executed. The plus of the dynamic version is if you have a changing list of colors
and/or names
this will generate the list at run-time.
All three queries will produce the same result:
| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric | 3 | 5 | 1 |
| John | 5 | 4 | 2 |
| Paul | 1 | 8 | 2 |
| Tim | 1 | 3 | 9 |
Transpose row to column in SQL Server
I recommend using cross apply
to unpivot and then aggregation:
select machine,
max(case when type = 'duration' then val end) as duration,
max(case when type = 'counter' then val end) as counter
from t cross apply
(values ('PKG_HA_01_ON', PKG_HA_01_ON),
('PKG_HA_03_ON', PKG_HA_03_ON),
('PKG_HA_04_ON', PKG_HA_04_ON),
('PKG_HA_05_ON', PKG_HA_05_ON),
('PKG_HA_06_ON', PKG_HA_06_ON),
('PKG_HA_09_ON', PKG_HA_09_ON)
) v(machine, val)
group by machine;
I much, much prefer this over pivot
/unpivot
. Why? APPLY
implements a lateral join, which is a powerful construct in SQL (and part of the SQL standard albeit with slightly different syntax). Unpivoting is a nice introduction to this feature.
The pivoting functions are bespoke functions and not part of the standard. They also do not generalize in any way, being designed for a single purpose.
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
Pivot/Transpose rows into column and convert NULLs into 0's
Two options. The first will use coalesce()
to eliminate the null values. The second will create a a unique set of intersections via a CROSS JOIN
and a UNION ALL
(brute force)
Example
Declare @YourTable Table ([Number] varchar(50),[TYPE] varchar(50),[Acct] varchar(50),[Total] varchar(50))
Insert Into @YourTable Values
('1X2','GGG',111,100)
,('1X2','GGG',222,200)
Select Number
,Type
,[111] = coalesce( [111] ,0)
,[222] = coalesce( [222] ,0)
,[333] = coalesce( [333] ,0)
From @YourTable
PIVOT (MAX(Total)
FOR ACCT in ([111],[222],[333])) pvt
Second Option:
You may notice I supplied the Accts
in the last CROSS JOIN
because 333
was not in the scope of the sample data. If it will exist, you could use the Select Distinct Acct
like we used in Cross Apply 1 and 2
Select *
From ( Select * from @YourTable
Union All
Select Number,Type,Acct,0
From ( Select distinct Number from @YourTable ) A
Cross Join (Select distinct Type from @YourTable) B
Cross Join (
Select * from (values ('111' )
,('222' )
,('333' )
)v(Acct)
) C
) src
PIVOT (MAX(Total)
FOR ACCT in ([111],[222],[333])) pvt
Both Results would be
SQL Transpose row to columns
You need to enumerate the values to pivot them:
select id,
max(case when seqnum = 1 then club end) as club_1,
max(case when seqnum = 2 then club end) as club_2,
max(case when seqnum = 3 then club end) as club_3
from (select b.*,
row_number() over (partition by id order by club) as seqnum
from b
) b
group by id;
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.
Related Topics
Does SQLite3 Not Support Foreign Key Constraints
Is There an Alternative to Top in MySQL
Search Text in Stored Procedure in SQL Server
How to Find Which Tables Reference a Given Table in Oracle SQL Developer
Sqlite Database Default Time Value 'Now'
What's the Best Way to Select the Minimum Value from Several Columns
Calculating Number of Full Months Between Two Dates in SQL
How to Capitalize the First Letter of Each Word in a String in SQL Server
How Many Rows in a Database Are Too Many
Different Current_Timestamp and Sysdate in Oracle
Accessing SQL Database in Excel-Vba
Oracle: What Does '(+)' Do in a Where Clause
How to List Files Inside a Folder with SQL Server
SQL Runs Fast in Ssms Slow in ASP.NET
SQL How to Make Null Values Come Last When Sorting Ascending