Reply to: sql server question

[EternalOptimist]

my solution

USE [FRB_NWCO]
GO
/****** Object: StoredProcedure [dbo].[CPUpdatePivot] Script Date: 03/13/2008 13:56:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Eternal Optimist>
-- Create date: <11 March 2008>
-- Description: <Updates the CP temp table using a pivot>
-- =============================================
ALTER PROCEDURE [dbo].[CPUpdatePivot]
-- Add the parameters for the stored procedure here

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

--drop table #TblCriticalP

select c.level1,
c.level2,
c.level3,
c.designref,
c.Colour,
c.Supplier,
c.Fabricref,
c.STEP,
c.StatusBits,
Coalesce(c.[ActualDate],c.[ProposedDate]) as myDate
into #TblCriticalP
FROM tblCriticalPath c
join tempTblCriticalPathView as t on
c.level1 = t.level1 and
c.level2 = t.level2 and
c.[level3] = t.[level3] and
c.[designref] = t.[designref] and
c.[Colour] = t.[Colour] and
c.[Supplier] = t.[Supplier] and
c.[Fabricref] = t.[Fabricref]

--update status

update tempTblCriticalPathView
set tempTblCriticalPathView.f01 = p.[1],
tempTblCriticalPathView.f02 = p.[2],
tempTblCriticalPathView.f03 = p.[3],
tempTblCriticalPathView.f04 = p.[4],
tempTblCriticalPathView.f05 = p.[5],
tempTblCriticalPathView.f06 = p.[6],
tempTblCriticalPathView.f07 = p.[7],
tempTblCriticalPathView.f08 = p.[8],
tempTblCriticalPathView.f09 = p.[9],
tempTblCriticalPathView.f10 = p.[10],
tempTblCriticalPathView.f11 = p.[11],
tempTblCriticalPathView.f12 = p.[12],
tempTblCriticalPathView.f13 = p.[13],
tempTblCriticalPathView.f14 = p.[14],
tempTblCriticalPathView.f15 = p.[15],
tempTblCriticalPathView.f16 = p.[16],
tempTblCriticalPathView.f17 = p.[17],
tempTblCriticalPathView.f18 = p.[18],
tempTblCriticalPathView.f19 = p.[19],
tempTblCriticalPathView.f20 = p.[20],
tempTblCriticalPathView.f21 = p.[21],
tempTblCriticalPathView.f22 = p.[22],
tempTblCriticalPathView.f23 = p.[23],
tempTblCriticalPathView.f24 = p.[24],
tempTblCriticalPathView.f25 = p.[25],
tempTblCriticalPathView.f26 = p.[26],
tempTblCriticalPathView.f27 = p.[27],
tempTblCriticalPathView.f28 = p.[28],
tempTblCriticalPathView.f29 = p.[29],
tempTblCriticalPathView.f30 = p.[30],
tempTblCriticalPathView.f31 = p.[31],
tempTblCriticalPathView.f32 = p.[32],
tempTblCriticalPathView.f33 = p.[33],
tempTblCriticalPathView.f34 = p.[34],
tempTblCriticalPathView.f35 = p.[35],
tempTblCriticalPathView.f36 = p.[36],
tempTblCriticalPathView.f37 = p.[37],
tempTblCriticalPathView.f38 = p.[38],
tempTblCriticalPathView.f39 = p.[39],
tempTblCriticalPathView.f40 = p.[40],
tempTblCriticalPathView.f41 = p.[41],
tempTblCriticalPathView.f42 = p.[42],
tempTblCriticalPathView.f43 = p.[43],
tempTblCriticalPathView.f44 = p.[44],
tempTblCriticalPathView.f45 = p.[45],
tempTblCriticalPathView.f46 = p.[46],
tempTblCriticalPathView.f47 = p.[47],
tempTblCriticalPathView.f48 = p.[48],
tempTblCriticalPathView.f49 = p.[49],
tempTblCriticalPathView.f50 = p.[50],
tempTblCriticalPathView.f51 = p.[51],
tempTblCriticalPathView.f52 = p.[52],
tempTblCriticalPathView.f53 = p.[53],
tempTblCriticalPathView.f54 = p.[54],
tempTblCriticalPathView.f55 = p.[55],
tempTblCriticalPathView.f56 = p.[56],
tempTblCriticalPathView.f57 = p.[57],
tempTblCriticalPathView.f58 = p.[58],
tempTblCriticalPathView.f59 = p.[59],
tempTblCriticalPathView.f60 = p.[60],
tempTblCriticalPathView.f61 = p.[61],
tempTblCriticalPathView.f62 = p.[62],
tempTblCriticalPathView.f63 = p.[63],
tempTblCriticalPathView.f64 = p.[64],
tempTblCriticalPathView.f65 = p.[65],
tempTblCriticalPathView.f66 = p.[66],
tempTblCriticalPathView.f67 = p.[67],
tempTblCriticalPathView.f68 = p.[68],
tempTblCriticalPathView.f69 = p.[69],
tempTblCriticalPathView.f70 = p.[70],
tempTblCriticalPathView.f71 = p.[71],
tempTblCriticalPathView.f72 = p.[72],
tempTblCriticalPathView.f73 = p.[73],
tempTblCriticalPathView.f74 = p.[74],
tempTblCriticalPathView.f75 = p.[75]

FROM #TblCriticalP
PIVOT (max([#TblCriticalP].[statusbits]) FOR [#TblCriticalP].[step] IN (
[0],[1],[2],[3],[4],[5],[6],[7],[8],[9],
[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],
[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],
[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],
[40],[41],[42],[43],[44],[45],[46],[47],[48],[49],
[50],[51],[52],[53],[54],[55],[56],[57],[58],[59],
[60],[61],[62],[63],[64],[65],[66],[67],[68],[69],
[70],[71],[72],[73],[74],[75]
)) p
join tempTblCriticalPathView z on
p.level1 = z.level1 and
p.level2 = z.level2 and
p.[level3] = z.[level3] and
p.[designref] = z.[designref] and
p.[Colour] = z.[Colour] and
p.[Supplier] = z.[Supplier] and
p.[Fabricref] = z.[Fabricref]




END

[Spacecadet]

Ahh, thanks for the info. i have thought about getting one at home to experiment with but always thought it would be too expensive to bother. Do I need to set up anything special, hardware wise, to use it?

You will need XP pro or a windows server 2000 or higher installation.
I've got XP Pro and it works fine, even have a report server publishing reports.

I agree, Sir Edward, it's not the best place to be pivoting etc. and it should be done in cubes etc etc. In the past I have also used Access via passthru queries to do this sort of analysis and reports generation

I agree with that too, but sometimes it is unavoidable or simply, due to the system, it is the logical place and knowing what tools are available to you is invaluble

[MrRobin]

Ahh, thanks for the info. i have thought about getting one at home to experiment with but always thought it would be too expensive to bother. Do I need to set up anything special, hardware wise, to use it?

I agree, Sir Edward, it's not the best place to be pivoting etc. and it should be done in cubes etc etc. In the past I have also used Access via passthru queries to do this sort of analysis and reports generation

[Spacecadet]

identical

Thanks

are you spell checking to help your post count recover?

[TheFaQQer]

tsk

You can get the developer edition of 2005 for about £50
and it is almost identicle to the full enterprise ediition and includes SSAS, SSRS, SSIS
http://www.amazon.co.uk/Server-Devel...5326859&sr=8-1


Beta versions of 2008 are available to download for free from MS

If you're doing a lot of SQL Server work then I can't recommend getting your own copy enough for experimenting with

identical

[Spacecadet]

tsk

You can get the developer edition of 2005 for about £50
and it is almost identicle to the full enterprise ediition and includes SSAS, SSRS, SSIS
http://www.amazon.co.uk/Server-Devel...5326859&sr=8-1


Beta versions of 2008 are available to download for free from MS

If you're doing a lot of SQL Server work then I can't recommend getting your own copy enough for experimenting with

[Sir_Edward_Matheson]

Ooo, groovey. I shall bear it in mind when I work somewhere that have finally upgraded!

You can of course use case...when...then...else to create a custom pivot function. I did this long ago when I was young and naïve. I am now firmly of the opinion that all pivoting should take place using client-side tools. If you really need to pivot your data you should really be re-designing the schema or creating some BI cubes.

[MrRobin]

you don't have your own SQL Server installations?

Nope.

[Spacecadet]

Ooo, groovey. I shall bear it in mind when I work somewhere that have finally upgraded!

you don't have your own SQL Server installations?

[MrRobin]

yup its new...
http://www.devx.com/dbzone/Article/28165

Ooo, groovey. I shall bear it in mind when I work somewhere that have finally upgraded!

[Spacecadet]

How do you pivot tables in SQL server? Was it a new feature in the 2005 version?

I've used a crosstab stored procedure I found on t'intertron before but it was quite fiddly.

yup its new...
http://www.devx.com/dbzone/Article/28165

[MrRobin]

How do you pivot tables in SQL server? Was it a new feature in the 2005 version?

I've used a crosstab stored procedure I found on t'intertron before but it was quite fiddly.

[Spacecadet]

you going to post your solution?

[EternalOptimist]

hey, pivot worked fine.



thanks guys

[MrRobin]

Ahh makes much more sense now...

Set up a loop on x to

Update ("B.F" + x)
where A.id = B.id and A.stepvalue = x

x goes from 1 to max(stepvalue)