Dynamically pivot a table - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Dynamically pivot a table

Author

Topic

lcpx
Yak Posting Veteran

54 Posts

Posted - 2006-02-23 : 04:05:38

We are developing reports for our customers under SQL server 2000 environment. We have a table, the structure like below:

Prod_Code Promotions Date
---------------- ----------------- --------

Our customers want a cross-tab report with product code on the left, Date on the top and Promotion details in the middle.

Prod_Code 01/01/05 01/02/05 01/03/05 01/04/05
1 Promotions
2 Promotions
3 Promotions Promotions
4 Promotions

Can anybody advise me how I can achieve this. Thanks very much for your help!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-23 : 04:07:13

Refer this
http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-23 : 04:20:06

Also refer to here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Pivot+Table on Pivot Table

----------------------------------
'KH'
It is inevitable

lcpx
Yak Posting Veteran

54 Posts

Posted - 2006-02-23 : 04:20:38

Hi Madhivanan, many thanks for your response. But it seems my case is a bit different. The promotions in my table is varchar type, is not number. I cannot sum them.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-23 : 04:44:56

Refer to the link that i provided. Somewhere it refers to here. http://www.sqlteam.com/item.asp?ItemID=5741
You can do something like this

max(case [Promotion Date] = '2005-01-01' then Prod_Code else NULL end) as [01/01/05],
max(case [Promotion Date] = '2005-01-02' then Prod_Code else NULL end) as [01/02/05]

----------------------------------
'KH'
It is inevitable

lcpx
Yak Posting Veteran

54 Posts

Posted - 2006-02-23 : 04:52:50

Thanks Khtan. The only thing is we got more than 3 years data in the table, and it will be updating everyday. I can't type all these date into my SQL, it's impossible. I have to find a way to do it in a loop. Any idea on it? Thanks for your time and help!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-23 : 04:57:53

Use Dynamic SQL to do this.

----------------------------------
'KH'
It is inevitable

lcpx
Yak Posting Veteran

54 Posts

Posted - 2006-02-24 : 07:27:43

Hi all

I tried the Dynamic SQL, it worked ok. The only problem is how can I order the columns by date ascendly, since all those store procedures are for varchar type of data.

From the above example, you will see i need to show the promotional date at the top of the report, users can see the length of promotional period easily. It's doesn't make sense if they are not ordered correctly.

Any idea would be greatly appreciate!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-24 : 07:47:08

You should store dates in DateTime datatype. If they are of varchar, convert and order it

Order by convert(datetime,varchardate)

assuming that varchardate has valid date values

Madhivanan

Failing to plan is Planning to fail

lcpx
Yak Posting Veteran

54 Posts

Posted - 2006-02-24 : 09:21:29

Here is the sp I am using to pivot the table:
create procedure CrossTab2 (@SQL varchar(1000),
@PivotCol varchar(100),
@Summaries varchar(100),
@GroupBy varchar(100),
@OtherFields varchar(100) = Null,
@Debug bit = 0)
AS

set nocount on
set ansi_warnings off

declare @Vals varchar(8000);
declare @Vals2 varchar(8000);
declare @Vals3 varchar(8000);
declare @tmp varchar(1000);
declare @TotalLen int;

set @Vals = '';
set @Vals2 = '';
set @Vals3 = '';
set @TotalLen = len(@SQL) + len(@GroupBy) + Len(ISNULL(@OtherFields,''))

set @OtherFields = isNull(', ' + @OtherFields ,'')

create table #temp (Pivot varchar(100))

insert into #temp
exec ('select distinct convert(varchar(100),' + @PivotCol + ') as Pivot FROM (' + @SQL + ') A')

select @tmp =
replace(replace(@Summaries,'(','(CASE WHEN ' + @PivotCol + '=''' + replace(Pivot,'''','''''') +
''' THEN '),')[', ' END) as [' + Pivot ),
@TotalLen = @TotalLen + Len(@tmp),
@Vals = case when @TotalLen < 7800 then @Vals + ', ' + @tmp else @Vals end,
@Vals2 = case when @TotalLen between 7800 and 15799 then @Vals2 + ', ' + @tmp else @Vals2 end,
@Vals3 = case when @TotalLen between 15800 and 23799 then @Vals3 + ', ' + @tmp else @Vals3 end
from
#Temp
order by
Pivot

drop table #Temp
if (@Debug=0)
exec ( 'select ' + @GroupBy + @OtherFields + @Vals + @Vals2 +
@Vals3 + ' from (' + @SQL + ') A GROUP BY ' + @GroupBy)
else
begin
create table #Temp2 (SQLText Text);
insert into #Temp2 (SQLText)
values ('select ' + @GroupBy + @OtherFields + @Vals + @Vals2 +
@Vals3 + ' from (' + @SQL + ') A GROUP BY ' + @GroupBy);
select * from #Temp2
end

set nocount off
set ansi_warnings on

Here is my SQL call this SP:

execute CrossTab2 'select Promotional_Date, Prod_Code,PROMO_NAME
from dbo.F_PIVOT_RETAIL_PRICE_DATA
where Promotional_Date between ''2005-02-05'' and ''2006-02-05''',
'Promotional_Date',
'sum(PROMO_NAME)[]',
'Prod_Code'

Everything works fine apart from the pivoted columns are order alphabetically rather than by datetime. I did whatever I could change the sp but just cannot make it work. Apprecaite any thought you have on it!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-24 : 10:21:34

What tool are you using to display/output this report for the users?

lcpx
Yak Posting Veteran

54 Posts

Posted - 2006-02-24 : 11:05:32

Hi jsmith8858, I am using excel as the front-end. But no matter what tools I use I have to get the data display correctly in SQL analyzer. right? Any idea would be appreciated.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-02-24 : 11:12:16

>>But no matter what tools I use I have to get the data display correctly in SQL analyzer. right?

Close ... but the word "display" is the problem. You want the values to be correct in Query Analyzer, but that's all. You should never, ever be concerned with formatting your output in Query Analyzer. make sure that you can write a SELECT to return the data you need, grouped and filtered as much as possible, but do not worry about formatting. Worry about the numbers being correct and all that. Then, you use the SQL you've been working on in QA to return the raw data to your presentation layer and let THAT do the formatting.

Excel has pivot tables that will do this for instantly, with absolutely no code to write or T-SQL to alter. It will be simplier and faster and even more flexible, since you can filter/sort on all different things and drag things around and get groupings and totals and all that. You can get totals by month, by year, and so on, in addition to one column per day if you want (which, to be honest, I doubt you want-- you really want a spreadsheet with 365 columns?)

Don't use SQL Server to pivot the data if you don't have to.

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2006-02-25 : 01:44:06

This one is much readable, much better and much faster.

http://sqlteam.com/item.asp?ItemID=2955

Want Philippines to become 1st World COuntry? Go for World War 3...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources