timeline in cross-tab??

Question

timeline in cross-tab??

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 1

Looking through the solutions, I'd have come up with pretty much the same thing as what Luis did. He also came up with it at about the same time that I would have had I been able to keep my promise.

Not sure if you still need an explanation but his code puts together a classic "Cross Tab" using some date driven dynamic SQL. He uses the Tally Table, which is a simple table of sequential numbers, to build some dates and to control how many aggregated case statements (one for each display column and does the "pivot").

The article at the link that previously provided explains much more.

Well done everyone and thank you very much for a much needed cover.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

r_slot (4/10/2013)
Thanks a lot Steve: I understand you solution a little bit better than the others, but the subject stays difficult for me. When I look into the messages as a result of running your solution I see a perfect (dynamic) table. Is it possible to export that table to a webpage (eg Visual Web .aspx)? I want to show the results in a gridview, but the SQL-variable is of course no table with rows. In fact is your solution in the end a table and could be the final product of a stored procedure.
The (silly) question I still have is: is the above mentioned possible or am I talking rubbish?
Any answers/reactions would be appreciated.
Above all: compliments for your solution - I gonna try to understand it.
Robert

To answer the question

Yes, it's possible to do it. Unfortunately, I don't know how to do it. A developer in a previous job used that in the web app, but I'm not sure how he did it.

If you find it easier to have a physical table, you can use SELECT...INTO to create a table.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

r_slot Ten Centuries Points: 1379 More actions · Answer 3

Luis,

A Select Into will do, but where in your code does it appear. In between in place of something else or at the end but with what variables?

It sounds stupid, but I have no idea: too much standarised thinking or no thinking at all.

Can you give me a hint once more?

I would appreciate that very much.

Robert

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Check the code and look for the part where the sql statement is completed and stored into @sql.

In there, you just have to add the INTO clause.

You might want to drop the table before creating it and check if it exists before dropping it.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

r_slot Ten Centuries Points: 1379 More actions · Answer 5

I tried the following (after making a table calles Results):

SELECT @sql = String FROM SQL_String

SET @sql = 'SELECT ev.Event_Name INTO dbo.Results' + CHAR(10) + @sql +

'FROM #Events ev ' + CHAR(10) +

'WHERE ev.event_year = @Year '

PRINT @sql

EXEC sp_executesql @sql, N'@Year int', @Year = @Edition_Year

but that does not work at all. The table has two fields: Event_Name and @sql.

I also tried the following:

SET @sql = 'SELECT ev.Event_Name' + CHAR(10) + @sql + 'INTO dbo.Results' +

'FROM #Events ev ' + CHAR(10) +

'WHERE ev.event_year = @Year '

with the same result. Nothing but errors.

What am I doing wrong?

Robert

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

Use the PRINT output to look for the error, I suspect that is something really simple.

If you can't find it, copy it and paste it here.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

r_slot Ten Centuries Points: 1379 More actions · Answer 7

I use the following code (last part):

SET @sql = 'SELECT ev.Event_Name' + CHAR(10) + @sql +

'INSERT INTO dbo.Results FROM #Events ev ' + CHAR(10) +

'WHERE ev.event_year = @Year '

When I run the query I get the following message:

(2 row(s) affected)

(8 row(s) affected)

SELECT ev.Event_Name

,CASE WHEN '20120727' BETWEEN ev.Event_Start AND ev.Event_Finish

THEN 'X' ELSE '' END AS '27-07'

,CASE WHEN '20120728' BETWEEN ev.Event_Start AND ev.Event_Finish

THEN 'X' ELSE '' END AS '28-07'

,CASE WHEN '20120729' BETWEEN ev.Event_Start AND ev.Event_Finish

THEN 'X' ELSE '' END AS '29-07'

,CASE WHEN '20120730' BETWEEN ev.Event_Start AND ev.Event_Finish

THEN 'X' ELSE '' END AS '30-07'

,CASE WHEN '20120731' BETWEEN ev.Event_Start AND ev.Event_Finish

THEN 'X' ELSE '' END AS '31-07'

,CASE WHEN '20120801' BETWEEN ev.Event_Start AND ev.Event_Finish

THEN 'X' ELSE '' END AS '01-08'

,CASE WHEN '20120802' BETWEEN ev.Event_Start AND ev.Event_Finish

THEN 'X' ELSE '' END AS '02-08'

,CASE WHEN '20120803' BETWEEN ev.Event_Start AND ev.Event_Finish

THEN 'X' ELSE '' END AS '03-08'

,CASE WHEN '20120804' BETWEEN ev.Event_Start AND ev.Event_Finish

THEN 'X' ELSE '' END AS '04-08'

,CASE WHEN '20120805' BETWEEN ev.Event_Start AND ev.Event_Finish

THEN 'X' ELSE '' END AS '05-08'

,CASE WHEN '20120806' BETWEEN ev.Event_Start AND ev.Event_Finish

THEN 'X' ELSE '' END AS '06-08'

,CASE WHEN '20120807' BETWEEN ev.Event_Start AND ev.Event_Finish

THEN 'X' ELSE '' END AS '07-08'

,CASE WHEN '20120808' BETWEEN ev.Event_Start AND ev.Event_Finish

THEN 'X' ELSE '' END AS '08-08'

,CASE WHEN '20120809' BETWEEN ev.Event_Start AND ev.Event_Finish

THEN 'X' ELSE '' END AS '09-08'

,CASE WHEN '20120810' BETWEEN ev.Event_Start AND ev.Event_Finish

THEN 'X' ELSE '' END AS '10-08'

,CASE WHEN '20120811' BETWEEN ev.Event_Start AND ev.Event_Finish

THEN 'X' ELSE '' END AS '11-08'

INSERT INTO dbo.Results FROM #Events ev

WHERE ev.event_year = @Year

Msg 156, Level 15, State 1, Line 34

Incorrect syntax near the keyword 'FROM'.

My code is wrong, as you can see. Because of the error Print does not work.

Any suggestions?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

You don't need to use INSERT, that's a different instruction.

Take a look at this link http://msdn.microsoft.com/en-us/library/ms190750(v=sql.100).aspx

and this one http://msdn.microsoft.com/en-us/library/ms188263(v=sql.100).aspx

To find the differences between both.

Avoid using a generic table name as Results and give a more describing name to avoid confusions later on.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

r_slot Ten Centuries Points: 1379 More actions · Answer 9

Luis (and others) stupid mistake: of course select.. into instead of select insert into. Typical newbie..

As a sort of wrap up: I changed your code in the following way (minor changes):

SET @sql = 'SELECT ev.Event_Name' + CHAR(10) + @sql +

'INTO dbo.TimelineResults FROM #Events ev ' + CHAR(10) +

'WHERE ev.event_year = @Year '

PRINT @sql

-- creates the table dbo.Results

EXEC sp_executesql @sql, N'@Year int', @Year = @Edition_Year

SELECT * FROM dbo.TimelineResults

Remarks:

>> no dbo.Results but dbo.TimelineResults as you suggested

>> SELECT ..... INTO ..... And SELECT * FROM dbo.TimelineResults etc. did the trick. The end result is a table and I use it in a website (work in progress).

>> I do understand the code, but it stays difficult to invent it by yourself - eg the apostrophes drive me crazy. I made a tally table, then a cross join with the dates, put the whole (cross-tab) query in a dynamic SQL-variable and execute the end-result. The last step is presenting the result in a table as TimelineResults.

Again thanks for the efforts of you all. Looking forward to other, new and inspiring problems!

Grz,

Robert