SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[dynamic_pivot]
(
@select nvarchar(2000),
@PivotCol nvarchar(100),
@Summaries nvarchar(100)
) as

set nocount on;
SET ANSI_WARNINGS OFF;

declare @pivot varchar(8000)
declare @sql varchar(8000)
select @select =replace(@select,'select ','select '+@PivotCol+' as pivot_col,')
 

create table #pivot_columns (pivot_column nvarchar(1000))

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)

quote:

---

Originally posted by madhivanan

Why did you remove varchar(max)?
See, I used it but you made it varchar(8000) but kept rest of the code as such
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx



Madhivanan

Failing to plan is Planning to fail

---

/****** Object:  StoredProcedure [dbo].[dynamic_pivot]    Script Date: 11/24/2009 14:06:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter procedure dynamic_pivot
(
@select varchar(2000),
@PivotCol varchar(100),
@Summaries varchar(100)
) as

set nocount on;
SET ANSI_WARNINGS OFF;

declare @pivot varchar(max), @sql nvarchar(max)
select @select =replace(@select,'select ','select '+@PivotCol+' as pivot_col,')
 

create table #pivot_columns (pivot_column varchar(1000))

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 sp_executesql @sql

quote:

---

Originally posted by mfemenel

because I live in this world and your post frightens me...please reconsider using a dynamic sql statemet that is over 8000 characters. Consider the poor bastard who has to change this years from now when you're not around. Scary.

Mike
"oh, that monkey is going to pay"

---

quote:

---

Originally posted by mfemenel

wow, way to overreact

Mike
"oh, that monkey is going to pay"

---

quote:

---

Originally posted by ferrethouse

I got it to work by only selecting the first 20 characters of the pivot column...

select @select =replace(@select,'select ','select substring('+@PivotCol+',1,20) + ''...'' as pivot_col,')

That is obviously only a temporary solution because the number of items will continue to grow so if anyone has any other suggestions I would appreciate it.

---

quote:

---

I printed out this line...

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

And it is getting truncated which I believe is causing the error.

---