January 28, 2010 at 8:52 am
Newbie,
Have 1 table, need to convert rows to columns
Example:
Col1 Col2
1 ABC
2 DEF
3 GHI
4 JKL
Need it to look like this:
ABC DEF GHI JKL
1 2 3 4
Any help would be greatly appreciated
February 2, 2010 at 6:40 am
1. Cannot PIVOT without aggregation
2. You will need to use Dynamic SQL
e.g.
DECLARE @sql varchar(8000)
SELECT @sql = COALESCE(@sql + ',MAX(CASE WHEN Col2='''+Col2+''' THEN Col1 END) AS ['+Col2+']',
'SELECT MAX(CASE WHEN Col2='''+Col2+''' THEN Col1 END) AS ['+Col2+']')
FROM #table
SET @sql = @sql + ' FROM #table'
EXEC(@sql)
This uses a single variable and is subject to the 8K limit, if there is a possiblity of greater then you will need to use multiple variables.
Beware the pitfalls of dynamic sql, read about in on this forum if you are unsure.
Far away is close at hand in the images of elsewhere.
Anon.
February 2, 2010 at 7:01 am
hi i came across one post by one of SQL Master. let me share it with you.
you can use this for dynamic pivot.
Requirements:
table: RRTab (user_name varchar(100),Day datetime,time int)
please insert some dummy records ... to see the result.
create procedure dbo.DynamicPivotProcess
(
@select varchar(2000),
@PivotCol varchar(100),
@Summaries varchar(100)
)
--EXEC DynamicPivotProcess 'SELECT USER_NAME,time FROM RRtab','[day]','max(time)'
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)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply