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'
Convert SQL query to SQL functions?
You can use a Scalar Function to achieve this.
But you have to use the CONCAT
function at the output, there is no other option to return multi select query in function.
You can also use Table Valued function to get the output in Table format.
CREATE Function dbo.Years_Days(@dateofbirth datetime, @currentdatetime datetime)
RETURNS VARCHAR(MAX) AS
BEGIN
Declare @years varchar(4)
Declare @days varchar(4)
set @currentdatetime = ISNULL(@currentdatetime,getdate());
select @years = datediff(year,@dateofbirth,@currentdatetime)
select @days = datediff(DAY,@dateofbirth,@currentdatetime)
RETURN @years + ' years,' + @days + ' days,'
END
SELECT dbo.Years_Days( '2020-01-25 ', NULL);
The type of function depends on the Return Data Type
If the returned datatype is a Table then it needs to be a Table-Valued Function.
Further, click below link:
Functions In SQL Server
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 |
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;
How to call a builtin function on every column in a table with bigquery without specifying column names?
Below is for BigQuery Standard SQL
#standardSQL
WITH table_two AS (
SELECT
CAST(REGEXP_EXTRACT(SPLIT(kv, ':')[OFFSET(0)], r'feat_(\d+)') AS INT64) AS feat_id,
CAST(SPLIT(kv, ':')[SAFE_OFFSET(1)] AS FLOAT64) AS feat_val,
label_bc, dontcare
FROM `project.dataset.table_one` t,
UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r'"feat_\d+":-?\d+.\d+')) kv
)
SELECT feat_id, CORR(label_bc, feat_val) AS feat_corr
FROM table_two
GROUP BY feat_id
You can test, play with above using sample/dummy data as in below example
#standardSQL
WITH table_one AS (
SELECT 0.123 feat_1, 0.645 feat_2, 0.656 feat_3, 1.0 label_bc, 'a' dontcare UNION ALL
SELECT 0.567, 0.456, -0.056, 0.0, 'b' UNION ALL
SELECT 0.243, 0.734, 0.754, 1.0, 'c' UNION ALL
SELECT 0.456, 0.888, -0.858, 0.0, 'i' UNION ALL
SELECT 0.645, 0.222, 0.252, 1.0, 'j' UNION ALL
SELECT 0.321, 0.123, -0.153, 0.0, 'c'
), table_two AS (
SELECT
CAST(REGEXP_EXTRACT(SPLIT(kv, ':')[OFFSET(0)], r'feat_(\d+)') AS INT64) AS feat_id,
CAST(SPLIT(kv, ':')[SAFE_OFFSET(1)] AS FLOAT64) AS feat_val,
label_bc, dontcare
FROM table_one t,
UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r'"feat_\d+":-?\d+.\d+')) kv
)
SELECT feat_id, CORR(label_bc, feat_val) AS feat_corr
FROM table_two
GROUP BY feat_id
with result
Row feat_id feat_corr
1 1 -0.30526201038849277
2 2 0.0818318512559385
3 3 0.838349444539397
How to deal with SQL column names that look like SQL keywords?
Wrap the column name in brackets like so, from
becomes [from].
select [from] from table;
It is also possible to use the following (useful when querying multiple tables):
select table.[from] from table;
Related Topics
Does Pl/SQL Have an Equivalent Stringtokenizer to Java'S
SQL Count to Include Zero Values
Oracle SQL Developer "String Literal Too Long" Error
Update an Excel Sheet Using Vba/Ado
Sql:Find Rows and Sort According to Number of Matching Columns
Returning the Value of Identity Column After Insertion in Oracle
Join Tables on Nearest Date in the Past, in MySQL
Listagg Query "Ora-00937: Not a Single-Group Group Function"
How to Specify Table Name as a String
Inserting Multiple Rows in Sybase Ase
Creating a SQL Table from a Xls (Excel) File
How to Make Lag() Ignore Nulls in SQL Server
How to Make a Stored Procedure Return a "Dataset" Using a Parameter I Pass
Parsing Openxml with Multiple Elements of the Same Name
How to Retrieve Rows Multiple Times in SQL Server