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'
					'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  
			insert into @FinalWithOnlyTotal
			select category [Label], avg(requiredfield) [Results]
			from #ResultsGrouped
			group by category
			insert into @FinalWithOnlyTotal
			select category [Label], avg(requiredfield) [Results]
			from #ResultsGrouped
			group by category
		-- 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 )
	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
	(	--- 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 
		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)
		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 
	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 
		for About in ( ' + @columnList + ' )
	) as main
	order by Weight'
	exec (@MySQL)


Read Full Post »