Dynamic Matrix (UNPIVOT and PIVOT)


  
 
create TABLE pivot_information  (
month_col VARCHAR(50),
[scrapRate] int,
[surfaceRepairRate] int,
[totalRepairRate] int ,
[totalProduction] int,
[totalSurfaceParts] int,
[totalScrapNumber] INT ,
[surfaceRepairingNumber] int ,
[allRepairingNumber] int

)

INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'jan', 6.58, 20.85,85.12,35651,4521,500,400,1000)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'feb', 6.58, 20.85,85.12,35652,4521,502,400,1002)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'march', 6.58, 20.85,85.12,35653,4521,503,400,1003)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'april', 6.58, 20.85,85.12,35651,4521,500,400,1000)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'may', 6.58, 20.85,85.12,35651,4521,500,400,1000)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'june', 6.58, 20.85,85.12,35651,4521,500,400,1000)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'july', 6.58, 20.85,85.12,35651,4521,500,400,1000)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'august', 6.58, 20.85,85.12,35651,4521,500,400,1000)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'september', 6.58, 20.85,85.12,35651,4521,500,400,1000)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'october', 6.58, 20.85,85.12,35651,4521,500,400,1000)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'november', 6.58, 20.85,85.12,35651,4521,500,400,1000)
INSERT   [dbo].pivot_information   (month_col,   [scrapRate], [surfaceRepairRate],[totalRepairRate],[totalProduction],[totalSurfaceParts],[totalScrapNumber],[surfaceRepairingNumber],[allRepairingNumber]) VALUES (N'december', 6.58, 20.85,85.12,35651,4521,500,400,1000)

 



Declare @sql1 as NVarchar(4000)
Declare @sql2 as NVarchar(4000)
Declare @Cols1 as NVarchar(4000)
Declare @Cols2 as NVarchar(4000)
 
Set @Cols1=null
 
Select @Cols1 =  COALESCE(@Cols1 + ', ', '') + '('+QUOTENAME(COLUMN_NAME,'''')+',' + QUOTENAME(COLUMN_NAME)+')'
FROM  [INFORMATION_SCHEMA].[COLUMNS]
WHERE TABLE_NAME='pivot_information' and COLUMN_NAME<>'month_col'

  --Print @Cols1

  Select @sql1=';with mycte as (Select
month_col, col,val
FROM pivot_information t
CROSS APPLY (Values ' + @Cols1 + ' )  d(col,val) )
SELECT   @Cols2  = STUFF( (SELECT  '','' + ''Max(CASE WHEN month_col='' + quotename(month_col,'''''''') + '' THEN val else null end ) as '' + quotename(month_col)  + char(10)+char(13)
                             FROM mycte
							 Group by month_col
							 order by Case 
							 when month_col=''Jan'' then 1
							  when month_col=''Feb'' then 2
							   when month_col=''March'' then 3
							    when month_col=''APril'' then 4
								 when month_col=''May'' then 5
								  when month_col=''June'' then 6
								   when month_col=''July'' then 7
								   when month_col=''August'' then 8
								   when month_col=''September'' then 9
								   when month_col=''October'' then 10
								    when month_col=''November'' then 11
									 when month_col=''December'' then 12
									 End							 
  FOR XML PATH(''''), TYPE).value(''.'', ''varchar(max)''), 1, 1, ''''); '
 
 

 EXEC sp_executesql @sql1,N'@Cols2 NVARCHAR(4000) output',@Cols2  output


  
 Set @sql2  =N';with mycte as (Select month_col, col,val
FROM pivot_information t
CROSS APPLY (Values ' + @Cols1 + ' )  d(col,val) )
Select  col, '+   @Cols2 + '  from  mycte   Group by col';

EXEC sp_executesql @sql2

 
drop table pivot_information


 

https://social.msdn.microsoft.com/Forums/en-US/9e915c1e-ec50-45c9-925b-5a2b89458cc8/conveting-rows-to-columns-vice-versa-in-sql-server?forum=transactsql

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s