Pivot Table View Required in (sql 2000)

[SarahMH]

I have to try and create a view on transact sql (2000)

The view needs to show the values of the past 5 days data by metric.

Example of what i require

Metrics Today -4 Today -3 Today -2 Today-1 today
1 A&E Attendances Value Value Value Value Value
10 AvgLos 30 WARD Value Value Value Value Value
Up to 20metric Value Value Value Value Value

I have attached a file with the example data of what is required but I have also included below an example of the table it will be pulled from contains the past 5 days worth of data, a row for each day and each metrics (20+ in total) ie.

Metric Metrics Value Datestamp ID
1 1 A&E Attendances 234
09/03/2011108/03/2011A&EAttendances
1 1 A&E Attendances 258 08/03/2011 107/03/2011A&EAttendances
1 1 A&E Attendances 204 07/03/2011 106/03/2011A&EAttendances
1 1 A&E Attendances 17 05/03/2011 104/03/2011A&EAttendances
10 10 AvgLos 30 WARD 25 09/03/2011 1008/03/2011AvgLos30WARD etc.......

Please would you help.

I have tried various ways but cannot seem to get the unique value (it is not a sum or average just the field value) per day, per metrics

Thank you very much.

 

[markros]

declare @Today datetime
set @Today = dateadd(day,datediff(day,'19000101',getdate()), '19000101') -- get rid of time portion

select Metric, Metrics, 
max(case when DateStamp = dateadd(day,-4, @Today)
then Value end) as [Today - 4],

max(case when DateStamp = dateadd(day,-3, @Today)
then Value end) as [Today - 3],

max(case when DateStamp = dateadd(day,-2, @Today)
then Value end) as [Today - 2],

max(case when DateStamp = dateadd(day,-1, @Today)
then Value end) as [Today - 1],

max(case when DateStamp = @Today
then Value end) as [Today]

from MetricsData

GROUP BY Metric, Metrics

See this blog post for explanation of this technique

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/understanding-sql-server-2000-pivot

PluralSight Learning Library

 

[SarahMH]

Thank you so very much Markros!!

My sql was soooooo much more complicated and didn't work.

Is there any chance that the headings can dynamically be the date?

 

[markros]

Yes, it's possible using dynamic SQL. In SQL 2000 it's a bit more complex than in the SQL 2005+.

Also, where do you want to display the dynamic headings? If in the report, then it will be much simpler to just change them directly in the report according to the current date, but keep the fixed names of the fields in the output.

PluralSight Learning Library

 

[SarahMH]

Hi

My intention was to create a view but I don't think you cannot declare in a view - so I created a stored procedure which will run daily initially truncating the table then inserting the data from the base table. I am creating a webpage that will enable the user to update the value in the table. Because I am using this method, like you have kindly said I can easily create dynamic report/table headings.

Thank you so very much for your help in resolving my problem!