Dynamic Columns Names

Question

Dynamic Columns Names

Viewing 8 posts - 16 through 22 (of 22 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Chris Morris-439714 (10/22/2010)
BTW while you are writing a reply, scroll down to the bar which reads "last 10 posts..." and click on the plus-sign on the RHS - which will then expand.

Ah!!! Thanks Chris!!! I didn't know that!

You don't even know how many times I dreamed of this feature, and it was already there!:blush:

-- Gianluca Sartori

hallidayd SSCrazy Points: 2490 More actions · Answer 2

Invaluable tip re. last 10 posts - wish it was expanded by default!

I too have written things like this, and I would not suggest that it is wrong regardless of circumstance and requirement. However it is very much the exception and is often requested on forums when not necessary.

I assume that when someone wants to do this yet does not know how that they do not have knowledge and experience of modern design architectures and patterns and so take the opportunity to challenge them on this. Sometimes I am wrong and they can put forth a compelling counter, but more often than not it is because they are not aware of alternatives\ best practice etc.. Call it playing devil's advocate - even though the OP has come away with exactly what (s)he asked for, hopefully (s)he will have heard enough to perhaps pause and review the alternatives the next time (s)he wants to do a similar thing.

It's one of the things I like about forums like this, rather than the point scoring ones, that discussions on wider points of practice can be entered into; this, rather than a literal answer to all questions, leads to more rounded and contextualised answers and benefits all concerned.

rootfixxxer SSCrazy Points: 2918 More actions · Answer 3

Thanks for all the replies and tips.

I know that this isn't the right way to do the things, but i don't have (don't see) any other way...

I need a quick way to display all rows for all user in an date interval (the gui it's vb.net), this date interval it's dynamic, so the only way that i see to do the job it's this way...

If anyone can give/tell/show me other way... 🙂

hallidayd SSCrazy Points: 2490 More actions · Answer 4

I'll confess that I can't just say "oh, do x, y and z" when it comes to client side coding but I can usually muddle through when I need to.

You can easily work out the column names in the client language, right? 2nd column is '10/22/2010', 3rd column is '10/21/2010' etc.. That column name then just needs writing to the control; how that is done depends on what you are using.

rootfixxxer SSCrazy Points: 2918 More actions · Answer 5

@hallidayd

I wasn't asking for tips for the client side... I know how to do that 🙂

I need to know what would you do if you need to get from the db that kind of data, like i said the user can define in the GUI the start date and the end date (input parameters), i just need to set the sp to retrieve the number of columns that belong to the date interval... but different from Gianluca's method.

Thanks

hallidayd SSCrazy Points: 2490 More actions · Answer 6

The sp retrieves nothing; its scope does not extend beyond the database. Think of it like the interface of an object, where the database is the object and the sp is a method (assuming you are familiar with OO design).

As such, you pass the information (that is the information you have gathered on your UI and will present back to the user ;-)) to the sp as the value for a parameter.

http://msdn.microsoft.com/en-us/library/aa258259%28SQL.80%29.aspx

In the example you have the value is set to a variable. Juct change that to a parameter and vwa la! The coupling is complete.

Sergiy SSC Guru Points: 110208 More actions · Answer 7

rootfixxxer (10/26/2010)
I need to know what would you do if you need to get from the db that kind of data, like i said the user can define in the GUI the start date and the end date (input parameters), i just need to set the sp to retrieve the number of columns that belong to the date interval... but different from Gianluca's method.
Thanks

Just don't put it in columns - put in rows (records).

DECLARE @data AS DATETIME

SET @data = '10/10/2010'

SELECT UtilizadorNome,

DATEADD(dd, DATEDIFF(dd, 0, DataRegisto), 0) AS DateRegisto,

SUM(Horas) AS SumHoras

FROM dbo.PontoRegistos

WHERE DataRegisto >= @data AND DataRegisto <DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 1)

GROUP BY UtilizadorNome, DATEADD(dd, DATEDIFF(dd, 0, DataRegisto), 0)

--Sorry, cannot test is as you did not provide CREATE TABLE statement and data samples

You may easily transpose rows for different days into columns in Application layer.

Ordering by {UtilizadorNome, DateRegisto} would make this task easier.

_____________
Code for TallyGenerator

rootfixxxer SSCrazy Points: 2918 More actions · Answer 8

Maybe it's a solution...

If you want to try something you can use the data posted by Gianluca, the table has other columns but they aren't relevant for this case, only need the user (UtilizadorNome), dates and the sum of values for the dates...

I'll make some tests, and see if that works like the way i want. 🙂

Thanks