Just done by me based on the previous article: SQLServer: Rows to columns
Use table
CREATE TABLE [dbo].[JCDAILYM2MPRICE] (
[PRICEID] int NOT NULL,
[VALIDDATE] date NULL,
[COMMODITYID] varchar(30) NULL,
[PRICE] [DMONEY] NULL,
[CURRENCYID] char(3) NULL,
[SHIPMONTHNUMBER] int NULL,
CONSTRAINT [PK_JCDAILYM2MPRICE] PRIMARY KEY CLUSTERED ([PRICEID]));
1.
Run the basic query:
select commodityid, price, shipmonthnumber
from jcdailym2mprice
where validdate='2012-08-31' and commodityid='CR 10'
Get the result:
| commodityid | price | shipmonthnumber |
| CR 10 | 2615 | 201209 |
| CR 10 | 2615 | 201210 |
2.
--hard code as research 1
select commodityid,
case shipmonthnumber when 201209 then isnull(price, 0) end as '201209',
case shipmonthnumber when 201210 then isnull(price, 0) end as '201210'
from jcdailym2mprice
where validdate='2012-08-31' and commodityid='CR 10'
Get the result:
| commodityid | 201209 | 201210 |
| CR 10 | 2615 | |
| CR 10 | | 2615 |
3.
--hard code as research 2
select commodityid, sum([201209]) as '201209', sum([201210]) as '201210'
from (select commodityid,
case shipmonthnumber when 201209 then isnull(price, 0) end as '201209',
case shipmonthnumber when 201210 then isnull(price, 0) end as '201210'
from jcdailym2mprice
where validdate='2012-08-31' and commodityid='CR 10') a
group by commodityid
Get the result
| commodityid | 201209 | 201210 |
| CR 10 | 2615 | 2615 |
4.
--use cursor to dynamic
declare cur cursor for
select monthnumber, '['+convert(varchar(6), monthnumber)+']' from jcshipmonthsmapping
where monthnumber>convert(varchar(6), dateadd(month, -2, getdate()), 112)
and monthnumber<convert(varchar(6), dateadd(month, 11, getdate()), 112)
declare @yearmonth Integer, @columnname varchar(max)
declare @max varchar(max), @select varchar(max), @sql nvarchar(max)
set @max=''
set @select ='select commodityid '
open cur
fetch next from cur into @yearmonth,@columnname
while @@fetch_status=0
begin
set @max=@max + ', max(' + @columnname + ') as ' + @columnname
set @select=@select + ', case when shipmonthnumber=' + convert(varchar(6), @yearmonth) + ' then price end as '+ @columnname
fetch next from cur into @yearmonth,@columnname
end
close cur
deallocate cur
set @sql = ' select commodityid ' + @max
+' from (' + @select + ' from jcdailym2mprice where validdate=''2012-08-31'') a group by commodityid'
--print @sql
exec sp_executesql @sql
Get the result:
| commodityid | 201209 | 201210 | 201211 | 201212 | 201301 | 201302 | 201303 | 201304 | 201305 | 201306 | 201307 | 201308 |
| Brown Crepe 3X | 2650 | | | | | | | | | | | |
| CR 10 | 2615 | 2615 | | | | | | | | | | |
| CR 20 | 2610 | 2610 | 2610 | 2610 | 2620 | 2620 | 2620 | 2630 | 2630 | | | |
| CSR 10 | 2350 | | | | | | | | | | | |
| CSR L | 2430 | 2435 | | | | | | | | | | |
| SIR 20 S/T BSTN | 2513.2 | | | | | | | | | | | |
| SIR 20 S/W | 2513.2 | 2529.8 | 2535.3 | 2540.8 | | | | | | | | |
| SIR 20 UG SW | 2513.2 | 2529.8 | 2535.3 | 2540.8 | | | | | | | | |
| SKIM BLOCK | 2300 | | | | | | | | | | | |
5.
--pivot basic
select commodityid, [201209], [201210]
from (
select commodityid, price, shipmonthnumber from jcdailym2mprice where validdate='2012-08-31' and commodityid='CR 10'
) a
pivot ( sum(price) for shipmonthnumber in ([201209], [201210])) as pvt
Get the result
commodityid | 201209 | 201210 |
CR 10 | 2615 | 2615 |
6.
--pivot dynamic
declare cur cursor for
select '['+convert(varchar(6), monthnumber)+']' from jcshipmonthsmapping
where monthnumber>convert(varchar(6), dateadd(month, -2, getdate()), 112)
and monthnumber<convert(varchar(6), dateadd(month, 11, getdate()), 112)
declare @columns varchar(max), @subjects varchar(max), @sql nvarchar(max)
set @subjects = '[-1]'
open cur
fetch next from cur into @columns
while @@fetch_status=0
begin
set @subjects = @subjects + ', ' + @columns
fetch next from cur into @columns
end
close cur
deallocate cur
set @sql = ' select commodityid,' + @subjects
+ ' from ( select commodityid, price, shipmonthnumber from jcdailym2mprice where validdate=''2012-08-31'' and commodityid=''CR 10'') a'
+ ' pivot(sum(price) for shipmonthnumber in ('+@subjects+')) as pvt'
--print @sql
exec sp_executesql @sql
Get the result
| commodityid | -1 | 201209 | 201210 | 201211 | 201212 | 201301 | 201302 | 201303 | 201304 | 201305 | 201306 | 201307 |
| CR 10 | | 2615 | 2615 | | | | | | | | | |