Jamie's Blog

I found out about a feature in SQL Server 2005 I didn’t know about (having come from a SQL Server 2000 shop). Cross Apply! It’s for when you want to feed the value of a table row into a function and join on the results. This is different than a cross join, because you cannot pass a table column value into a function when you do a cross join.

I whipped up a simple example. Let’s say we want the states that state with a certain letter (this is a trivial example because we could do this with a simple query as well).

select *
from letters
cross apply dbo.get_StateByFirstLetter ( letters.letter )
where letters.letter between 'L' and 'M'

-- equivalent to
select * from dbo.get_StateByFirstLetter ( 'L' )
UNION
select * from dbo.get_StateByFirstLetter ( 'M' )

-- equivalent to (which makes the above trivial)
select * from [states]
where left(state_name, 1) between 'L' and 'M'

Full script to setup everything (pardon the sloppiness)

-- setup the states table
CREATE TABLE [states] (
	state_name varchar(25),
	state_abbr char(2)
)

INSERT INTO states VALUES ('Alaska', 'AK');
INSERT INTO states VALUES ('Alabama', 'AL');
INSERT INTO states VALUES ('American Samoa', 'AS');
INSERT INTO states VALUES ('Arizona', 'AZ');
INSERT INTO states VALUES ('Arkansas', 'AR');
INSERT INTO states VALUES ('California', 'CA');
INSERT INTO states VALUES ('Colorado', 'CO');
INSERT INTO states VALUES ('Connecticut', 'CT');
INSERT INTO states VALUES ('Delaware', 'DE');
INSERT INTO states VALUES ('District of Columbia', 'DC');
INSERT INTO states VALUES ('Florida', 'FL');
INSERT INTO states VALUES ('Georgia', 'GA');
INSERT INTO states VALUES ('Guam', 'GU');
INSERT INTO states VALUES ('Hawaii', 'HI');
INSERT INTO states VALUES ('Idaho', 'ID');
INSERT INTO states VALUES ('Illinois', 'IL');
INSERT INTO states VALUES ('Indiana', 'IN');
INSERT INTO states VALUES ('Iowa', 'IA');
INSERT INTO states VALUES ('Kansas', 'KS');
INSERT INTO states VALUES ('Kentucky', 'KY');
INSERT INTO states VALUES ('Louisiana', 'LA');
INSERT INTO states VALUES ('Maine', 'ME');
INSERT INTO states VALUES ('Marshall Islands', 'MH');
INSERT INTO states VALUES ('Maryland', 'MD');
INSERT INTO states VALUES ('Massachusetts', 'MA');
INSERT INTO states VALUES ('Michigan', 'MI');
INSERT INTO states VALUES ('Minnesota', 'MN');
INSERT INTO states VALUES ('Mississippi', 'MS');
INSERT INTO states VALUES ('Missouri', 'MO');
INSERT INTO states VALUES ('Montana', 'MT');
INSERT INTO states VALUES ('Nebraska', 'NE');
INSERT INTO states VALUES ('Nevada', 'NV');
INSERT INTO states VALUES ('New Hampshire', 'NH');
INSERT INTO states VALUES ('New Jersey', 'NJ');
INSERT INTO states VALUES ('New Mexico', 'NM');
INSERT INTO states VALUES ('New York', 'NY');
INSERT INTO states VALUES ('North Carolina', 'NC');
INSERT INTO states VALUES ('North Dakota', 'ND');
INSERT INTO states VALUES ('Ohio', 'OH');
INSERT INTO states VALUES ('Oklahoma', 'OK');
INSERT INTO states VALUES ('Oregon', 'OR');
INSERT INTO states VALUES ('Palau', 'PW');
INSERT INTO states VALUES ('Pennsylvania', 'PA');
INSERT INTO states VALUES ('Puerto Rico', 'PR');
INSERT INTO states VALUES ('Rhode Island', 'RI');
INSERT INTO states VALUES ('South Carolina', 'SC');
INSERT INTO states VALUES ('South Dakota', 'SD');
INSERT INTO states VALUES ('Tennessee', 'TN');
INSERT INTO states VALUES ('Texas', 'TX');
INSERT INTO states VALUES ('Utah', 'UT');
INSERT INTO states VALUES ('Vermont', 'VT');
INSERT INTO states VALUES ('Virgin Islands', 'VI');
INSERT INTO states VALUES ('Virginia', 'VA');
INSERT INTO states VALUES ('Washington', 'WA');
INSERT INTO states VALUES ('West Virginia', 'WV');
INSERT INTO states VALUES ('Wisconsin', 'WI');
INSERT INTO states VALUES ('Wyoming', 'WY');
GO

-- create the function which we are applying
create function dbo.get_StateByFirstLetter(@firstLetter char(1))
RETURNS @rtn TABLE (
	state_name varchar(25),
	state_abbr char(2)
)
AS
bEGIN
	insert into @rtn
	select state_name, state_abbr from states where left(state_name,1) = @firstLetter
	return
END
GO

-- generate letters table
create table letters ( letter char(1) PRIMARY KEY )
with gen_letters as
(
	select 65 as ascii_val
	UNION ALL
	SELECT ascii_val + 1 from gen_letters where ascii_val + 1 <= 90
)
insert into letters
select char(ascii_val) from gen_letters 

-- get results by cross applying
select *
from letters
cross apply dbo.get_StateByFirstLetter ( letters.letter )
where letters.letter between 'L' and 'M'

-- equivalent to
select * from dbo.get_StateByFirstLetter ( 'L' )
UNION
select * from dbo.get_StateByFirstLetter ( 'M' )

And for good measure, using the WITH clause to generate a table of letters:

with gen_letters as
(
	select 65 as ascii_val
	UNION ALL
	SELECT ascii_val + 1 from gen_letters where ascii_val + 1 <= 90
)
insert into letters select char(ascii_val) from gen_letters

I created an org chart module. It interacts with the profile module to display an organizational hierarchy.

From the project description:

An organizational chart that uses the profile.module. Install in your modules directory. Currently, it uses profile fields to build the chart. It assumes that there will be two fields that hold the following data:

* Employee ID – this can be any alphanumeric identifier
* Supervisor ID – this is the alphanumeric identifier pointing to the current user’s supervisor.

In the administrator settings, you may specify the profile field names which correspond to these values.

There are plans to have two other options for loading the data in the future:

1. A custom external table specific to the module
2. Some flat file

Next on the roadmap is graphviz output.

Check it out!

A co-worker asked me about transposing a question response table. I came from a SQL 2000 background, so I was excited to play with the PIVOT command which is available in SQL 2005 and 2008.  It’s a common use case to transpose row-based data into columns. Sometimes it just makes more sense to people to view data this way.

– first, let’s set up a sample table

drop table response
go
Create table response (
-- no primary key
question_fk int, -- some foreign key to another table
response_num int, -- some column to pivot on
response_text varchar(100), -- some text to show in the table
response_type char(1) -- another foreign key
)
go
insert into response values (1, 1, 'response 1.1', 'a')
insert into response values (1, 2, 'response 1.2', 'a')
insert into response values (1, 3, 'response 1.3', 'a')

insert into response values (2, 1, 'response 2.1', 'b')
insert into response values (2, 2, 'response 2.2', 'b')
insert into response values (2, 3, 'response 2.3', 'b')

-- then, we use a pivot command to transpose the table.

select

-- some columns will just be returned as is
response_type,
question_fk,
-- other columns will pivot based on values and adopt the values as
-- their column names. these columns may be aliased into more appropriate names
[1] as col1,
[2] as col2,
[3] as col3 from
( select question_fk, response_num, response_text, response_type from response ) as src
pivot (
min ( response_text ) -- we use an aggregate for the pivot
for response_num in ([1], [2], [3] )
) as piv -- the pivot table needs a name

-- try this in Query Analyzer to see the results.

I had a problem at work today, and this post on Stack Overflow resolved it.

http://stackoverflow.com/questions/850327/how-to-insert-into-a-table-with-just-one-identity-column

CREATE Table myTable ( id int identity(1,1) )

INSERT INTO myTable DEFAULT VALUES

Here’s a quick script to export table data:

if exists ( select * from sysobjects where name = 'ExportData_P' )
	drop proc ExportData_P
go

CREATE PROC dbo.ExportData_P (
	@tableName varchar(500),
	@where varchar(5000) = '(1=1)'
)
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @sql varchar(8000)
	DECLARE @fieldList varchar(8000)
	DECLARE @valueList varchar(8000)
	SELECT @fieldList = '', @valueList = ''

	DECLARE @cols TABLE ( column_name nvarchar(250), data_type varchar(250) )
	DECLARE @c nvarchar(250), @data_type varchar(250)

	INSERT INTO @cols
	select column_name, data_type
	from information_Schema.columns
	where table_name = @tableName

	WHILE EXISTS ( SELECT TOP 1 * FROM @cols )
	BEGIN
		SELECT TOP 1 @c = column_name, @data_type = data_type FROM @cols

		SELECT
		@fieldList = @fieldList + @c + ', ',
		@valueList = @valueList + CHAR(13) + 'case when ' + @c + ' is null then ''NULL'' else '''''''' + ' +
			case when @data_type in ('text','ntext','char', 'nvarchar', 'varchar' ) then
				' REPLACE ( REPLACE ( REPLACE ( '
				else ''
			end +
			'IsNull ( convert(varchar' +
			( -- change this section to pass the length of varchar to convert
				case when @data_type in ( 'uniqueidentifier' ) then '(50)'
					when @data_type in ( 'text', 'ntext' ) then '(8000)'
				else '' end
			) +
			', ' +
			@c +
			'), '''' )' + -- end is null
			case when @data_type in ('text','ntext','char', 'nvarchar', 'varchar' ) then
				', CHAR(39), CHAR(39)+CHAR(39) ), CHAR(13), '''' + CHAR(13) + ''''), CHAR(9), '''' + CHAR(9) + '''') '
				else ''
			end +
			' + '''''''' end + '', '' + '

		DELETE FROM @cols WHERE column_name = @c
	END

	SELECT @fieldList = LEFT ( @fieldList, LEN(@fieldList)-1 ),
		@valueList = LEFT ( @valueList, LEN(@valueList)-1 )

	SELECT @sql = 'select ''insert into ' + @tableName + ' (' + @fieldList + ') ' +
		' VALUES ( ''+ ' + left ( @valueList, len(@valueList)-5) + ''') '' from ' + @tableName +
		' WHERE ' + @where

	-- into [#mcoe_temp_export' + @tableName + ']
	print @sql
	EXEC ( @sql )
	--EXEC ( 'select * from [#mcoe_temp_export' + @tableName + ']' )		

	SET NOCOUNT OFF
END

go

exec dbo.ExportData_P 'tablename'

CS/IS/Computer-Related FAQ

Topic: SQL

Question
What are joins? What is the difference between all of the different types of joins?

Answer
A join is the combination of columsn across multiple tables. This differs from a union, in that unions are combinations of rows.

The join syntax looks something like:
SELECT * FROM tbl1 [LEFT | RIGHT | INNER | OUTER] JOIN tbl2 ON tbl1.id=tbl2.id;

The syntax of course can have slight variations with additional or alternative keywords. The line above will append the columns of tbl2 onto tbl1, aligning records where the id field of tbl1 is equal to the id field of tbl2.

An inner join will return all those records in tbl1 that have a match in tbl2.
A left join will return all records in tbl1 regardless of it having a match in tbl2. Any missing fields will be filled with NULL.
A right join will return all records in tbl2 regardless of it having a match in tbl1. It is important to note that a left join can be made into a right join by swapping the order of the tables.
An outer join will return the cross product of the tables, meaning all possible combinations from tbl1 and tbl2.

This concludes today’s tip. Thank you for joining me. A har har.

I NEED SQL help!

Is there a way to syntatically join these two ideas??

var1=select conference_id from table1 where conference_name=’Trauma’;

var2=select conference_date from table2 where conference_id.table2=var1;

Answer: select conference_date from table2,table1 where conference_id.table1=conference_id.table2 and conference_name.table1=’trauma’;

I want to find the corresponding primary key to the first table using “conference name” and then look up the conference date using that primary key from the first table.

Thanks for trying Wen! No thanks to all u other a-holes. The answer was so simple i overlooked it!