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: Real Transpose
I am unsure why you think you cannot accomplish this with an UNPIVOT
and a PIVOT
:
select [1], [2], [3], [4], [5]
from
(
select *
from
(
select col1, col2, col3,
row_number() over(order by col1) rn
from yourtable
) x
unpivot
(
val for col in (col1, col2, col3)
) u
) x1
pivot
(
max(val)
for rn in ([1], [2], [3], [4], [5])
) p
See SQL Fiddle with Demo. This could also be performed dynamically if needed.
Edit, if the column order needs to be kept, then you can use something like this, which applies the row_number()
without using a order by
on one of the columns in your table (here is an article about using non-deterministic row numbers):
select [1], [2], [3], [4], [5]
from
(
select *
from
(
select col1, col2, col3,
row_number()
over(order by (select 1)) rn
from yourtable
) x
unpivot
(
val for col in (col1, col2, col3)
) u
) x1
pivot
(
max(val)
for rn in ([1], [2], [3], [4], [5])
) p;
See SQL Fiddle with Demo
transposed with one select sql
You can use dense_rank()
in a subquery to enumerate the plateno
(or plateno
/desc
combination) and then use conditional aggregation for those columns:
SELECT t1.licence_no,
MAX(CASE WHEN seqnum = 1 THEN t1.plateno END) as plateno_1,
MAX(CASE WHEN seqnum = 1 THEN t1.desc END) as desc_1,
MAX(CASE WHEN seqnum = 2 THEN t1.plateno END) as plateno_2,
MAX(CASE WHEN seqnum = 2 THEN t1.desc END) as desc_2,
MAX(CASE WHEN axle_no = 1 THEN axle_dist ELSE NULL END) AS axle_dist_1,
MAX(CASE WHEN axle_no = 2 THEN axle_dist ELSE NULL END) AS axle_dist_2,
MAX(CASE WHEN axle_no = 3 THEN axle_dist ELSE NULL END) AS axle_dist_3,
MAX(CASE WHEN axle_no = 4 THEN axle_dist ELSE NULL END) AS axle_dist_4,
MAX(CASE WHEN axle_no = 5 THEN axle_dist ELSE NULL END) AS axle_dist_5
from (select1 t1.*,
dense_rank() over (partition by license_no order by plateno) as seqnum
from table1 t1
) t1
group t1.licence_no;
SQL transpose full table
In order to transpose the data into the result that you want, you will need to use both the UNPIVOT
and the PIVOT
functions.
The UNPIVOT
function takes the A
and B
columns and converts the results into rows. Then you will use the PIVOT
function to transform the day
values into columns:
select *
from
(
select day, col, value
from yourtable
unpivot
(
value
for col in (A, B)
) unpiv
) src
pivot
(
max(value)
for day in (Mon, Tue, Wed, Thu, Fri)
) piv
See SQL Fiddle with Demo.
If you are using SQL Server 2008+, then you can use CROSS APPLY
with VALUES
to unpivot the data. You code would be changed to the following:
select *
from
(
select day, col, value
from yourtable
cross apply
(
values ('A', A),('B', B)
) c (col, value)
) src
pivot
(
max(value)
for day in (Mon, Tue, Wed, Thu, Fri)
) piv
See SQL Fiddle with Demo.
Edit #1, applying your current query into the above solution you will use something similar to this:
select *
from
(
select LEFT(datename(dw,datetime),3) as DateWeek,
col,
value
from DataTable
cross apply
(
values ('A', ACalls), ('B', BCalls)
) c (col, value)
) src
pivot
(
sum(value)
for dateweek in (Mon, Tue, Wed, Thu, Fri)
) piv
SQL - Transpose one column of data into a row
You can use PIVOT
for this, e.g.:
;WITH src AS
(
SELECT ClientGUID, DxCode,
rn = ROW_NUMBER() OVER (PARTITION BY ClientGUID ORDER BY @@SPID)
FROM dbo.ClientDxCodes
-- WHERE ClientGUID = 12345
)
SELECT ClientGUID, DxCode1 = [1], DxCode2 = [2], DxCode3 = [3],
DxCode4 = [4], DxCode5 = [5], DxCode6 = [6], DxCode7 = [7],
DxCode8 = [8], DxCode9 = [9], DxCode10 = [10]
FROM src
PIVOT
(
MAX(DxCode)
FOR rn IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])
) AS p;
Output:
ClientGUID | DxCode1 | DxCode2 | DxCode3 | DxCode4 | DxCode5 | DxCode6 | DxCode7 | DxCode8 | DxCode9 | DxCode10 |
---|---|---|---|---|---|---|---|---|---|---|
12345 | 50.8 | 62.5 | 42.1 | null | null | null | null | null | null | null |
transpose rows to columns in sql
I have tried this code. Please check and let me know if it works
I know that it doesnt look so good. Also not sure how it will be performance wise.
--Can have more columns like A,B,...
DECLARE @tbl TABLE
(
TOTAL INT,
CHARGE FLOAT,
PAYMENT FLOAT,
MONTHYEAR VARCHAR(50)
)
--Test data
INSERT INTO @tbl SELECT 661, 157832.24, 82967.80, 'Oct2013'
INSERT INTO @tbl SELECT 612, 95030.52, 17824.28, 'Nov2013'
INSERT INTO @tbl SELECT 584 ,90256.35, 16732.91, 'Dec2013'
--Can be a physical table
CREATE TABLE #FinalTbl
(
DATA VARCHAR(100)
)
--inserted hardcode records in data column. To add it dynamically you would need to loop through information_schema.columns
--SELECT *
--FROM information_schema.columns
--WHERE table_name = 'tbl_name'
INSERT INTO #FinalTbl
VALUES ('TOTAL')
INSERT INTO #FinalTbl
VALUES ('CHARGE')
INSERT INTO #FinalTbl
VALUES ('PAYMENT')
DECLARE @StartCount INT, @TotalCount INT, @Query VARCHAR(5000), @TOTAL INT,@CHARGE FLOAT,@PAYMENT FLOAT,@MONTHYEAR VARCHAR(50)
SELECT @TotalCount = COUNT(*) FROM @tbl;
SET @StartCount = 1;
WHILE(@StartCount <= @TotalCount)
BEGIN
SELECT @TOTAL = TOTAL,
@CHARGE = CHARGE,
@PAYMENT = PAYMENT,
@MONTHYEAR = MONTHYEAR
FROM
(SELECT ROW_NUMBER() over(ORDER BY MONTHYEAR) AS ROWNUM, * FROM @tbl) as tbl
WHERE ROWNUM = @StartCount
SELECT @Query = 'ALTER TABLE #FinalTbl ADD ' + @MONTHYEAR + ' VARCHAR(1000)'
EXEC (@Query)
SELECT @Query = 'UPDATE #FinalTbl SET ' + @MONTHYEAR + ' = ''' + CONVERT(VARCHAR(50), @TOTAL) + ''' WHERE DATA = ''TOTAL'''
EXEC (@Query)
SELECT @Query = 'UPDATE #FinalTbl SET ' + @MONTHYEAR + ' = ''' + CONVERT(VARCHAR(50), @CHARGE) + ''' WHERE DATA = ''CHARGE'''
EXEC (@Query)
SELECT @Query = 'UPDATE #FinalTbl SET ' + @MONTHYEAR + ' = ''' + CONVERT(VARCHAR(50), @PAYMENT) + ''' WHERE DATA = ''PAYMENT'''
EXEC (@Query)
SELECT @StartCount = @StartCount + 1
END
SELECT * FROM #FinalTbl
DROP TABLE #FinalTbl
Hope this helps
SQL How to transpose table from row to column
I am not using exact names of columns but you can try this,
SELECT GOOD2,
NAME,
MAX(CASE WHEN [TYPE] = 'BF' THEN VALUE END) BQTY,
MAX(CASE WHEN [TYPE] = 'RC' THEN VALUE END) BQTY,
MAX(CASE WHEN [TYPE] = 'ISU' THEN VALUE END) BQTY,
MAX(CASE WHEN [TYPE] = 'CF' THEN VALUE END) BQTY
FROM TABLE1
GROUP BY
GOOD2,
NAME
Related Topics
Invoking a Large Set of SQL from a Rails 4 Application
Referencing Current Row in Filter Clause of Window Function
Join a Count Query on Generate_Series() and Retrieve Null Values as '0'
How to Create Temp Table with Select * into Temptable from Cte Query
Job Queue as SQL Table with Multiple Consumers (Postgresql)
SQL Recursive Query That Gets All Ancestors of an Item
Does the Order of Fields in a Where Clause Affect Performance in MySQL
How to Decrypt Stored Procedure in SQL Server 2008
Rails 3 Activerecord Query Using Both SQL in and SQL or Operators
Adding 'Go' Statements to Entity Framework Migrations
Why Is Rand() Not Producing Random Numbers
SQL Convert Week Number to Date (Dd/Mm)
How to Find Out What Foreign Key Constraint References a Table in SQL Server
In SQL Server, When Should You Use Go and When Should You Use Semi-Colon ;