Cari Blog Ini

13 Desember 2013

How To Create Pivot Table with Flexible Column

Apply To :
* Microsoft SQL Server 2008 R2

Problem :

* Want to create pivot table with flexible column 







Solution : 

1. Example Table "Temp_Currency_1" and "Temp_Exchange_1"

----------------------------------------------------------
----------------------------------------------------------
-----CODE START

CREATE TABLE [dbo].[Temp_Currency_1]
(
[Currency] [varchar](5) NOT NULL,
[Description] [varchar](50) NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[Temp_Exchange_1]
(
[Currency] [varchar](5) NOT NULL,
[Effective] [smalldatetime] NOT NULL,
[Rate] [float] NULL
) ON [PRIMARY]

-----CODE END
----------------------------------------------------------
----------------------------------------------------------





2. Create a sampling data,


---------------------------------------
---------------------------------------
-----CODE START

insert into Temp_Currency_1 values ('AUD','Australian Dollar')

insert into Temp_Currency_1 values ('EUR','Euro Eropa')
insert into Temp_Currency_1 values ('USD','US Dollar')


insert into Temp_Exchange_1 values ( 'AUD','9/1/2013','9766')
insert into Temp_Exchange_1 values ( 'AUD','10/1/2013','10798')
insert into Temp_Exchange_1 values ( 'AUD','11/1/2013','10675')
insert into Temp_Exchange_1 values ( 'EUR','9/1/2013','14470')
insert into Temp_Exchange_1 values ( 'EUR','10/1/2013','15671')
insert into Temp_Exchange_1 values ( 'EUR','11/1/2013','15428')
insert into Temp_Exchange_1 values ( 'USD','9/1/2013','10924')
insert into Temp_Exchange_1 values ( 'USD','10/1/2013','11613')
insert into Temp_Exchange_1 values ( 'USD','11/1/2013','11234')

-----CODE END
------------------------------------------
------------------------------------------





3. To see the data,

--------------------------------------
--------------------------------------
-----CODE START

select * from Temp_Currency_1
select * from Temp_Exchange_1

-----CODE END
-------------------------------------
-------------------------------------





4. Type the code below,

-------------------------------------------
-------------------------------------------
-----CODE START

create procedure [dbo].[sp_Exchange_All_1]
@User varchar(100)
as

BEGIN
Declare @TableName_1  varchar(255),
@sql varchar(8000)

set @sql=''  
set @TableName_1=''
set @TableName_1='_'+@User+'_Temp_Exchange_All'


if EXISTS(select * from INFORMATION_SCHEMA.tables where table_name = @TableName_1 )
begin exec('drop table '+@TableName_1) end


SELECT @sql=@sql+Currency+',' from
(
select distinct Currency from Temp_Currency_1
) a

set @sql= left(@sql,len(@sql)-1)

print @sql

exec ('

select * into '+@TableName_1+' from
(
select * from
(
select Currency,Effective, max(Rate) [Rate]
from Temp_Exchange_1
group by Currency,Effective

) _table
pivot
(
max(Rate)
for Currency in ( '+@sql+' )
) as PivotTableA

) an

select * from '+@TableName_1+' order by Effective desc

')

END


-----CODE END
---------------------------------------------
---------------------------------------------







5. Try to run the procedure, example for User_1

-------------------------------------
-------------------------------------
-----CODE START

exec [sp_Exchange_All_1] 'User_1'

-----CODE END
-------------------------------------
-------------------------------------




6. Optional, to remove the procedure

------------------------------------
------------------------------------
-----CODE START

drop table dbo.Temp_Currency_1
drop table dbo.Temp_Exchange_1
drop procedure dbo.sp_Exchange_All_1

-----CODE END
-------------------------------------
-------------------------------------