Dynamic Pivot

Hi

Is there a way for using the PIVOT synatx with a dyamic/unknown list of columns? In most PIVOT examples ther columns are "hard" coded but if for example, you are pivoting a sales order table where u don't know which Fiscal Years the orders cover, how could you do this?

Thanks

Hi,

I followed a link http://www.sqlmag.com/Article/ArticleID/43140/sql_server_43140.html

and get this one working in another forum.

http://forums.asp.net/2/1392175/ShowThread.aspx#1392175

If you have question for that, please let me know.

|||Thanks limno, this helps a lot

Dynamic Partitions using SSIS

I have a client at which I am currently utilizing a method similar to the one found in the Integration Services examples where a SQL query is used to return the partitions that should exist in an SSAS cube. Then the SSIS packge uses the results to see if the partitions exist and creates them if necessary.

This works well, however, I now have the need to handle partitions in a "rolling" fashion. So I want to process the 3 previous months of data into an active partition (or partitions) and roll the "old" stuff in to year partitions.

My thought is that I will want to do this using code rather than relying on a SQL query to tell me what to do because there will be a greater dependency on what actually exists in the SSAS cube. I think I will have to create some logic to see what partitions actually exist and then act on that information. I will also want to merge "old partitions" into the larger year chunks.

Has anyone done anything like this? If so, what did you find worked best and what kind of pitfalls did you find? If I was not clear in my description, please let me know.

Try to look at the materials coming with documentation for project REAL: http://www.microsoft.com/sql/solutions/bi/projectreal.mspx#E3B

There is tons of useful material you'd find there.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Dynamic Cursor/ Dynamic SQL Statement

I've looked up Books Online on Dynamic Cursor/ Dynamic SQL Statement.

Using the examples given in Books Online returns compilation errors. See below.

Does anyone know how to use Dynamic Cursor/ Dynamic SQL Statement?

James

-- SQL -----

EXEC SQL BEGIN DECLARE SECTION;
char szCommand[] = "SELECT au_fname FROM authors WHERE au_lname = ?";
char szLastName[] = "White";
char szFirstName[30];
EXEC SQL END DECLARE SECTION;

EXEC SQL
DECLARE author_cursor CURSOR FOR select_statement;

EXEC SQL
PREPARE select_statement FROM :szCommand;

EXEC SQL OPEN author_cursor USING :szLastName;
EXEC SQL FETCH author_cursor INTO :szFirstName;

--Error-------
Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near ';'.
Server: Msg 1038, Level 15, State 1, Line 24
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 1038, Level 15, State 1, Line 25
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 170, Level 15, State 1, Line 27
Line 27: Incorrect syntax near ';'.
Server: Msg 170, Level 15, State 1, Line 30
Line 30: Incorrect syntax near 'select_statement'.
Server: Msg 170, Level 15, State 1, Line 33
Line 33: Incorrect syntax near 'select_statement'.
Server: Msg 102, Level 15, State 1, Line 35
Incorrect syntax near 'author_cursor'.
Server: Msg 170, Level 15, State 1, Line 36
Line 36: Incorrect syntax near ':'.using the following seem to be achieving what i wanted.
but i would still like to know how to use
1. PREPARE
2. EXECUTE
i.e. under what circumstances would you use those 2?
It must be there for a reason.

James

--Working SQL-----

DECLARE @.sql nvarchar(4000)
SET @.sql = 'DECLARE @.name nvarchar(128) ' +
'DECLARE test_cursor CURSOR FOR SElECT name FROM class_category ' +
'OPEN test_cursor ' +
'FETCH NEXT FROM test_cursor INTO @.name ' +
'BEGIN ' +
'PRINT @.name ' +
'FETCH NEXT FROM test_cursor INTO @.name ' +
'END '
EXECUTE sp_executesql @.sql|||You seem to be confusing the VB environment with the Transact-SQL environment. The VB code executes only on the client side, and that is where a PREPARE might make sense. The Transact-SQL code executes only on the server side, and you can't explicitly prepare code there (at least under normal circumstances).

-PatP

dynamic column headings?

Is it possible for Report Services 2000 to accomplish the following. Are there any examples available that i can take a look?

How can I turn rows in a table into column headings in a report. By the way, the user can chose one questions to see the answers for all the Test Takers.

The questions and answers are rows in two seperate tables.

For example, a certain test may have N questions and N answers. I would like to make the questions the column headings and the answers the rows. Into something like this.

Test Taker Questions 1 Questions2 Questions3 QuestionsN+1

Mike Jay Answer1 Answer2 Answer3 AnswerN+1

Jon Doe Answer1 Answer2 Answer3 AnswerN+1

etc....

Can anyone suggest a solution or documentation?

Thank you,

yadgor2000

Try using a matrix. You can easily do this in VS.Net.