I'm moving a process from Access to SQL Server 2000. I have a report which picks out some data from the last 6 working weeks and ultimately produces an output like
User 10_2005 9_2005 8_2005
Tom 5% 6% 12%
Dick 4% 8% 7%
Harry 3% 5% 2%
And so on. The week numbers are our own internal calendar. What I used to do was:
1 Generate a data table with the last 6 weeks' data in it.
2 Use a crosstab query to do some of the initial calculations
3 In VB code, look at the structure of the crosstab query and pull out the column names into an array
4 Use those column names to generate an SQL string for the final output
here's the code
Set qd = db.QueryDefs("my_crosstab_query")
For foo = 0 To 6
Fname(foo) = qd.Fields(foo).Name
Next
qd.Close
SQLstr = "SELECT e.createdby,e.Type,"
For foo = 0 To 6
SQLstr = SQLstr & "s.[" & Fname(foo) & "]/e.[" & Fname(foo) & "] AS " & Fname(foo) & ","
Next
SQLstr = left(SQLstr, Len(SQLstr) - 1)
SQLstr = SQLstr & " FROM crosstab1 e INNER JOIN crosstab2 s ON (e.Type = s.Type) AND (e.CreatedBy = s.CreatedBy)"
Can anyone point me in the right direction for converting this over to T-SQL? I'm happy with using CASE to perform the equivalent of a crosstab, but whenever I've used this construct before I've known what all the column names wree going to be in advance - here I don't.
Scot Doughty