Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
dynamic transact-sql contingency table implementing http://stackoverflow.com/a/10404455 in a generic manner
create procedure dbo.dynamic_simple_contingency_table_query
@withExpression nvarchar(max) -- ^ optional with CTE expression
, @selectStatement nvarchar(max) -- ^ select statement, should return at least three columns, whose name matches following parameters
, @row_column_key nvarchar(max) -- ^ column name which identifies row keys (result will have as many distinct rows as found here)
, @column_column_key nvarchar(max) -- ^ column name which identifies column keys (result will have as many distinct values as found here)
, @fact_column_key nvarchar(max) -- ^ column name which contains facts
as
begin
declare @sql nvarchar(max)
set @sql = @withExpression
+'
select z.* into #temptable from ((' + @selectStatement +')) z
declare @cols as nvarchar(max)
declare @query as nvarchar(max)
set @cols = stuff(
(select distinct '','' + quotename(c.' + @column_column_key + ') from #temptable c for xml path(''''), type).value(''.'', ''nvarchar(max)'')
, 1
, 1
, ''''
)
set @query =
''
select
' + @row_column_key + '
, '' + @cols + ''
from
(
select
' + @row_column_key + '
, ' + @fact_column_key + '
, ' + @column_column_key + '
from
#temptable
) x
pivot
(
max(' + @fact_column_key + ')
for ' + @column_column_key + ' in ('' + @cols + '')
) p
''
execute(@query)
'
execute (@sql)
end
@smoothdeveloper

This comment has been minimized.

Copy link
Owner Author

commented Oct 9, 2013

sample usage:

------------------------------------------------------------------------------
declare @withexpr nvarchar(max)
declare @sql nvarchar(max)
declare @rk nvarchar(max)
declare @ck nvarchar(max)
declare @fk nvarchar(max)

set @withexpr = '
;with sales (time, category, fact) as (
select ''jan'', ''apple'', 3
union all select ''jan'', ''orange'', 3
union all select ''feb'', ''apple'', 6
union all select ''feb'', ''orange'', 10
)
'
set @sql = 'select * from sales r'
set @rk = '[time]'
set @ck = '[category]'
set @fk = '[fact]'
exec dbo.dynamic_simple_contingency_table_query @withexpr, @sql, @rk, @ck, @fk
------------------------------------------------------------------------------
@rschaeferhig

This comment has been minimized.

Copy link

commented Apr 22, 2015

I'm trying to use this on a SQL 2012 table. I get no output from the SP. I believe the problem may be in here:
pivot
(
max(' + @fact_column_key + ')
for ' + @column_column_key + ' in ('' + @cols + '')
) p

I need to return absolute/distinct values in the fact column, not aggregates. I tried:
pivot
(
@fact_column_key
for ' + @column_column_key + ' in ('' + @cols + '')
) p

but that doesn't work. I inserted "print @cols" and "print @query" into the SP to see if it would show me what was being calculated but I get no output.

Any thoughts?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.