I've actually done one or two successful PIVOTs, but I do it so infrequently, that I always struggle with it. The script at the bottom creates a table of data that produces rows like
0 to 3 (14) 10 50 0
4 to 6 (23) 17.2 52 4
7 to 9 (5) 3 65 7
'>9 (14) 10 50 9
What I want is rows like below so I can graph the averages in Crystal Reports.
0 to 3 (14) 4 to 6 (23) 7 to 9 (5) >9 (14)
AvgStart 10 17.2 3 10
AvgEnd 50 52 65 50
One thing that could cause problems is the value in parentheses. In the case of '0 to 3' this is the number of people in that group. So while there will always be 4 groups that are 0 to 3, 4 to 6, 7 to 9, and >9, the way I build the data table, the 'labels' (column headings after the pivot) will change. I can remove it so the 4 groups are always the same, but the data is useful.
Greg
DROP TABLE WellnessReport
CREATE TABLE [dbo].[WellnessReport](
[GroupName] varchar NOT NULL,
[AvgStart] [float] NULL,
[AvgEnd] [float] NULL,
[GroupType] [int] NULL
) ON [PRIMARY]
INSERT INTO [dbo].[WellnessReport] ([GroupName],[AvgStart],[AvgEnd],[GroupType])
VALUES ('0 to 3 (14)' ,10,50,0)
INSERT INTO [dbo].[WellnessReport] ([GroupName],[AvgStart],[AvgEnd],[GroupType])
VALUES ('4 to 6 (23)' ,17.2,52,4)
INSERT INTO [dbo].[WellnessReport] ([GroupName],[AvgStart],[AvgEnd],[GroupType])
VALUES ('7 to 9 (5)' ,3,65,7)
INSERT INTO [dbo].[WellnessReport] ([GroupName],[AvgStart],[AvgEnd],[GroupType])
VALUES ('>9 (14)' ,10,50,9)
SELECT * FROM [WellnessReport]