How to make additional columns on pivot of sql table in SQL Server 2005?

You need to combine the labels before the PIVOT. The column list also needs to take account of the cross join of label possibilities: SET NOCOUNT ON ; DECLARE @pivot_cols AS varchar(max) ; DECLARE @src AS TABLE ( ID int NOT NULL ,Label varchar(14) NOT NULL ,Numerator int NOT NULL ,Denominator int NOT NULL ,Ratio decimal(5, 4) NOT NULL ) ; DECLARE @label_order AS TABLE ( Label varchar(14) NOT NULL ,Sort int NOT NULL ) DECLARE @sub_label_order AS TABLE ( SubLabel varchar(14) NOT NULL ,Sort int NOT NULL ) INSERT INTO @src VALUES (1, 'LabelNameOne', 41, 10, 4.1) ; INSERT INTO @src VALUES (1, 'LabelNameTwo', 0, 0, 0) ; INSERT INTO @src VALUES (1, 'LabelNameThree', 21, 10, 2.1) ; INSERT INTO @src VALUES (1, 'LabelNameFour', 15, 10, 1.5) ; INSERT INTO @src VALUES (2, 'LabelNameOne', 19, 19, 1) ; INSERT INTO @src VALUES (2, 'LabelNameTwo', 0, 0, 0) ; INSERT INTO @src VALUES (2, 'LabelNameThree', 15, 16, 0.9375) ; INSERT INTO @src VALUES (2, 'LabelNameFive', 19, 19, 1) ; INSERT INTO @src VALUES (2, 'LabelNameSix', 17, 17, 1) ; INSERT INTO @src VALUES (3, 'LabelNameOne', 12, 12, 1) ; INSERT INTO @src VALUES (3, 'LabelNameTwo', 0, 0, 0) ; INSERT INTO @src VALUES (3, 'LabelNameThree', 11, 12, 0.9167) ; INSERT INTO @src VALUES (3, 'LabelNameFour', 12, 12, 1) ; INSERT INTO @src VALUES (3, 'LabelNameSix', 0, 1, 0) ; INSERT INTO @label_order VALUES ('LabelNameOne', 1) ; INSERT INTO @label_order VALUES ('LabelNameTwo', 2) ; INSERT INTO @label_order VALUES ('LabelNameThree', 3) ; INSERT INTO @label_order VALUES ('LabelNameFour', 4) ; INSERT INTO @label_order VALUES ('LabelNameFive', 5) ; INSERT INTO @label_order VALUES ('LabelNameSix', 6) ; INSERT INTO @sub_label_order VALUES ('Numerator', 1) ; INSERT INTO @sub_label_order VALUES ('Denominator', 2) ; INSERT INTO @sub_label_order VALUES ('Ratio', 3) ; WITH Labels AS ( SELECT DISTINCT src. Label + sublabel.

SubLabel AS Label ,ISNULL(label_order. Sort, 0) AS Sort ,ISNULL(sublabel. Sort, 0) AS SubSort FROM @src AS src CROSS JOIN @sub_label_order AS sublabel LEFT JOIN @label_order AS label_order ON src.

Label = label_order. Label ) SELECT @pivot_cols = COALESCE(@pivot_cols + ',', '') + QUOTENAME(Label, '') FROM Labels ORDER BY Sort ,SubSort ,Label ; DECLARE @template AS varchar(max) ; SET @template = ' DECLARE @src AS TABLE ( ID int NOT NULL ,Label varchar(14) NOT NULL ,Numerator int NOT NULL ,Denominator int NOT NULL ,Ratio decimal(5, 4) NOT NULL ) ; INSERT INTO @src VALUES (1, ''LabelNameOne'', 41, 10, 4.1) ; INSERT INTO @src VALUES (1, ''LabelNameTwo'', 0, 0, 0) ; INSERT INTO @src VALUES (1, ''LabelNameThree'', 21, 10, 2.1) ; INSERT INTO @src VALUES (1, ''LabelNameFour'', 15, 10, 1.5) ; INSERT INTO @src VALUES (2, ''LabelNameOne'', 19, 19, 1) ; INSERT INTO @src VALUES (2, ''LabelNameTwo'', 0, 0, 0) ; INSERT INTO @src VALUES (2, ''LabelNameThree'', 15, 16, 0.9375) ; INSERT INTO @src VALUES (2, ''LabelNameFive'', 19, 19, 1) ; INSERT INTO @src VALUES (2, ''LabelNameSix'', 17, 17, 1) ; INSERT INTO @src VALUES (3, ''LabelNameOne'', 12, 12, 1) ; INSERT INTO @src VALUES (3, ''LabelNameTwo'', 0, 0, 0) ; INSERT INTO @src VALUES (3, ''LabelNameThree'', 11, 12, 0.9167) ; INSERT INTO @src VALUES (3, ''LabelNameFour'', 12, 12, 1) ; INSERT INTO @src VALUES (3, ''LabelNameSix'', 0, 1, 0) ; WITH src_conformed AS ( SELECT ID ,Label ,CAST (Numerator AS decimal(10, 4)) AS Numerator ,CAST (Denominator AS decimal(10, 4)) AS Denominator ,CAST (Ratio AS decimal(10, 4)) AS Ratio FROM @src ), UNPIVOTED AS ( SELECT * FROM src_conformed UNPIVOT ( Val FOR Col IN (Numerator, Denominator, Ratio) ) AS unpvt ) ,COMBINED AS (SELECT ID, Label + Col AS NewLabel, Val FROM UNPIVOTED) SELECT * FROM COMBINED PIVOT ( SUM(Val) FOR NewLabel IN ({@pivot_cols}) ) AS pvt ORDER BY ID ;' ; SET @template = REPLACE(@template, '{@pivot_cols}', @pivot_cols) ; EXEC (@template).

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.