Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
I have this data in my database and can display it easily on crystal report
ID	SUB.	GRADE
1	MATHS	D
1	ENGLISH	B-
1	SCIENCE	A
2	MATHS	A
2	ENGLISH	B
2	SCIENCE	B-
3	MATHS	B+
3	ENGLISH	A
3	SCIENCE	A
but I want it in this form
	MATHS	ENGLISH	SCIENCE
1	D	B-	A
2	A	B	B-
3	B+	A	A
pls can anybody help me on how I can achieve this>?
Posted 18-Apr-13 2:17am
Edited 18-Apr-13 2:49am
Maciej Los151.7K
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Try this:
DECLARE @tbl TABLE (ID INT,	[SUB.] NVARCHAR(30), GRADE NVARCHAR(5))
 
INSERT INTO @tbl (ID, [SUB.], GRADE)
VALUES(1, 'MATHS', 'D')
INSERT INTO @tbl (ID, [SUB.], GRADE)
VALUES(1, 'ENGLISH', 'B-')
INSERT INTO @tbl (ID, [SUB.], GRADE)
VALUES(1, 'SCIENCE', 'A')
INSERT INTO @tbl (ID, [SUB.], GRADE)
VALUES(2, 'MATHS', 'A')
INSERT INTO @tbl (ID, [SUB.], GRADE)
VALUES(2, 'ENGLISH', 'B')
INSERT INTO @tbl (ID, [SUB.], GRADE)
VALUES(2, 'SCIENCE', 'B-')
INSERT INTO @tbl (ID, [SUB.], GRADE)
VALUES(3, 'MATHS', 'B+')
INSERT INTO @tbl (ID, [SUB.], GRADE)
VALUES(3, 'ENGLISH', 'A')
INSERT INTO @tbl (ID, [SUB.], GRADE)
VALUES(3, 'SCIENCE', 'A')
 
--select from @tbl
SELECT t1.ID, MATHS, ENGLISH, SCIENCE 
FROM (
	SELECT ID, GRADE AS 'MATHS' --select only MATHS data
	FROM @tbl 
	WHERE [SUB.] = 'MATHS'
	) AS t1 INNER JOIN (
			SELECT ID, GRADE AS 'ENGLISH' --join ENGLISH data on ID
			FROM @tbl 
			WHERE [SUB.] = 'ENGLISH'
			) AS t2 ON t1.ID = t2.ID INNER JOIN (
					SELECT ID, GRADE AS 'SCIENCE' --join SCIENCE data on ID
					FROM @tbl 
					WHERE [SUB.] = 'SCIENCE'
				) AS t3 ON t2.ID = t3.ID
 
Result:
1	D	B-	A
2	A	B	B-
3	B+	A	A
 
Read about JOIN's[^]
To beeter understan, see this: Visual Representation of SQL Joins[^]
 
[EDIT #1]
Ok, if you want to fetch data in dynamic way, we need to do some trick. See example:
IF (OBJECT_ID('#tbl') IS NOT NULL) DROP TABLE #tbl
 
CREATE TABLE #tbl (ID INT,	[SUB.] NVARCHAR(30), GRADE NVARCHAR(5))
 
INSERT INTO #tbl (ID, [SUB.], GRADE)
VALUES(1, 'MATHS', 'D')
INSERT INTO #tbl (ID, [SUB.], GRADE)
VALUES(1, 'ENGLISH', 'B-')
INSERT INTO #tbl (ID, [SUB.], GRADE)
VALUES(1, 'SCIENCE', 'A')
INSERT INTO #tbl (ID, [SUB.], GRADE)
VALUES(2, 'MATHS', 'A')
INSERT INTO #tbl (ID, [SUB.], GRADE)
VALUES(2, 'ENGLISH', 'B')
INSERT INTO #tbl (ID, [SUB.], GRADE)
VALUES(2, 'SCIENCE', 'B-')
INSERT INTO #tbl (ID, [SUB.], GRADE)
VALUES(3, 'MATHS', 'B+')
INSERT INTO #tbl (ID, [SUB.], GRADE)
VALUES(3, 'ENGLISH', 'A')
INSERT INTO #tbl (ID, [SUB.], GRADE)
VALUES(3, 'SCIENCE', 'A')
 
-- for example, i fetch unique grades into second table

IF (OBJECT_ID('#Grades') IS NOT NULL) DROP TABLE #Grades
 
CREATE TABLE #Grades (IDG INT IDENTITY(1,1), GRADE NVARCHAR(5))
 
INSERT INTO #Grades (GRADE)
SELECT DISTINCT GRADE
FROM #tbl
 
--define dynamic columns for SUB.
DECLARE @cols NVARCHAR(200)
SET @cols = STUFF((SELECT DISTINCT '],[' + [SUB.]
					FROM #tbl
					ORDER BY '],[' + [SUB.]
			FOR XML PATH('')),1,2,'') + ']'
 
--define data table
DECLARE @dt NVARCHAR(2000)
 
SET @dt = 'SELECT t1.ID, t1.[SUB.], t2.IDG ' + 
		'FROM #tbl AS t1 INNER JOIN #Grades AS t2 ON t1.GRADE = t2.GRADE'
--EXEC(@dt)

--define pivot table
DECLARE @pt NVARCHAR(MAX)
 
SET @pt = 'SELECT ID, ' + @cols + ' ' +
'FROM (' + @dt + ') AS DT ' + 
'PIVOT(MAX(IDG) FOR [SUB.] IN(' + @cols + ')) AS PT '
 
EXEC(@pt)
 
--clean up ;)
DROP TABLE #Grades
DROP TABLE #tbl
 
Result:
ID   ENGLISH  MATHS   SCIENCE
1	3	5	1
2	2	1	3
3	1	4	1
 
Change the code to your needs Wink | ;)
[EDIT]
  Permalink  
v3
Comments
rosoftghana at 18-Apr-13 8:24am
   
pls I understand your solution but the subjects are dynamic, they can change anytime
Maciej Los at 18-Apr-13 8:35am
   
OK, i'll add a comments. Please, see another solution (in my opinion is more readible).
Maciej Los at 18-Apr-13 14:21pm
   
Dynamic columns added ;)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Another solution is to use temporary table:
--how to create @tbl? 
--see solution1
DECLARE @dst TABLE (ID INT , MATHS NVARCHAR(5) NULL, ENGLISH NVARCHAR(5) NULL, SCIENCE NVARCHAR(5) NULL)
 
--add maths
INSERT INTO @dst (ID, MATHS)
SELECT ID, GRADE AS 'MATHS'
FROM @tbl
WHERE [SUB.] = 'MATHS'
 
--uncomment this to check values
--SELECT *
--FROM @dst

--update on ID to add ENGLISH
UPDATE @dst SET t1.ENGLISH = t2.ENGLISH
FROM @dst AS t1 INNER JOIN (
	SELECT ID, GRADE AS 'ENGLISH'
	FROM @tbl 
	WHERE [SUB.] = 'ENGLISH'
	) AS t2 ON t1.ID = t2.ID
 
--uncomment this to check values
--SELECT *
--FROM @dst

--update on ID to add SCIENCE
UPDATE @dst SET t1.SCIENCE = t2.SCIENCE
FROM @dst AS t1 INNER JOIN (
	SELECT ID, GRADE AS 'SCIENCE'
	FROM @tbl 
	WHERE [SUB.] = 'SCIENCE'
	) AS t2 ON t1.ID = t2.ID
 
SELECT *
FROM @dst
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Maciej Los 315
1 OriginalGriff 233
2 Aajmot Sk 224
3 Richard MacCutchan 220
4 Marcin Kozub 210
0 OriginalGriff 7,853
1 Sergey Alexandrovich Kryukov 7,107
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,790


Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 18 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100