Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

Dynamic way to turn rows (which are already in cross-tab format) into columns?

The recordset I have is like this:
Date      Type            North_Cnt       West_Cnt
5/1/09     A                 20                  30  
5/1/09     B                 40                  25
5/2/09     A                30                  20
5/2/09     B                35                  45
...
I want to turn it into this way:
Date      Type    North_Cnt   West_Cnt    Date     Type  North_Cnt     West_Cnt
5/1/09     A             20                30         5/2/09     A          30                  20
5/1/09     B             40                25         5/2/09     B           35                 45

so if there are 10 dates then there will be 10 sections each of which has 4 columns (date, type, North_cnt, West_cnt) so totally 40 columns.

I want to try Pivot in 2005 but doubt it would work. I guess the only way is to use dynamic sql?
Has anyone done something similar like this before?
0
qinyan
Asked:
qinyan
  • 3
  • 2
1 Solution
 
BrandonGalderisiCommented:
One of our local hot shots here at EE wrote an article on doing dynamic pivots.

Take a look at this:

http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Dynamic-Pivot-Procedure-for-SQL-Server.html
0
 
qinyanAuthor Commented:
Thanks! This is a very helpful article, the only problem is that it can only have one pivot_value_column in the aggregate. it doesn't do both sum(Tcs_quantity) and sum(tcs_quantity) together in the same pivot in the example, but this function is what i need.
0
 
BrandonGalderisiCommented:
I haven't worked with PIVOT a lot, but I'll assume that "sum(Tcs_quantity) and sum(tcs_quantity)" are acutally two different columns.  You MAY need to do two pivot statements.  Or two whole selects, built as derived tables, then joined together.
0
 
qinyanAuthor Commented:
actually since my recordset is already in pivot format i'm looking for something like this:
select * from
(
select * from myTable
where date=5/1/09'
) a
join
(
select * from myTable
where date=5/2/09'
) b
on a.type=b.type

and make it dynamic:
declare @min datetime
select @min = min(date) from myTable

declare @max datetime
select @max=max(date) from myTable

declare @sql varchar(max)
select @sql = 'SELECT * from (select * from myTable where date=, ' + CHAR(13),

while @min<=@max
begin
SET @sql = @sql + ''

SET @min = @min + 7 ;

end
0
 
qinyanAuthor Commented:
I haven't worked with PIVOT a lot, but I'll assume that "sum(Tcs_quantity) and sum(tcs_quantity)" are acutally two different columns.  You MAY need to do two pivot statements.  Or two whole selects, built as derived tables, then joined together.

Yes I can do it that way and that's the only way to do it if using pivot, but I was just trying to avoid two or multiple pivot statements as I have 4 columns which i need to do aggregation on. That's why i was thinking about other ways in dynamic sql.

Thanks!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now