December 5, 2013 at 10:59 am
hi
i have a table which i need to unpivot. It is always in 2 year ranges (months) so looks something like:
AccountId, StockId, 2013 - Jan, 2013 - feb, 2013 - mar...., 2014 - Jan, 2014 - feb, 2014 - mar....
The year can be any Year but will always be for the year selected and the following year, so 24 columns.
how can i unpivot this to look like:
Account Id....StockId..........Date...............Value
AC123..........STK123..........Jan - 2013.......1000
AC123..........STK123..........Feb - 2013.......1150
AC123..........STK123..........Mar - 2013.......2000
AC123..........STK123..........Jan - 2014.......1400
AC123..........STK123..........Feb - 2014.......900
AC123..........STK123..........Mar - 2014.......2100
thanks
December 5, 2013 at 11:22 am
If I assume that you could do this if you knew the names of the columns, the problem reduces to getting the names of the columns and then assembling them into the query string and executing it.
Column names can be extracted (in their correct order) from information_schema.columns. Try running this:
select table_name
,column_name
,ordinal_position
,data_type
from information_schema.columns
order by 1
,3
Now we have to resort to dynamic SQL... There may be another way but none comes to mind.
Put your various column names into local variables and then build your SQL string from them.
And finally,
Exec(@sql_string)
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 5, 2013 at 11:35 am
Thats just what I was after. I can create the dynamic column string from here. Thanks.
December 5, 2013 at 12:04 pm
Could you post DDL and sample data on the way of INSERT statements so we can work directly on it?
December 6, 2013 at 3:28 am
ok here's the completed solution, thanks to all who helped....
-- destroy staging table if exists
IF OBJECT_ID('tbl_TestTable', 'U') IS NOT NULL
begin
DROP TABLE tbl_TestTable
end
-- create the temp table
create table tbl_TestTable(
AccountId varchar(6),
StockId varchar(7),
[Jan - 2013] int,
[Feb - 2013] int,
[Mar - 2013] int,
[Apr - 2013] int,
[May - 2013] int,
[Jun - 2013] int,
[Jul - 2013] int,
[Aug - 2013] int,
[Sep - 2013] int,
[Oct - 2013] int,
[Nov - 2013] int,
[Dec - 2013] int,
[Jan - 2014] int,
[Feb - 2014] int,
[Mar - 2014] int,
[Apr - 2014] int,
[May - 2014] int,
[Jun - 2014] int,
[Jul - 2014] int,
[Aug - 2014] int,
[Sep - 2014] int,
[Oct - 2014] int,
[Nov - 2014] int,
[Dec - 2014] int
)
-- insert some values
insert into tbl_TestTable
select 'AC1234', 'STK1111', 4284, 4946, 4773, 1812, 1121, 3977, 2453, 1229, 4448, 4844, 3460, 3299, 3715, 1082, 4335, 1024, 3363, 4565, 2096, 4482, 4282, 4539, 3638, 2061 union
select 'AC1234', 'STK1112', 3543, 1854, 2986, 1249, 3105, 1799, 3425, 1620, 1209, 2872, 1737, 1886, 2114, 4249, 4924, 4936, 3008, 4339, 4043, 3151, 4302, 2721, 2677, 4177 union
select 'AC1234', 'STK1113', 4184, 4376, 1101, 4746, 1117, 1846, 4379, 2953, 1956, 3053, 3749, 3753, 1924, 1698, 1311, 2688, 4144, 4174, 4481, 4660, 1326, 2990, 3830, 4573 union
select 'AC1234', 'STK1114', 3881, 1936, 4198, 2471, 3815, 4555, 3502, 4989, 4880, 2727, 3440, 2434, 3362, 1552, 1188, 1221, 3431, 4134, 1526, 2839, 3856, 4577, 4226, 4769 union
select 'AC1234', 'STK1115', 1444, 1679, 1569, 2278, 2120, 2308, 3436, 1134, 4062, 2198, 2233, 3644, 1784, 1701, 2965, 2433, 2798, 3474, 3025, 2333, 2170, 3255, 3259, 2904 union
select 'AC4321', 'STK1111', 2359, 2618, 2941, 4887, 3570, 1946, 2350, 2322, 2472, 2813, 2791, 3035, 2886, 1849, 4087, 3182, 1103, 2213, 2003, 4447, 1167, 2249, 2064, 1370 union
select 'AC4321', 'STK1112', 2684, 2542, 1095, 1584, 2153, 4382, 2599, 1515, 2640, 3967, 2325, 1661, 3847, 2485, 4534, 2696, 3549, 4411, 1651, 1146, 2570, 3716, 3877, 3361 union
select 'AC4321', 'STK1113', 1663, 1538, 1377, 4045, 3626, 1488, 2471, 1447, 4196, 2438, 1361, 1259, 2255, 1744, 3991, 4205, 3717, 1700, 3254, 3190, 1218, 1651, 2111, 2768 union
select 'AC4321', 'STK1114', 4989, 3589, 2524, 3803, 1684, 4717, 3292, 3051, 3050, 4871, 2815, 4892, 2717, 1647, 2355, 4823, 3194, 1197, 3814, 2178, 4276, 4943, 1638, 1254 union
select 'AC4321', 'STK1115', 1226, 1261, 1900, 4854, 1699, 4810, 4957, 4280, 2799, 3956, 3352, 4196, 1723, 2208, 1560, 4367, 3610, 3876, 3745, 1892, 1224, 1449, 1356, 4300
select * from tbl_TestTable
-- create the dynamic date columns string
declare @cols AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(column_name)
FROM (
-- get columns as a list (THANKS FOR THIS Phil Parkin)
selecttable_name, column_name, ordinal_position, data_type
frominformation_schema.columns
wheretable_name = 'tbl_TestTable'
andordinal_position not in (1,2)
) cols
ORDER BY ordinal_position
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,''
)
select @cols as ColString
-- finally do the unpivot using dynamic sql
declare @sqlStr nvarchar(max)
set @sqlStr = N'
select AccountId, StockId, Col as Date, Quantity
from(
select AccountId, StockId, ' + @cols + '
from tbl_TestTable
) as cp
unpivot
(
Quantity for Col in (' + @cols + ')
) as up'
exec sp_executesql @sqlStr
-- kill the temp table
drop table tbl_TestTable
December 6, 2013 at 3:41 am
spin (12/6/2013)
ok here's the completed solution, thanks to all who helped....
Nice work, well done.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply