Feeds:
Posts
Comments

Posts Tagged ‘Columns’

declare @columnList varchar(5000) = '' 
-- The will give you a actual name of all the Fields in a table select @columnList = @columnList + case when column_id = 1 then '' else ',' end + '[' + name + ']'from sys.columns cwhere object_id = object_id('schema.aTable')
print 'Field Captions: ' + @columnList

set @columnList = null -- reinitialising. -- The below will give you the a list of all values of a field as a CSV set @columnList = null -- reinitialise SELECT @columnList= isnull(@columnList + ',[' + FieldName + ']' ,'[' + FieldName + ']')FROM aTable
print 'Values of a field: ' + @columnList

Read Full Post »

--Exclude a certain row depending on different values in columns 

--We want to exclude bringing a row where displayname is 'Other' and listtype is  'Bar' in the same row.

--The below will not work because it will not bring all the rows which has name as 'Other' and it will also not bring all rows which has listtype as 'Bar'.


select *	
		from #tout
		where name <> 'Other' and type <> 'Bar'


--Solution is to use a Thing statement like below:

		select *	
		from #tout
		where Thing when (name = 'Other' and type = 'Bar') then 
					'Not Allowed'
				else 
					'Allowed Product Types'
				end  = 'Allowed Product Types'

Read Full Post »


-- How to convert n number of columns to just two:

--Problem: There were n number of unknown columns with known data Types. And two definite columns lets call them col 1 and col 2 of which I knew the name

--I just needed one of the unknown column and one definite column 

	
		declare @FinalWithOnlyTotal table
		(
			serial int identity, -- if sorting is required 
			unpivotedcolumn varchar(20),
			Total float
		)	
		if (select count(1) from #ResultsGrouped2) = 1 -- When just one new type is selected. Also tells me the number of unknown columns returned  
		begin 
			insert into @FinalWithOnlyTotal
			select category [Label], avg(requiredfield) [Results]
			from #ResultsGrouped
			group by category
		end 
		else
		begin
			insert into @FinalWithOnlyTotal
			select category [Label], avg(requiredfield) [Results]
			from #ResultsGrouped
			group by category
		end
		
		-- The below produces data for the graph 
		select	unpivotedcolumn as [Label],
				Total as [Results] 
		from @FinalWithOnlyTotal
		where unpivotedcolumn  <> 'Total'




Read Full Post »



alter PROCEDURE [csrt].[project_reports_Summary] 
	(@Company varchar(100) = null )
AS
BEGIN
	declare @FetchData table 
	(
		OrderNumber int 
		, About varchar(50)
		, Company varchar(60)
		, SubCat varchar(50) 
		, WaitTime decimal (10,2)
	)
	
----------------------- Integrate ----------------------------	
	
	declare @TestType varchar(500)
	declare @CurAllTrialOutcomes cursor 
	
	set @CurAllTrialOutcomes = CURSOR FOR 
	select About
	from 
	(	--- Manual list of all the possible values that needs to be fed into the SP along with its order 
		select 'Type1_Cur'	About , 1 Serial -- This will make the values to be entered by [Serial] else it would have been alphabetic 
		union
		select 'Type2_Cur' About, 2	Serial

	) a
	order by Serial 
	
	open @CurAllTrialOutcomes
	
	fetch next from @CurAllTrialOutcomes into @TestType 
	declare @i int = 0 -- loop counter alos used to keep track of which type is inserted first  
	while (@@FETCH_STATUS =0)
	begin
		set @i = @i + 1 
		insert into @FetchData (About, Company, SubCat,  WaitTime)
		exec [csrt].[project_reports_Summary_Type] 
			@TestType, @Company		
				 
		update @FetchData
		set OrderNumber = @i
		where About = @TestType
				
		fetch next from @CurAllTrialOutcomes into @TestType 
	end 
	
	close @CurAllTrialOutcomes 
	deallocate @CurAllTrialOutcomes

	--select * from @FetchData -- test ` 
	
--------------------------------------------------------	

	--------========= Making Columns Dynamic so that if if clients ask for more products later on they should automatically come up ------ 
	declare @columnList varchar(max) 
		,@MySQL varchar(max) 
	 
	-- Instead of of maked the below update (which will maked number appearing in the final resultset. it is used in the actual select statement below
	--update @FetchData --- This adds numbers starting from 1 (OrderNumber] to [About] so that it can be sorted when pivotted 
	--set About = replace(str(OrderNumber,2),' ',0) + ' ' + About

	SELECT @columnList= COALESCE(@columnList + ',[' + About + ']'
							,'[' + About + ']')
	FROM (select distinct replace(str(OrderNumber,2),' ',0) + ' ' + [About] OrderAbout -- This will make the select query sort [About]] 
			, [About]  
			from  @FetchData) dl
		
	--select @columnList -- test ` 
	--------==========================================
	
	begin try
		drop table ##GlobalResultSet
	end try
	begin catch
		 PRINT    'Error Handled : ' + ERROR_MESSAGE();
	end catch 
	
	/* The below statement is an alternative 
		IF OBJECT_ID('tempdb..##GlobalResultSet') is not null 
		drop table  ##GlobalResultSet
	*/
	
	-- The below query creates a global resultset because it is required by Dynamic Queries 
	select About, Company, SubCat,  WaitTime
	into ##GlobalResultSet
	from @FetchData  
	
	set @MySQL = 
	'select Company Label, SubCat,  ' 
		+ @columnList + 
	' from 
	(
		select *
			, Thing when Company = ''Average'' then 1 else 0 end  Weight
		from ##GlobalResultSet  
	) as source 
	pivot
	(
		max(WaitTime) 
		for About in ( ' + @columnList + ' )
	) as main
	order by Weight'
	
	exec (@MySQL)

END 

Read Full Post »