Code

CREATE TABLE [dbo].[ind_subject_scores]
(
	[pn_id] [char](9) NULL,
	[name] [char](20) NULL,
	[skill_id] [char](2) NULL,
	[test_level] [varchar](2) NULL,
	[subj score] [char](2) NULL,
	[class] [char](12) NULL,
	[lang] [char](2) NULL,
	[test_dt] datetime
	);

INSERT INTO [dbo].[ind_subject_scores] 
(
	[pn_id],
	[name],
	[skill_id] ,
	[test_level] ,
	[subj score], 
	[class],
	[lang] ,
	[test_dt]
	)
VALUES (
	'897841239'
	,'Justin bieber'
	,'1'
	,'L'
	,'1+'
	,null
	,'AD' 
	,'20140602'	
	);

INSERT INTO [dbo].[ind_subject_scores] 
(
	[pn_id],
	[name],
	[skill_id] ,
	[test_level] ,
	[subj score], 
	[class],
	[lang] ,
	[test_dt]
	)
VALUES (
	'897841239'
	,'Justin bieber'
	,'2'
	,'L'
	,'1+'
	,null
	,'AD' 
	,'20140528'	
	);


INSERT INTO [dbo].[ind_subject_scores] 
(
	[pn_id],
	[name],
	[skill_id] ,
	[test_level] ,
	[subj score], 
	[class],
	[lang] ,
	[test_dt]
	)
VALUES (
	'897841239'
	,'Justin bieber'
	,'2'
	,'L'
    ,'2+'
	,null
	,'AD' 
	,'20140820'	
	);
	
	
	----
	
	INSERT INTO [dbo].[ind_subject_scores] 
(
	[pn_id],
	[name],
	[skill_id] ,
	[test_level] ,
	[subj score], 
	[class],
	[lang] ,
	[test_dt]
	)
VALUES (
	'897841239'
	,'Justin bieber'
	,'1'
	,'L'
	,'3'
	,'21601DG00113'
	,'DG' 
	,'20140527'	
	);
	
	INSERT INTO [dbo].[ind_subject_scores] 
(
	[pn_id],
	[name],
	[skill_id] ,
	[test_level] ,
	[subj score], 
	[class],
	[lang] ,
	[test_dt]
	)
VALUES (
	'897841239'
	,'Justin bieber'
	,'1'
	,'L'
	,'2+'
	,'21601DG00113'
	,'DG' 
	,'20140819'	
	);
	
	

Problem: 
Reformatting table headers to the following columns such that skill_id = 1 is LC, skill_id=2 is RC, test_lvl=L is LR and V is VLR.  

pn_id |name| L LC | L LC test_dt | L RC | L RC test_dt |VR LC | VLR LC test_dt | VLR RC | VLR RC test_dt |class | lang 

The following code doesn't list all the records right. It should list 3 records here :/. Furthermore, theresults are wrong or doesnt match. what i could do differently to fix the code

	;WITH cte
AS (SELECT *
		,DENSE_RANK() OVER (
			PARTITION BY pn ORDER BY language_, rola, [Test level]	) AS Rn
	FROM 
	(
	Select pn_id as pn, name,skill_id as rola,test_level as [Test level],[subj score],lang as language_,test_dt from dbo.ind_subject_scores
	)x
	)
	
	SELECT MAX(pn) as pn, MAX(name) as name, MAX(language_) as [lang],
	MAX(CASE WHEN Rn = 2 THEN CASE when [Test level]='L' THEN [subj score] end END) as [LR RC Score],
MAX(CASE WHEN Rn = 2 THEN CASE when [Test level]='L' THEN test_dt end END) as [LR RC Test Date],

MAX(CASE WHEN Rn = 1 THEN CASE when [Test level]='L' THEN [subj score] end END) as [LR LC Score],
MAX(CASE WHEN Rn = 1 THEN CASE when [Test level]='L' THEN test_dt end END) as [LR LC Test Date],

MAX(CASE WHEN Rn = 1 THEN CASE when [Test level]='V' THEN [subj score] end END) as [VLR LC Score],
MAX(CASE WHEN Rn = 1 THEN CASE when [Test level]='V' THEN test_dt end END) as [VLR LC Test Date],

MAX(CASE WHEN Rn = 2 THEN CASE when [Test level]='V' THEN [subj score] end END) as [VLR RC Score],
MAX(CASE WHEN Rn = 2 THEN CASE when [Test level]='V' THEN test_dt end END) as [VLR RC Test Date]
	from cte
	group by language_,pn

• Edited by Sandra VO Friday, January 30, 2015 11:28 PM

Sounds like this is what you're after

;With CTE
	AS
	(
	SELECT *,CASE WHEN  skill_id = 2 AND [test_level]='L' THEN 'LR RC'
	WHEN skill_id = 1 AND [test_level]='L' THEN 'LR LC'
	WHEN skill_id = 1 AND [test_level]='V' THEN 'VLR LC'
	WHEN skill_id = 2 AND [test_level]='V' THEN 'VLR RC'
	END AS Category
	FROM dbo.ind_subject_scores
	)
	
	SELECT [pn_id],
	[name],
	MAX(CASE WHEN Category = 'LR RC' THEN [subj score] END) AS [LR RC],
	MAX(CASE WHEN Category = 'LR RC' THEN [test_dt] END) AS [LR RC test_dt],
	MAX(CASE WHEN Category = 'LR LC' THEN [subj score] END) AS [LR LC],
	MAX(CASE WHEN Category = 'LR LC' THEN [test_dt] END) AS [LR LC test_dt],
	MAX(CASE WHEN Category = 'VLR RC' THEN [subj score] END) AS [VLR RC],
	MAX(CASE WHEN Category = 'VLR RC' THEN [test_dt] END) AS [VLR RC test_dt],
	MAX(CASE WHEN Category = 'VLR LC' THEN [subj score] END) AS [VLR LC],
	MAX(CASE WHEN Category = 'VLR LC' THEN [test_dt] END) AS [VLR LC test_dt],
	lang,class
	FROM	CTE
	GROUP BY [pn_id],
	[name],
	Skill_id,lang,class
	

Are you looking for below query,

SELECT * FROM (
	SELECT pn_id,name,class,lang,[subj score],[test_dt], CASE
	WHEN skill_id = 2 AND [test_level]='L' THEN 'LR RC Score'
	WHEN skill_id = 1 AND [test_level]='L' THEN 'LR LC Score'
	WHEN skill_id = 1 AND [test_level]='V' THEN 'VLR LC Score'
	WHEN skill_id = 2 AND [test_level]='V' THEN 'VLR RC Score'
	END [subj scores],
	CASE
	WHEN skill_id = 2 AND [test_level]='L' THEN 'LR RC test_dt'
	WHEN skill_id = 1 AND [test_level]='L' THEN 'LR LC test_dt'
	WHEN skill_id = 1 AND [test_level]='V' THEN 'VLR LC test_dt'
	WHEN skill_id = 2 AND [test_level]='V' THEN 'VLR RC test_dt'
	END [test_dt_]
	FROM dbo.ind_subject_scores
) X
PIVOT
(MAX([subj score]) FOR [subj scores] IN ([LR RC Score],[LR LC Score],[VLR LC Score],[VLR RC Score])) pvt
PIVOT
(MAX([test_dt]) FOR [test_dt_] IN ([LR RC test_dt],[LR LC test_dt],[VLR LC test_dt],[VLR RC test_dt])) pvt1