May 20, 2010 at 12:13 am
I have a table created with following structure:
TABLERESULT
SECTION COURSECODE P1 P2 P3 P4 P5.............P40
SOURCETABLE
ParameterVALUES SECTION COURSECODE
1 A AC1
2 A AC1
3 A AC1
4 A AC2
5 A AC2
6 B BC1
7 B BC2
8 B BCC1
9 B BCC2
10 B BCC2
11 B BCC2
12 B BCC2
I want output like this in TABLERESULT
SECTION COURSECODE P1 P2 P3 P4 P5.............P40
A AC1 1 2 3
A AC2 4 5
B BC1 6
B BC2 7
B BCC1 8
B BCC2 9 10 11 12
SOURCETABLE has around 2000 records like this. there are multiple sections and courses i want to write a query that can help me to insert the records into TABLERESULT in the manner as in above example. Please help
May 20, 2010 at 6:56 am
Try this:
INSERT INTO TABLERESULT
SELECT SECTION,
COURSECODE,
MAX(CASE WHEN rec_id = 1 THEN ParameterVALUES END) AS P1,
COALESCE(MAX(CASE WHEN rec_id = 2 THEN ParameterVALUES END), '') AS P2,
COALESCE(MAX(CASE WHEN rec_id = 3 THEN ParameterVALUES END), '') AS P3,
COALESCE(MAX(CASE WHEN rec_id = 4 THEN ParameterVALUES END), '') AS P4,
COALESCE(MAX(CASE WHEN rec_id = 5 THEN ParameterVALUES END), '') AS P5,
COALESCE(MAX(CASE WHEN rec_id = 6 THEN ParameterVALUES END), '') AS P6,
COALESCE(MAX(CASE WHEN rec_id = 7 THEN ParameterVALUES END), '') AS P7,
COALESCE(MAX(CASE WHEN rec_id = 8 THEN ParameterVALUES END), '') AS P8,
COALESCE(MAX(CASE WHEN rec_id = 9 THEN ParameterVALUES END), '') AS P9,
COALESCE(MAX(CASE WHEN rec_id = 10 THEN ParameterVALUES END), '') AS P10,
COALESCE(MAX(CASE WHEN rec_id = 11 THEN ParameterVALUES END), '') AS P11,
COALESCE(MAX(CASE WHEN rec_id = 12 THEN ParameterVALUES END), '') AS P12,
COALESCE(MAX(CASE WHEN rec_id = 13 THEN ParameterVALUES END), '') AS P13,
COALESCE(MAX(CASE WHEN rec_id = 14 THEN ParameterVALUES END), '') AS P14,
COALESCE(MAX(CASE WHEN rec_id = 15 THEN ParameterVALUES END), '') AS P15,
COALESCE(MAX(CASE WHEN rec_id = 16 THEN ParameterVALUES END), '') AS P16,
COALESCE(MAX(CASE WHEN rec_id = 17 THEN ParameterVALUES END), '') AS P17,
COALESCE(MAX(CASE WHEN rec_id = 18 THEN ParameterVALUES END), '') AS P18,
COALESCE(MAX(CASE WHEN rec_id = 19 THEN ParameterVALUES END), '') AS P19,
COALESCE(MAX(CASE WHEN rec_id = 20 THEN ParameterVALUES END), '') AS P20,
COALESCE(MAX(CASE WHEN rec_id = 21 THEN ParameterVALUES END), '') AS P21,
COALESCE(MAX(CASE WHEN rec_id = 22 THEN ParameterVALUES END), '') AS P22,
COALESCE(MAX(CASE WHEN rec_id = 23 THEN ParameterVALUES END), '') AS P23,
COALESCE(MAX(CASE WHEN rec_id = 24 THEN ParameterVALUES END), '') AS P24,
COALESCE(MAX(CASE WHEN rec_id = 25 THEN ParameterVALUES END), '') AS P25,
COALESCE(MAX(CASE WHEN rec_id = 26 THEN ParameterVALUES END), '') AS P26,
COALESCE(MAX(CASE WHEN rec_id = 27 THEN ParameterVALUES END), '') AS P27,
COALESCE(MAX(CASE WHEN rec_id = 28 THEN ParameterVALUES END), '') AS P28,
COALESCE(MAX(CASE WHEN rec_id = 29 THEN ParameterVALUES END), '') AS P29,
COALESCE(MAX(CASE WHEN rec_id = 30 THEN ParameterVALUES END), '') AS P30,
COALESCE(MAX(CASE WHEN rec_id = 31 THEN ParameterVALUES END), '') AS P31,
COALESCE(MAX(CASE WHEN rec_id = 32 THEN ParameterVALUES END), '') AS P32,
COALESCE(MAX(CASE WHEN rec_id = 33 THEN ParameterVALUES END), '') AS P33,
COALESCE(MAX(CASE WHEN rec_id = 34 THEN ParameterVALUES END), '') AS P34,
COALESCE(MAX(CASE WHEN rec_id = 35 THEN ParameterVALUES END), '') AS P35,
COALESCE(MAX(CASE WHEN rec_id = 36 THEN ParameterVALUES END), '') AS P36,
COALESCE(MAX(CASE WHEN rec_id = 37 THEN ParameterVALUES END), '') AS P37,
COALESCE(MAX(CASE WHEN rec_id = 38 THEN ParameterVALUES END), '') AS P38,
COALESCE(MAX(CASE WHEN rec_id = 39 THEN ParameterVALUES END), '') AS P39,
COALESCE(MAX(CASE WHEN rec_id = 40 THEN ParameterVALUES END), '') AS P4
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY SECTION, COURSECODE ORDER BY ParameterVALUES ASC) as rec_id
FROM SOURCETABLE)D
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply