* 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
-------------------------------------
-------------------------------------