Convert Rows to columns using 'Pivot' in mssql when columns are string data type
You can use a PIVOT to perform this operation. When doing the PIVOT you can do it one of two ways, with a Static Pivot that you will code the rows to transform or a Dynamic Pivot which will create the list of columns at run-time:
Static Pivot (see SQL Fiddle with a Demo):
SELECT *
FROM
(
select empid, wagecode, amount
from t1
) x
pivot
(
sum(amount)
for wagecode in ([basic], [TA], [DA])
) p
Dynamic Pivot:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(wagecode)
FROM t1
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT empid, ' + @cols + ' from
(
select empid, wagecode, amount
from t1
) x
pivot
(
sum(amount)
for wagecode in (' + @cols + ')
) p '
execute(@query)
Both of these will give you the same results
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 Rows to columns using 'Pivot' in SQL Server
If you are using SQL Server 2005+, then you can use the PIVOT function to transform the data from rows into columns.
It sounds like you will need to use dynamic sql if the weeks are unknown but it is easier to see the correct code using a hard-coded version initially.
First up, here are some quick table definitions and data for use:
CREATE TABLE yt
(
[Store] int,
[Week] int,
[xCount] int
);
INSERT INTO yt
(
[Store],
[Week], [xCount]
)
VALUES
(102, 1, 96),
(101, 1, 138),
(105, 1, 37),
(109, 1, 59),
(101, 2, 282),
(102, 2, 212),
(105, 2, 78),
(109, 2, 97),
(105, 3, 60),
(102, 3, 123),
(101, 3, 220),
(109, 3, 87);
If your values are known, then you will hard-code the query:
select *
from
(
select store, week, xCount
from yt
) src
pivot
(
sum(xcount)
for week in ([1], [2], [3])
) piv;
See SQL Demo
Then if you need to generate the week number dynamically, your code will be:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(Week)
from yt
group by Week
order by Week
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT store,' + @cols + ' from
(
select store, week, xCount
from yt
) x
pivot
(
sum(xCount)
for week in (' + @cols + ')
) p '
execute(@query);
See SQL Demo.
The dynamic version, generates the list of week numbers that should be converted to columns. Both give the same result:
| STORE | 1 | 2 | 3 |
---------------------------
| 101 | 138 | 282 | 220 |
| 102 | 96 | 212 | 123 |
| 105 | 37 | 78 | 60 |
| 109 | 59 | 97 | 87 |
SQL Server - Pivot Convert rows to columns (with additional row data)
I think conditional aggregation does what you want:
select id, type, color, date,
max(case when country_code = 'US' then cost end) as us,
max(case when country_code = 'EU' then cost end) as eu,
max(case when country_code = 'RU' then cost end) as ru,
max(case when country_code = 'AP' then cost end) as AP
from t
group by id, type, color, date;
Get ROWS as COLUMNS (SQL Server dynamic PIVOT query)
What you are trying to do is called a PIVOT. There are two ways to do this, either with a Static Pivot or a Dynamic Pivot.
Static Pivot - is where you will hard-code the values of the rows to transform to columns (See SQL Fiddle with Demo):
select ws_id,
start_date,
end_date,
IsNull([100.00], 0) [100.00],
IsNull([50.00], 0) [50.00],
IsNull([20.00], 0) [20.00],
IsNull([10.00], 0) [10.00],
IsNull([5.00], 0) [5.00],
IsNull([1.00], 0) [1.00]
from
(
select ws.ws_id,
ws.start_date,
ws.end_date,
cd.name,
cbw.qty
from workshift ws
left join currency_by_workshift cbw
on ws.ws_id = cbw.ws_id
left join currency_denom cd
on cbw.cd_id = cd.cd_id
) x
pivot
(
sum(qty)
for name in ([100.00], [50.00], [20.00], [10.00], [5.00], [1.00])
) p
Dynamic pivot is where the columns are determined at run-time (see SQL Fiddle with Demo):
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@colsPivot AS NVARCHAR(MAX)
select @colsPivot =
STUFF((SELECT ', IsNull(' + QUOTENAME(rtrim(name)) +', 0) as ['+ rtrim(name)+']'
from currency_denom
GROUP BY name
ORDER BY cast(name as decimal(10, 2)) desc
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
select @cols = STUFF((SELECT distinct ', ' + QUOTENAME(name)
from currency_denom
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'SELECT ws_id, start_date, end_date,' + @colsPivot + ' from
(
select ws.ws_id,
ws.start_date,
ws.end_date,
cd.name,
cbw.qty
from workshift ws
left join currency_by_workshift cbw
on ws.ws_id = cbw.ws_id
left join currency_denom cd
on cbw.cd_id = cd.cd_id
) x
pivot
(
sum(qty)
for name in (' + @cols + ')
) p '
execute(@query)
Both versions will produce the same results.
Convert row value in to column in SQL server (PIVOT)
Try this one:
SELECT *
FROM (SELECT Item, attribute, value FROM MyTable) AS t
PIVOT
(
MAX(value)
FOR attribute IN([Quality], [Color])
) AS p;
Output:
╔═══════╦═════════╦═══════╗
║ ITEM ║ QUALITY ║ COLOR ║
╠═══════╬═════════╬═══════╣
║ item1 ║ A ║ Red ║
║ item2 ║ B ║ Black ║
╚═══════╩═════════╩═══════╝
See this SQLFiddle
You can also use this dynamic query if you don't know the specific value of attribute:
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(attribute)
from MyTable
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT Item,' + @cols + '
from
(
Select Item, attribute , value
from MyTable
) dta
pivot
(
MAX(Value)
for attribute in (' + @cols + ')
) pvt '
execute(@query);
See this SQLFiddle
How to use string type column in SQL pivot
If your dbms is sql-server you can try to use SUM condition aggregate function in a CTE
then use CAST with coalesce to make it.
;WITH CTE AS(
SELECT Year,Name,
SUM(CASE WHEN Bonus LIKE '%[0-9]%' THEN CAST(Bonus AS DECIMAL) ELSE 0 END) total,
COUNT(CASE WHEN Bonus = 'No Bonus' THEN 1 END) cnt
FROM T
GROUP BY Year,Name
)
SELECT Name,
coalesce(MAX(CASE WHEN Year = 2011 THEN CAST(total AS VARCHAR(50)) END),'No Bonus') '2011',
coalesce(MAX(CASE WHEN Year = 2012 THEN CAST(total AS VARCHAR(50)) END),'No Bonus') '2012'
FROM CTE
GROUP BY Name
sqlfiddle
If you want to create columns dynamically you can try to use dynamic PIVOT.
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
;WITH CTE AS(
SELECT Year,Name,
SUM(CASE WHEN Bonus LIKE '%[0-9]%' THEN CAST(Bonus AS DECIMAL) ELSE 0 END) total,
COUNT(CASE WHEN Bonus = 'No Bonus' THEN 1 END) cnt
FROM T
GROUP BY Year,Name
)
SELECT @cols = STUFF((SELECT distinct ',coalesce(MAX(CASE WHEN cnt > 0 and Year = ' + cast(Year as varchar(5)) + ' THEN ''No Bonus'' WHEN Year = ' + cast(Year as varchar(5)) + ' and cnt = 0 THEN CAST(total AS VARCHAR(50)) END),''0'')' + QUOTENAME(Year)
FROM CTE c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = '
;WITH CTE AS(
SELECT Year,Name,
SUM(CASE WHEN Bonus LIKE ''%[0-9]%'' THEN CAST(Bonus AS DECIMAL) ELSE 0 END) total,
COUNT(CASE WHEN Bonus = ''No Bonus'' THEN 1 END) cnt
FROM T
GROUP BY Year,Name
)
SELECT Name, ' + @cols + '
from CTE
GROUP BY Name'
exec(@query)
sqlfiddle
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 |
Related Topics
Difference Between Information_Schema VS Sys Tables in SQL Server
How to Find the User That Has Both a Cat and a Dog
Xml Query() Works, Value() Requires Singleton Found Xdt:Untypedatomic
SQL - Pivot Table and Group by Not Working
Referencing a Calculated Column in the Where Clause SQL
Postgresql Insert If Not Exists
Using Merge in SQL Server 2012 to Insert/Update Data
Insert Record to SQL Table with Identity Column
Self Join to Get Employee Manager Name
How Does One Escape an Apostrophe in Db2 SQL
Powershell SQL Select Output to Variable
Using Guid in SQLite Select Where Guid Is Stored in the SQLite Db as Binaries
Detect SQL Island Over Multiple Parameters and Conditions
How to Know How Many Rows Will Be Affected Before Running a Query in Microsoft SQL Server 2008