Solved

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

Posted on 2009-05-20
5
159 Views
Last Modified: 2012-05-07
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
Comment
Question by:qinyan
  • 3
  • 2
5 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 24435707
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
 

Author Comment

by:qinyan
ID: 24451161
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
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24451371
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
 

Author Comment

by:qinyan
ID: 24451456
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
 

Author Comment

by:qinyan
ID: 24452225
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Question 1 29
spx for moving values to new table 5 60
Sql Data via Excel--performance issues 2 49
Record open by another user 6 41
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now