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 |
How to convert or transpose rows to columns in SQL without using pivot?
Try STRING_AGG instead of any_value
:
select id, string_agg(if(source_name = 'cp', value, null)) as cp,
string_agg(if(source_name = 'hi', value, null)) as hi,
string_agg(if(source_name = 'li', value, null)) as li
string_agg(if(source_name = 'mi', value, null)) as mi
from table_name group by id
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 table in presto?
You can try to use UNNEST function.
SELECT v.*
FROM T
CROSS JOIN UNNEST(ARRAY[
ROW('A', A),
ROW('B', B),
ROW('C', C),
ROW('D', D),
ROW('E', E)
]) AS v(Reason, Percentage);
another way is using UNION ALL
SELECT 'A' Reason , A Percentage FROM T
UNION ALL
SELECT 'B' Reason , B Percentage FROM T
UNION ALL
SELECT 'C' Reason , C Percentage FROM T
//....
SQL transpose and concatenate
You would seem to want string_agg()
:
select name, string_agg(no, ',') within group (order by no)
from piv1
group by name;
Azure SQL Transpose a table with all rows
I tried my luck. Could you check below query if it works,
What I did different to your query is making the result of UNPIVOT
distinct by adding row_number
to it so that the later PIVOT
will take max of each row and display separately. My bad if the explanation doesn't makes sense to you.
select [1],[2],[3],[4],[5],[6]
from
( select link_id,head_values,
row_number() over (partition by link_id order by link_id) rn
from
( select link_id
,cast(comp1 as varchar(255)) as comp1
,cast(comp2 as varchar(255)) as comp2
,cast(comp3 as varchar(255)) as comp3
,cast(comp4 as varchar(255)) as comp4
from [dbo].[test_excel_poc_head]
) as cp
unpivot
(
head_values for head_value in (comp1,comp2,comp3,comp4)
) as up
) temp_results
pivot
(
max(head_values)
for link_id in ([1],[2],[3],[4],[5],[6])
) as pivottable;
db<>fiddle for your reference.
How to transpose columns?
As both comentsalready told you you need to aggreate the hole CASE WHEN
to have an aggregation function
SELECT SKU, CITY,
sum(case
when MOV=49 then Total_Imp end) as Type_Imp_Caducidad ,
sum(case
when MOV=48 then Total_Imp end) as Type_Imp_Daño ,
SUM(case
when MOV=47 then Total_Imp end) as Type_Imp_Robo
from #movimientos
where Id_Num_SKU=11466978
group by SKY, CITY
How to transpose a single column in BigQuery without specifying all the values in the column?
Hope this is helpful: You said you don't know what values in col_D
in advance, so it will be needed to create a pivot query dynamically using EXECUTE IMMEDIATE
.
CREATE TEMP TABLE sample AS
SELECT * FROM UNNEST([STRUCT
(1 AS col_A, 'ab' AS col_B, 'cd' AS col_C, 'val1' AS col_D),
(2, 'cd', 'ef', 'val2'),
(2, 'cd', 'ef', 'val3'),
(3, 'ab', 'bc', 'val4')
]);
EXECUTE IMMEDIATE FORMAT("""
SELECT * FROM sample PIVOT (COUNT(col_A) FOR col_D IN ('%s'));
""", ARRAY_TO_STRING(ARRAY(SELECT DISTINCT col_D FROM sample ORDER BY 1), "','"));
output:
If you want a exact same result in your question, try this instead of previous query.
EXECUTE IMMEDIATE FORMAT("""
SELECT *
FROM (SELECT col_A AS col, * FROM sample)
PIVOT (COUNT(col) FOR col_D IN ('%s'))
ORDER BY 1;
""", ARRAY_TO_STRING(ARRAY(SELECT DISTINCT col_D FROM sample ORDER BY 1), "','"));
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
When No 'Order By' Is Specified, What Order Does a Query Choose For Your Record Set
Fastest Way to Perform Nested Bulk Inserts With Scope_Identity() Usage
Unrecognized Name: Employees At [9:8]
Mixing Ansi 1992 Joins and Commas in a Query
How to Restore a Dump File from MySQLdump
"Case" Statement Within "Where" Clause in SQL Server 2008
SQL Query - Using Order by in Union
SQL Server Check Case-Sensitivity
SQL Server - in Clause With a Declared Variable
How to Get the Difference in Years from Two Different Dates
Get Day of Week in SQL Server 2005/2008
Combining Union and Limit Operations in MySQL Query