This article introduces the simple method of transposing columns and rows in SQL. It has certain reference value for everyone to solve problems. Friends who need it, follow the editor to learn together!
Problem description
Giveaway a ChatGPT account for a limited time..
How to simply switch columns and rows in SQL? Is there a simple transpose command?
How do I simply switch columns with rows in SQL? Is there any simple command to transpose?
That’s the result:
<code> Paul | John | Tim | Eric Red 1 5 1 3 Green 8 4 3 5 Blue 2 2 9 1 </code>
Enter this:
<code> Red | Green | Blue Paul 1 8 2 John 5 4 2 Tim 1 3 9 Eric 3 5 1 </code>
PIVOT seems too complex for this scenario.
PIVOT seems too complex for this scenario.
Recommended answers
There are multiple ways to convert this data. In your original post, you stated that PIVOT seemed too complex for this case, but using UNPIVOT and PIVOT function server.
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 these functions, you can use UNION ALL to copy to UNPIVOT and then use CASE statement to PIVOT >:
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 code> statement to PIVOT:
Create table:
<code><code>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);
</code></code>
Union All, Aggregate and CASE versions:
<code><code>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 </code></code>
See SQL Fiddle with Demo
UNION ALL Performs UNPIVOT of the data by converting columns Paul, John, Tim, Eric into separate rows. Then applies aggregation Function sum() and case statement to get new columns for each color.
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 versions:
The UNPIVOT and PIVOT functions in SQL Server make this conversion easier. If you know all the values you want to convert, you can hardcode them as Static version to get results:
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:
<code><code>select name, [Red], [Green], [Blue]
from
(
select color, name, value
from your table
unpivot
(
value for name in (Paul, John, Tim, Eric)
)unpiv
) src
pivot
(
sum(value)
for color in ([Red], [Green], [Blue])
)piv
</code></code>
See SQL Fiddle with Demo
The inner query with UNPIVOT performs the same function as UNION ALL. It takes the column list and converts it into rows, PIVOT Then perform the final conversion to columns.
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 (in your example Paul, John, Tim, Eric) and then an unknown number of colors to convert, you can use dynamic sql to generate the list Become UNPIVOT then PIVOT:
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:
<code><code>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 your table t
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'select name, ' + @colsPivot + '
from
(
select color, name, value
from your table
unpivot
(
value for name in (' + @colsUnpivot + ')
) unpiv
) src
pivot
(
sum(value)
for color in (' + @colsPivot + ')
)piv'
exec(@query)
</code></code>
See SQL Fiddle with Demo
The dynamic version queries the yourtable and sys.columns tables to generate a list of items for UNPIVOT and PIVOT. It is then added to The advantage of the dynamic version is that if you have a changing list of code>colors and/or names, this will generate the list at runtime.
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 produce the same results:
All three queries will produce the same result:
<code><code><code>| NAME | RED | GREEN | BLUE | -------------------------- | Eric | 3 | 5 | 1 | | John | 5 | 4 | 2 | | Paul | 1 | 8 | 2 | | Tim | 1 | 3 | 9 | </code></code></code>
That’s it for this article about a simple method to transpose columns and rows in SQL? We hope that the answers we recommend will be helpful to everyone, and we also hope that everyone will support IT House!