12,064,498 members (46,249 online)
Rate this:
See more:
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 Los213.7K
v2

Rate this:

## 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```

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

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 ' +
--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 #tbl```

Result:
```ID   ENGLISH  MATHS   SCIENCE
1	3	5	1
2	2	1	3
3	1	4	1```

Change the code to your needs
[EDIT]
v3
rosoftghana 18-Apr-13 8:24am

pls I understand your solution but the subjects are dynamic, they can change anytime
Maciej Los 18-Apr-13 8:35am

Maciej Los 18-Apr-13 14:21pm

Rate this:

## 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)

INSERT INTO @dst (ID, 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 (
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 (
FROM @tbl
WHERE [SUB.] = 'SCIENCE'
) AS t2 ON t1.ID = t2.ID

SELECT *
FROM @dst```

Top Experts
Last 24hrsThis month
 d@nish 200 CPallini 175 CHill60 120 OriginalGriff 74 BillWoodruff 55
 Dave Kreskowiak 1,837 Richard MacCutchan 1,250 d@nish 980 OriginalGriff 874 CPallini 750