SQL Server : Columns to Rows
You can use the UNPIVOT function to convert the columns into rows:
select id, entityId,
indicatorname,
indicatorvalue
from yourtable
unpivot
(
indicatorvalue
for indicatorname in (Indicator1, Indicator2, Indicator3)
) unpiv;
Note, the datatypes of the columns you are unpivoting must be the same so you might have to convert the datatypes prior to applying the unpivot.
You could also use CROSS APPLY
with UNION ALL to convert the columns:
select id, entityid,
indicatorname,
indicatorvalue
from yourtable
cross apply
(
select 'Indicator1', Indicator1 union all
select 'Indicator2', Indicator2 union all
select 'Indicator3', Indicator3 union all
select 'Indicator4', Indicator4
) c (indicatorname, indicatorvalue);
Depending on your version of SQL Server you could even use CROSS APPLY with the VALUES clause:
select id, entityid,
indicatorname,
indicatorvalue
from yourtable
cross apply
(
values
('Indicator1', Indicator1),
('Indicator2', Indicator2),
('Indicator3', Indicator3),
('Indicator4', Indicator4)
) c (indicatorname, indicatorvalue);
Finally, if you have 150 columns to unpivot and you don't want to hard-code the entire query, then you could generate the sql statement using dynamic SQL:
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @colsUnpivot
= stuff((select ','+quotename(C.column_name)
from information_schema.columns as C
where C.table_name = 'yourtable' and
C.column_name like 'Indicator%'
for xml path('')), 1, 1, '')
set @query
= 'select id, entityId,
indicatorname,
indicatorvalue
from yourtable
unpivot
(
indicatorvalue
for indicatorname in ('+ @colsunpivot +')
) u'
exec sp_executesql @query;
Converting Columns into rows with their respective data in sql server
declare @T table (ScripName varchar(50), ScripCode varchar(50), Price int)
insert into @T values ('20 MICRONS', '533022', 39)
select
'ScripName' as ColName,
ScripName as ColValue
from @T
union all
select
'ScripCode' as ColName,
ScripCode as ColValue
from @T
union all
select
'Price' as ColName,
cast(Price as varchar(50)) as ColValue
from @T
SQL Query Convert columns to rows
You could use UNPIVOT to get the desired result also:
SELECT
*
FROM
(
SELECT
SUM(cases) AS total_cases,
SUM(deaths) AS total_deaths
FROM
myschema.metrics
) UNPIVOT ( value
FOR category
IN ( total_cases,
total_deaths ) );
The output of the above will be:
Category Value
Total_cases 1234
Total_deaths 123
SQL query to convert columns into rows
Your Data
DECLARE @TABLE TABLE
(sip_RECno INT,user1 VARCHAR(10),user2 VARCHAR(10)
,user3 VARCHAR(10),user4 VARCHAR(10))
INSERT INTO @TABLE VALUES
(1,'ram','ravi','sam','raj')
Query
;WITH CTE
AS
(
SELECT * FROM (
SELECT user1 ,user2 ,user3 , user4 FROM @TABLE) T
UNPIVOT ( Value FOR N IN (user1 ,user2 ,user3 , user4))P
)
SELECT Value AS Users
FROM CTE
Result Set
╔═══════╗
║ Users ║
╠═══════╣
║ ram ║
║ ravi ║
║ sam ║
║ raj ║
╚═══════╝
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 |
Convert a set of columns into rows in ORACLE SQL
Like you already know to convert rows
to columns
PIVOT
is required , similarly to convert columns
to rows
we need UNPIVOT
.
As there is no specific table with column names specified I have used WITH
clause to create a temporary table to demonstrate. Here is link to know more about the clauses used in UNPIVOT
for your reference.
with table1
as
(
select 'Inbound' department, 50 hour0, 44 hour1, 29 hour2, 47 hour3, 17 hour4 from dual
union all
select 'Outbound', 6, 4, 10, 24, 39 from dual
union all
select 'Returns', 3, 1, 39, 43, 35 from dual
)
select *
from table1 t1
unpivot (value for hour in (hour0 as '0',hour1 as '1',hour2 as '2',hour3 as '3',hour4 as '4'))
SQL: Convert multiple columns to rows
try
select *
from yourTable
unpivot (
Value
for NewCol in (Value1, Value2, Value3,Value4, Value5)
) up
How to convert columns to rows in sql server
Use UNPIVOT. Try something like:
SELECT ID, Page, Line, City, Value
FROM SourceTable
UNPIVOT
(Value FOR City IN
(C01, C02, C03)
)AS unpvt;
Where 'SourceTable' is your source table name. (Note: I can't test this at the moment, so it may not be exactly right.)
Full details here: http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx
Convert columns to rows in Spark SQL
It's the opposite of pivot - it's called unpivot.
In Spark, unpivoting is implemented using stack
function.
Using PySpark, this is what you could do if you didn't have many columns:
from pyspark.sql import SparkSession, functions as F
spark = SparkSession.builder.getOrCreate()
df = spark.createDataFrame(
[(101, 3, 520, 2001),
(102, 29, 530, 2020)],
['ID', 'Value1', 'Value2', 'Value40'])
df = df.select(
"ID",
F.expr("stack(3, Value1, 'Value1', Value2, 'Value2', Value40, 'Value40') as (ValueVv, ValueDesc)")
)
From your example I see that you may have lots of columns. In this case you may use something like this:
cols_to_unpivot = [f"`{c}`, \'{c}\'" for c in df.columns if c != 'ID']
stack_string = ", ".join(cols_to_unpivot)
df = df.select(
"ID",
F.expr(f"stack({len(cols_to_unpivot)}, {stack_string}) as (ValueVv, ValueDesc)")
)
For the example data both versions return
+---+-------+---------+
| ID|ValueVv|ValueDesc|
+---+-------+---------+
|101| 3| Value1|
|101| 520| Value2|
|101| 2001| Value40|
|102| 29| Value1|
|102| 530| Value2|
|102| 2020| Value40|
+---+-------+---------+
Related Topics
How to Replace a Substring of a String Before a Specific Character
Query to Check Overlapping Ranges in SQL Server
Is Too Many Left Joins a Code Smell
Sql Server Select Datetime Without Seconds
Sql Best Practices - Ok to Rely on Auto Increment Field to Sort Rows Chronologically
I Am Trying to Copy a File, But Getting Error Message
Sql How to Remove Duplicates Within Select Query
Get The Second Highest Value with Standard Sql
Sqlite: Alias Column Name Can't Contains a Dot "."
Sql Server Stored Procedure and Execute in VB.NET
How to Bulk Update with SQL Server
Selecting The Same Row Multiple Times
Replacing Certain Character in Email Addresses with '*' in an SQL Query
Select All Projects That Have Matching Tags
How to Drop a Column with Object Dependencies in SQL Server 2008
Convert from Uniqueidentifier to Bigint and Back