Unpivot dynamic table columns into key value rows
You can't pivot or unpivot in one query without knowing the columns.
What you can do, assuming you have privileges, is query sys.columns to get the field names of your source table then build an unpivot query dynamically.
--Source table
create table MyTable (
id int,
Field1 nvarchar(10),
Field2 nvarchar(10),
Field3 nvarchar(10)
);
insert into MyTable (id, Field1, Field2, Field3) values ( 1, 'aaa', 'bbb', 'ccc' );
insert into MyTable (id, Field1, Field2, Field3) values ( 2, 'eee', 'fff', 'ggg' );
insert into MyTable (id, Field1, Field2, Field3) values ( 3, 'hhh', 'iii', 'jjj' );
--key/value table
create table MyValuesTable (
id int,
[field] sysname,
[value] nvarchar(10)
);
declare @columnString nvarchar(max)
--This recursive CTE examines the source table's columns excluding
--the 'id' column explicitly and builds a string of column names
--like so: '[Field1], [Field2], [Field3]'.
;with columnNames as (
select column_id, name
from sys.columns
where object_id = object_id('MyTable','U')
and name <> 'id'
),
columnString (id, string) as (
select
2, cast('' as nvarchar(max))
union all
select
b.id + 1, b.string + case when b.string = '' then '' else ', ' end + '[' + a.name + ']'
from
columnNames a
join columnString b on b.id = a.column_id
)
select top 1 @columnString = string from columnString order by id desc
--Now I build a query around the column names which unpivots the source and inserts into the key/value table.
declare @sql nvarchar(max)
set @sql = '
insert MyValuestable
select id, field, value
from
(select * from MyTable) b
unpivot
(value for field in (' + @columnString + ')) as unpvt'
--Query's ready to run.
exec (@sql)
select * from MyValuesTable
In case you're getting your source data from a stored procedure, you can use OPENROWSET to get the data into a table, then examine that table's column names. This link shows how to do that part.
https://stackoverflow.com/a/1228165/300242
Final note: If you use a temporary table, remember that you get the column names from tempdb.sys.columns like so:
select column_id, name
from tempdb.sys.columns
where object_id = object_id('tempdb..#MyTable','U')
Dynamic Unpivot and split Columns SQL Server 2012
You just need to split the columns after do the UNPIVOT something like this:
WITH Unpivoted
AS
(
SELECT region, lob, columns, value
FROM Regions
UNPIVOT
(
columns
FOR value IN([GWP 2013] , [GWP 2014] ,
[LR 2013] , [LR 2014] ,
[GWP 2015], [LR 2015])
) AS u
)
SELECT
region,
lob,
columns,
CAST(CASE WHEN value LIKE 'GWP%' THEN REPLACE(value,'GWP ', '')
WHEN value LIKE 'LR%' THEN REPLACE(value,'LR ', '')
END AS INT) AS Year,
CASE WHEN value LIKE 'GWP%' THEN 'GWP'
WHEN value LIKE 'LR%' THEN 'LR'
END AS Metrics
FROM Unpivoted;
And then of course you should do it dynamically to avoid listing the columns manually and do it dynamically:
DECLARE @cols AS NVARCHAR(MAX);
DECLARE @query AS NVARCHAR(MAX);
select @cols = STUFF((SELECT distinct ',' +
QUOTENAME(column_name)
FROM information_schema.columns
WHERE table_name = 'Regions'
AND COLUMN_NAME <> 'Region'
AND COLUMN_NAME <> 'LOB'
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
, 1, 1, '');
SELECT @query = 'WITH Unpivoted
AS
(
SELECT region, lob, columns, value
FROM Regions
UNPIVOT
(
columns
FOR value IN('+ @cols + ')
) AS u
)
SELECT
region,
lob,
columns,
CAST(CASE WHEN value LIKE ''GWP%'' THEN REPLACE(value,''GWP '', '''')
WHEN value LIKE ''LR%'' THEN REPLACE(value,''LR '', '''')
END AS INT) AS Year,
CASE WHEN value LIKE ''GWP%'' THEN ''GWP''
WHEN value LIKE ''LR%'' THEN ''LR''
END AS Metrics
FROM Unpivoted';
EXECUTE(@query);
This should work fine assuming that:
- All the columns
[GWP 2013] , [GWP 2014] , [LR 2013] , [LR 2014] , [GWP 2015], [LR 2015], ... etcare in the same format (GWP or LR then space then the year, and All of the columns are of the same data type
intor decimal, if the data types are not the same you should cast all of them into one data type before doing theunpivototherwise you will got an error.SQL Fiddle Demo
This will give you:
| region | lob | columns | Year | Metrics |
|--------|---------|----------|------|---------|
| North | Workers | 38902.5 | 2013 | GWP |
| North | Workers | 37972404 | 2014 | GWP |
| North | Workers | 70 | 2015 | GWP |
| North | Workers | 89 | 2013 | LR |
| North | Workers | 82 | 2014 | LR |
| North | Workers | 80 | 2015 | LR |
Update:
I used FOR XML PATH('') .. to concatenate all the list of values in one string, it is a work around in SQL Server to do that. The value of @cols will be the string: [GWP 2013], [GWP 2014], ....
If your fields data type are different, you have to do the cast of all the columns that will be unpivoted in the anchor query before doing the UNPVOT like this:
SELECT @query = 'WITH Unpivoted
AS
(
SELECT region, lob, columns, value
FROM
(
SELECT
region,
lob,
CAST([GWP 2013] AS DECIMAL(10,2)) AS [GWP 2013],
CAST([GWP 2014] AS DECIMAL(10,2)) AS [GWP 2014],
... etc
FROM Regions
) AS t
UNPIVOT
(
columns
FOR value IN('+ @cols + ')
) AS u
)
SELECT
region,
lob,
columns,
CAST(CASE WHEN value LIKE ''GWP%'' THEN REPLACE(value,''GWP '', '''')
WHEN value LIKE ''LR%'' THEN REPLACE(value,''LR '', '''')
END AS INT) AS Year,
CASE WHEN value LIKE ''GWP%'' THEN ''GWP''
WHEN value LIKE ''LR%'' THEN ''LR''
END AS Metrics
FROM Unpivoted';
If you found it hard to write the cast for all the columns manually, you can generate it dynamically and append it instead, for example:
DECLARE @colsCasted AS NVARCHAR(MAX);
select @colsCasted = STUFF((SELECT distinct ',' +
'CAST(' + QUOTENAME(column_name) + 'AS DECIMAL(10,2)) AS ' + QUOTENAME(column_name)
FROM information_schema.columns
WHERE table_name = 'Regions'
AND COLUMN_NAME <> 'Region'
AND COLUMN_NAME <> 'LOB'
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
, 1, 1, '');
Then in the dynamic query append that value to it:
SELECT @query = 'WITH Unpivoted
AS
(
SELECT region, lob, columns, value
FROM
(
SELECT region, lob,
' + @colsCasted + '
FROM Regions
) AS t
UNPIVOT
(
columns
FOR value IN('+ @cols + ')
) AS u
)
SELECT
region,
lob,
columns,
CAST(CASE WHEN value LIKE ''GWP%'' THEN REPLACE(value,''GWP '', '''')
WHEN value LIKE ''LR%'' THEN REPLACE(value,''LR '', '''')
END AS INT) AS Year,
CASE WHEN value LIKE ''GWP%'' THEN ''GWP''
WHEN value LIKE ''LR%'' THEN ''LR''
END AS Metrics
FROM Unpivoted';
EXECUTE(@query);
How Can I Use unpivot dynamically?
I think this is what you need:
CREATE TABLE Category
(
[Profile] varchar(10),
Batch int,
Doubles int,
Feeder int,
[Image] int,
Hardware int
)
INSERT Category VALUES
('A', '1', '1', '0', '2', '1'),
('B', '1', '2', '3', '0', '4'),
('A', '1', '5', '1', '6', '1'),
('A', '1', '2', '1', '2', '7')
DECLARE @query nvarchar(MAX);
SELECT @query = COALESCE(
@query+char(10)+'UNION ALL'+char(10)+'SELECT '+QUOTENAME(name,'''')+' EventCategory, SUM('+QUOTENAME(name)+') Occurence FROM Category',
'SELECT '+QUOTENAME(name, '''')+' EventCategory, SUM('+QUOTENAME(name)+') Occurence FROM Category')
FROM sys.columns C WHERE [object_id]=OBJECT_ID('Category')
AND name<>'Profile'
EXEC (@query)
SQL Unpivot With Every Column
Please try this:
DECLARE @sql AS NVARCHAR(MAX)
DECLARE @cols1 AS NVARCHAR(MAX)
DECLARE @cols2 AS NVARCHAR(MAX)
SELECT @cols1= ISNULL(@cols1 + ',','') + QUOTENAME(name)
FROM (select c.name from sys.tables t
inner join sys.columns c on c.object_id = t.object_id
where t.name = 'globals'
) cols1
SELECT @cols2= ISNULL(@cols2 + ',cast(','cast(') + QUOTENAME(name) + ' as nvarchar(max))'+ QUOTENAME(name)
FROM (select c.name from sys.tables t
inner join sys.columns c on c.object_id = t.object_id
where t.name = 'globals'
) cols2
SET @sql =
N'SELECT g.property, g.value
FROM (SELECT ' + @cols2 + '
FROM globals) Person
UNPIVOT (value
FOR property IN (' + @cols1 +')) AS g; '
EXEC sp_executesql @sql
It's not beautiful and can certainly be improved, but it should work.
Using UNPIVOT in constructed String Query Dynamic
Try the following dynamic-pivot:
--drop table if exists unpivottest
create table unpivotTest (data1 int, data2 int, com char(1), fr char(1))
insert into unpivotTest
select 1, 2, 'a' , 'd' union all
select 3, 4, 'b', 'a'
select * from unpivotTest
declare @colsunpivot as nvarchar(max),
@query as nvarchar(max)
select @colsunpivot = stuff((select ','+ quotename(c.name)
from sys.columns c
where c.object_id = object_id('dbo.unpivottest') and c.name not like '%data%'
for xml path('')), 1, 1, '')
set @query
= 'select data1, data2, name, website
from unpivottest
-- you cannot do the ordering here
unpivot
(
name
for website in ('+ @colsunpivot +')
) u
where data1 = 1 -- here you can use your where clause
order by data1' -- here you can do the ordering by any col
--print @query
exec sp_executesql @query;
Check a working demo here.
Unpivot columns from another table
As far as I know (please correct me if I'm wrong) it is not possible to use dynamic columnnames without the use of a dynamic query, which is executed with for example exec.
Take a look at the following question unpivot with dynamic columns plus column names
Related Topics
Is of a Type That Is Invalid for Use as a Key Column in an Index
Postgres Constraint for Unique Datetime Range
Composite Primary Keys:Good or Bad
SQL Server Select Where Any Column Contains 'X'
Get Execution Time of Postgresql Query
Representing Ecommerce Products and Variations Cleanly in the Database
Merge Row Values into a CSV (A.K.A Group_Concat for SQL Server)
Sql: How to Get the Value of an Attribute in Xml Datatype
T-Sql: How to Use Parameters in Dynamic SQL
How to Specify Date Literal When Writing SQL Query from SQL Server That Is Linked to Oracle
Get Start and End Date from Week Number SQL Server
Postgresql Does Not Accept Column Alias in Where Clause
Optimize Groupwise Maximum Query
Transposing an SQL Result So That One Column Goes Onto Multiple Columns