Dynamically Create Columns Based on Other Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dynamically Create Columns Based on Other Table

Author

Topic

klondono
Starting Member

6 Posts

Posted - 2011-07-13 : 09:03:28

Select * from #temp

The statement above yields the following records:

ShopName-----LicenseNo----Officer------TypeOfInsp - Result - Date
ABC Shop LIC1 Alfredo Diaz MVR Insp Approved 6/1/2011
ABC Shop LIC1 Suresh Bedasie MVR Insp Approved 6/10/2011
GEH Shop LIC3 Suresh Bedasie MVR Insp Approved 7/1/2011
IJK Shop LIC4 Alfredo Diaz MVR Insp Approved 8/1/2011
LMN Shop LIC5 Alfredo Diaz MVR Insp Approved 6/1/2011
LMN Shop LIC5 Suresh Bedasie MVR Insp Approved 7/1/2011
RST Shop LIC7 Alfredo Diaz MVR Insp Approved 2/1/2011
UVW Shop LIC8 Alfredo Diaz MVR Insp Approved 6/1/2011

From the table above, I extract the total inspections done for each officer per shop with the following statement. This is the Total Inspections by Officer table :

select
[Shop] = T.ShopName
, [LicenseNo] = T.LicenseNo
, [Alfredo] = (SUM(case when T.Officer = 'Alfredo Diaz' then 1 else 0 end))
, [Suresh] = SUM(case when T.Officer = 'Suresh Bedasie' then 1 else 0 end)
, [Total_Insp] = count(*)
from #temp T
group by T.ShopName, T.LicenseNo
ORDER BY [Total_Insp] desc

This is the result:

Shop-----LicenseNo----Alfredo------Suresh ---Total_Insp
ABC Shop LIC1 1 1 2
GEH Shop LIC3 0 1 1
IJK Shop LIC4 1 0 1
LMN Shop LIC5 1 1 2
UVW Shop LIC8 1 0 1

Instead of explicitly creating the columns for each officer (this will then require that I change the query each time an officer is hired or fired), how can I create the Total Inspections by Officer table to dynamically create a column depending on the total number of officers that appear on the first table? Any help will be greatly appreciated. Thank you!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-07-14 : 05:25:33

Refer this post
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

klondono
Starting Member

6 Posts

Posted - 2011-07-14 : 21:27:44

Thank you so much! I used the stored procedure that dynamically creates my columns and provides the totals for each officer. I would like to create a totals column but cant figure out how to do it. I'll provide the sql statement i used to get the totals & create the columns tomorrow.

Seeking Analyst/Programming Job - Career changer

klondono
Starting Member

6 Posts

Posted - 2011-07-15 : 09:52:35

Thank you very much Madhivanan!! It worked using the code you posted. I created the procedure "dynamic_pivot" and then executed it with the parameters below. This is the entire SQL statement:

Select * from #temp

--Create the procedure that will dynamically create your columns

create procedure dynamic_pivot
(
@select varchar(2000),
@PivotCol varchar(100),
@Summaries varchar(100)
) as
declare @pivot varchar(max), @sql varchar(max)
select @select =replace(@select,'select ','select '+@PivotCol+' as pivot_col,')

create table #pivot_columns (pivot_column varchar(100))

Select @sql='select distinct pivot_col from ('+@select+') as t'

insert into #pivot_columns
exec(@sql)

select @pivot=coalesce(@pivot+',','')+'['+pivot_column+']'from #pivot_columns

select @sql=
'
select * from
(
'+@select+'
) as t
pivot
(
'+@Summaries+' for pivot_col in ('+@pivot+')
) as p
'

exec(@sql)

--Execute dynamic_pivot with SQL select statement, column requiring pivoting and specific column to count
EXEC dynamic_pivot
'select [ShopName] = T.ShopName, T.LicenseNo, T.Officer from #temp T',
'(T.Officer)',
'Count(T.Officer)'

Seeking Analyst/Programming Job - Career changer

klondono
Starting Member

6 Posts

Posted - 2011-07-15 : 09:57:08

The code above dynamically creates the Officer columns (Alfredo Suresh), How do I add a totals column to the end of each row? Also, how do I add totals for the Alfredo and Suresh columns using the stored procedure in the previous post? I know how to do it when I know in advance what the name of Officer columns will be but that is not the case here. Any help will be appreciated.
Shop-----LicenseNo----Alfredo------Suresh
ABC Shop LIC1 1 1
GEH Shop LIC3 0 1
IJK Shop LIC4 1 0
LMN Shop LIC5 1 1
UVW Shop LIC8 1 0 1

Seeking Analyst/Programming Job - Career changer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources