Dynamic Case List for Pivot


  
 

 https://social.msdn.microsoft.com/Forums/en-US/245f9c25-0975-4faa-bea4-bd9871a3c17b/build-dynamic-output-query-from-sql-based-on-parm-passed-it?forum=transactsql

 ---All tables have the same structure

CREATE TABLE [dbo].[F315-Line12](
	[Stamp_time] [datetime] NOT NULL,
	[Bead1Tst] [decimal](18, 0) NULL,
	[Bead2Tst] [decimal](18, 0) NULL,
	[Bead3Tst] [decimal](18, 0) NULL,
	[Speed] [int] NULL,
	[Volts] [int] NULL)

 

INSERT INTO  [F315-Line12]  VALUES ('2016-12-12 09:25:00.000',5 ,6,8,70,20)

Declare @tablename nvarchar(256)
DECLARE @Param INT =10
---output file called 315xx

--If @Param  = 5  
--Begin
--Set @tablename ='F315-Line5'
--End
If @Param  = 10  
Begin
Set @tablename ='F315-Line10'
End

--If @Param  = 11  
--Begin
--Set @tablename ='F315-Line11'
--End
If @Param  = 12  
Begin
Set @tablename ='F315-Line12'
End



Declare @sqlCase as NVarchar(4000) =null
Declare @sql as NVarchar(4000)=null
 
  
 --===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101), 
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1
WHere n<= @Param ) ---Control the total columns list

Select @sqlCase =  COALESCE(@sqlCase + ', ', '') 
+ ' CASE WHEN @Param=' + Cast(  n   as varchar(10))+ ' THEN '   
+  QUOTENAME(COLUMN_NAME)+' ELSE 0 END as ' + QUOTENAME(COLUMN_NAME  + Cast(n as varchar(10))  ) +char(10)+char(13)
FROM   [INFORMATION_SCHEMA].[COLUMNS]
cross apply (Select n From Nums) d (n)
WHERE TABLE_NAME= @tablename and COLUMN_NAME Like 'Bead%Tst'
order by n, COLUMN_NAME
print @sqlCase


Set @sql='Select  [Stamp_time], ' + @sqlCase + ' from  ' + QUOTENAME(@tablename)
 print @sql
 EXEC sp_executesql @sql, N'@Param int ' ,@Param=@Param ;
 
 

 
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