TSQL – List Columns Names Or Values

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

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

Tool – Transpose & Put Comma – Copy all values from a field

-- This snippet lists all given the field vlaues so that they straight away be copied and put into a string.

 

-- i.e.  set @variable = 'a,b,c,d,e' -- the text inside the single quote is generated

 

-- Change the below sql in the singel quote to replace it with the required field and table name.

 

-- Delete or change the top 100 to the number of values you want it returned
-- Also remember to be inside the server and the database the table is in.
-- Go to the "MESSAGES" tab and copy the piece of text into the clipboard for later use.

declare @passed varchar(max)
set @passed = 'SELECT top 100 [place the field name here] from [place the table name here]'

Declare @fetch table
(
 id int identity,
 store varchar(max)
)

if left(ltrim(@passed), 6) = 'select'
begin
 insert into @fetch
 exec (@passed)
end

select * from @fetch

 

declare @id int, -- Pointer
 @idMax int, -- Scroller
 @pipe varchar(max) -- Final string to return

select @id = 0, @idMax = max(id), @pipe = ''
from @fetch
while @id < @idMax -- whenever @id would be = @idMax; the loop would break
begin
 set @id = @id + 1 -- Start Scrolling
 select @pipe = case when @id < @idMax then @pipe + store + ', '
 else @pipe + store end -- We dont want a comma at the end of the string
 from @fetch where id = @id -- tells which record to fetch
end

print @pipe

-- Please check the screen shot below:
-- I have used the below query:
-- SELECT name FROM dbo.sysobjects WHERE type = 'u'

I have also made an excel file that does a similar job but it is limited to the number of rows. Just copy and past the the fields values in the left most column and press ctrl + d

Click the link below to download it:

Link to download

Read Full Post »

TSQL – Split Function – Feed in multiple values from a parameter – returns Table

-- I did not write this function and I do not take the credit for it.
--This function is used to feed in values from the parameter seperated with  commas. Example:
/*

where @week in (select data from split(@WeekDayNumbers, ','))

@WeekDayNumbers = '1,2,3,4,5,6,7'

*/
& nbsp;

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[Split] (
	@RowData NVARCHAR(max)
	,@SplitOn NVARCHAR(5)
	)
RETURNS @RtnValue TABLE -- if the variable to be returned is mentioned then it does not need to mentioned again after the return key word
	(Data NVARCHAR(100))
AS
BEGIN
	WHILE (Charindex(@SplitOn, @RowData) & gt;0)
	BEGIN
		INSERT INTO @RtnValue (data)
		SELECT Data = ltrim(rtrim(Substring(@RowData, 1, Charindex(@SplitOn, @RowData) - 1)))

		SET @RowData = Substring(@RowData, Charindex(@SplitOn, @RowData) + 1, len(@RowData))
	END

	INSERT INTO @RtnValue (data)
	SELECT Data = ltrim(rtrim(@RowData))

	RETURN -- this returns the current content of what is mentioned above (i.e. RETURNS @RtnValue table)
END

Read Full Post »