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 rows to columns - SQL2005
It can be done using INFORMATION_SCHEMA, UNPIVOT, CAST and EXEC. Here's a working solution.
CREATE TABLE #Data ([Status] int, [Wage] varchar(100), [Tax] decimal(10,3), BigText nvarchar(max))
INSERT #Data VALUES (7, '$12m', 123123.22, 'small island')
SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE
+ CASE WHEN CHARACTER_MAXIMUM_LENGTH > 0 THEN '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS nvarchar(10)) + ')' WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN '(max)' ELSE '' END
+ CASE WHEN DATA_TYPE IN ('numeric', 'decimal') AND NUMERIC_PRECISION > 0 THEN '(' + CAST(NUMERIC_PRECISION AS nvarchar(10))
+ CASE WHEN NUMERIC_SCALE > 0 THEN ',' + CAST(NUMERIC_SCALE AS NVARCHAR(10)) ELSE '' END
+ ')' ELSE '' END DATA_TYPE
INTO #Columns
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_NAME LIKE '#Data%'
ORDER BY C.ORDINAL_POSITION
DECLARE @selectList nvarchar(max), @columnNames nvarchar(max)
SELECT @selectList = ISNULL(@selectList + ',', '') + 'CAST(' + QUOTENAME(COLUMN_NAME) + ' AS nvarchar(max)) ' + QUOTENAME(COLUMN_NAME),
@columnNames = ISNULL(@columnNames + ',', '') + QUOTENAME(COLUMN_NAME)
FROM #Columns
DECLARE @unpivot nvarchar(max)
SET @unpivot = '
SELECT b.Column_Name, b.DataValue, c.Data_Type
FROM
(
SELECT ' + @selectList + ' FROM #Data
) a
UNPIVOT (DataValue FOR Column_Name IN (' + @columnNames + ')) b
JOIN #Columns c ON c.Column_Name = b.Column_Name
'
EXEC (@unpivot)
SQL Server - Transpose rows into columns
You also need a value field in your query for each id to aggregate on. Then you can do something like this
select [1234], [1235]
from
(
-- replace code below with your query, e.g. select id, value from table
select
id = 1234,
value = 1
union
select
id = 1235,
value = 2
) a
pivot
(
avg(value) for id in ([1234], [1235])
) as pvt
Transpose rows into columns in SQL without using aggregate functions like MAX, AVG etc
You can use max()
with characters/string. A simple old style pivot
will work for this:
select
RollNo
, Q1 = max(case when QuesBlock = 1 then RespVal else null end)
, Q2 = max(case when QuesBlock = 2 then RespVal else null end)
, Q3 = max(case when QuesBlock = 3 then RespVal else null end)
from MyDataTable
group by RollNo;
or with pivot()
like so:
select
RollNo
, Q1
, Q2
, Q3
from (select RollNo, QuesBlock='Q'+convert(varchar(2),QuesBlock), RespVal
from MyDataTable) as i
pivot (max(RespVal) for QuesBlock in (Q1,Q2,Q3)) as p;
or dynamically pivot()
like so:
declare @query nvarchar(max);
declare @cols nvarchar(max);
select @cols = stuff((select ','+quotename('Q'+convert(varchar(2),QuesBlock))
from MyDataTable as C
group by c.QuesBlock
order by c.QuesBlock
for xml path('')), 1, 1, '');
set @query = 'select RollNo, '+@cols+'
from(select RollNo, QuesBlock=''Q''+convert(varchar(2),QuesBlock), RespVal
from MyDataTable) as i
pivot
(
max(RespVal)
for QuesBlock in ('+@cols+')
) p';
exec sp_executesql @query;
test setup: http://rextester.com/TURW69000
all three return:
+---------+----+----+----+
| RollNo | Q1 | Q2 | Q3 |
+---------+----+----+----+
| mba0001 | A | B | D |
| mba0002 | C | A | B |
| mba0003 | B | C | A |
+---------+----+----+----+
Displaying Columns as Rows in SQL Server 2005
In order to get the result that you want you need to first UNPIVOT
the data and then PIVOT the
DatePeriod` Values.
The UNPIVOT will transform the multiple columns of Transactions
, Customers
and Visits
into multiple rows. The other answers are using a UNION ALL
to unpivot but SQL Server 2005 was the first year the UNPIVOT
function was supported.
The query to unpivot the data is:
select dateperiod,
col, value
from transactions
unpivot
(
value for col in (Transactions, Customers, Visits)
) u
See Demo. This transforms your current columns into multiple rows, so the data looks like the following:
| DATEPERIOD | COL | VALUE |
-------------------------------------
| Jan 2012 | Transactions | 100 |
| Jan 2012 | Customers | 50 |
| Jan 2012 | Visits | 150 |
| Feb 2012 | Transactions | 200 |
Now, since the data is in rows, you can apply the PIVOT
function to the DatePeriod
column:
select col, [Jan 2012], [Feb 2012], [Mar 2012]
from
(
select dateperiod,
t.col, value, c.SortOrder
from
(
select dateperiod,
col, value
from transactions
unpivot
(
value for col in (Transactions, Customers, Visits)
) u
) t
inner join
(
select 'Transactions' col, 1 SortOrder
union all
select 'Customers' col, 2 SortOrder
union all
select 'Visits' col, 3 SortOrder
) c
on t.col = c.col
) d
pivot
(
sum(value)
for dateperiod in ([Jan 2012], [Feb 2012], [Mar 2012])
) piv
order by SortOrder;
See SQL Fiddle with Demo.
If you have an unknown number of date period's then you will use dynamic SQL:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(dateperiod)
from transactions
group by dateperiod, PeriodNumberOverall
order by PeriodNumberOverall
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT col, ' + @cols + '
from
(
select dateperiod,
t.col, value, c.SortOrder
from
(
select dateperiod,
col, value
from transactions
unpivot
(
value for col in (Transactions, Customers, Visits)
) u
) t
inner join
(
select ''Transactions'' col, 1 SortOrder
union all
select ''Customers'' col, 2 SortOrder
union all
select ''Visits'' col, 3 SortOrder
) c
on t.col = c.col
) x
pivot
(
sum(value)
for dateperiod in (' + @cols + ')
) p
order by SortOrder'
execute(@query)
See SQL Fiddle with Demo. Both will give the result:
| COL | JAN 2012 | FEB 2012 | MAR 2012 |
-------------------------------------------------
| Transactions | 100 | 200 | 300 |
| Customers | 50 | 100 | 200 |
| Visits | 150 | 300 | 600 |
SQL server: convert rows to columns
You should take a look at PIVOT for switching rows with columns. This prevents a select statement for each month. Something like this:
DECLARE @salesTable TABLE
(
[month] INT,
sales INT
)
-- Note that I use SQL Server 2008 INSERT syntax here for inserting
-- multiple rows in one statement!
INSERT INTO @salesTable
VALUES (0, 2) ,(0, 2) ,(1, 2) ,(1, 2) ,(2, 2)
,(3, 2) ,(3, 2) ,(4, 2) ,(4, 2) ,(5, 2)
,(6, 2) ,(6, 2) ,(7, 2) ,(8, 2) ,(8, 2)
,(9, 2) ,(10, 2) ,(10, 2) ,(11, 2) ,(11, 2)
SELECT [0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11]
FROM
(
SELECT [month], sales
FROM @salesTable
) AS SourceTable
PIVOT
(
SUM(sales)
FOR [month] IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11])
) AS PivotTable
Transpose a set of rows as columns in SQL Server 2000
The example at http://jdixon.dotnetdevelopersjournal.com/pivot_table_data_in_sql_server_2000_and_2005.htm only works if you know in advance what the row values can be. For example, let's say you have an entity with custom attributes and the custom attributes are implemented as rows in a child table, where the child table is basically variable/value pairs, and those variable/value pairs are configurable.
color red
size big
city Chicago
I'm going to describe a technique that works. I've used it. I'm NOT promoting it, but it works.
To pivot the data where you don't know what the values can be in advance, create a temp table on the fly with no columns. Then use a cursor to loop through your rows, issuing a dynamically built "alter table" for each variable, so that in the end your temp table has the columns, color, size, city.
Then you insert one row in your temp table, update it via another cursor through the variable, value pairs, and then select it, usually joined with its parent entity, in effect making it seem like those custom variable/value pairs were like built-in columns in the original parent entity.
Transpose rows to columns based on ID column
you can use SQL Server pivot clause for this:
select
p.*
from Table1
pivot(
max([Field Selection])
for [Field Name] in ([Rating 1], [Rating 2], [Rating 3])
) as p
or you can pivot manually:
select
ID,
max(case when [Field Name] = 'Rating 1' then [Field Selection] end) as [Rating 1],
max(case when [Field Name] = 'Rating 2' then [Field Selection] end) as [Rating 2],
max(case when [Field Name] = 'Rating 3' then [Field Selection] end) as [Rating 3]
from Table1
group by ID
sql fiddle demo
Query Transposing certain rows into column names
Here's a solution which generates and runs the dynamic SQL with a PIVOT:
DECLARE @pivot_list AS VARCHAR(MAX)
--
;
WITH cols
AS ( SELECT DISTINCT
'INCIDENT IN ' + LEFT(UPPER(CONVERT(VARCHAR, [date], 107)),
3) + ' '
+ SUBSTRING(UPPER(CONVERT(VARCHAR, [date], 107)), 9, 4) AS col
FROM so926209_2
)
SELECT @pivot_list = COALESCE(@pivot_list + ', ', '') + '[' + col + ']'
FROM cols
--
DECLARE @template AS VARCHAR(MAX)
SET @template = 'WITH incidents AS (
SELECT [user_id],
incident_code,
''INCIDENT IN '' + LEFT(UPPER(CONVERT(VARCHAR, [date], 107)), 3)
+ '' '' + SUBSTRING(UPPER(CONVERT(VARCHAR, [date], 107)), 9, 4) AS col
FROM so926209_2
)
,results AS (
SELECT * FROM incidents PIVOT (MAX(incident_code) FOR col IN ({@pivot_list})) AS pvt
)
SELECT results.[user_id]
,so926209_1.[name]
,{@select_list}
FROM results INNER JOIN so926209_1 ON so926209_1.[user_id] = results.[user_id]
'
DECLARE @sql AS VARCHAR(MAX)
SET @sql = REPLACE(REPLACE(@template, '{@pivot_list}', @pivot_list), '{@select_list}', @pivot_list)
--PRINT @sql
EXEC (@sql)
Where so926209_1
, so926209_2
are your table 1 and table 2
Note that if you have multiple incidents in a month for the same person, your example doesn't show how you want that handled. This example only takes the last incident in the month.
Related Topics
Oracle Regexp_Substr | Fetch String Between Two Delimiters
SQL - Select Distinct Only on One Column
How to Check All Stored Procedure Is Ok in SQL Server
Performance Issue in Update Query
Delete Top-N' Rows from a Table with Some Sorting(Order by 'Column')
Mongodb and Postgresql Thoughts
Getting List of Table Comments in Postgresql
Postgresql - Using Subqueries with Alter Sequence Expressions
Ukrainian Character Change to Question Mark When Insert to Table
Oracle Delete Rows Matching on Multiple Values
Pivot with Dynamic Columns in Oracle
How to Check Type of Value in Postgres
Difference Between Stored Procedures and User Defined Functions
Percentage from Total Sum After Group by SQL Server