如何以数据透视表格形式显示水晶报表上的数据 [英] how to display data on crystal report in a pivot form
本文介绍了如何以数据透视表格形式显示水晶报表上的数据的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的数据库中有这些数据,可以在水晶报告上轻松显示
ID SUB。 GRADE
1 MATHS D
1 ENGLISH B-
1 SCIENCE A
2 MATHS A
2 ENGLISH B
2 SCIENCE B-
3数学B +
3英语A
3科学A
但是我想要这种形式
MATHS ENGLISH SCIENCE
1 D B- A
2 A B B-
3 B + A A
请参阅任何人帮助我如何实现这个>?
解决方案
试试这个:
DECLARE @ tbl TABLE (ID INT ,[SUB。] NVARCHAR ( 30 ),GRADE < span class =code-keyword> 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',< span class =code-string>' A')
INSERT INTO @ tbl (ID,[SUB。],GRADE)
VALUES ( 2 ,' ENGLISH',' B')
INSERT INTO @ tbl (ID,[SUB。],GRADE )
VALUES ( 2 ,' 科学',' 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')
- 从@tbl中选择
SELECT t1.ID,MATHS,ENGLISH,SCIENCE
FROM (
SELECT ID,GRADE AS ' MATHS' - 仅选择MATHS数据
FROM @tbl
WHERE [SUB。] = ' MATHS'
) AS t1 INNER JOIN (
SELECT ID,GRADE AS ' ENGLISH' - join ID上的ENGLISH数据
FROM @ tbl
WHERE [SUB。] = ' ENGLISH'
) AS t2 ON t1.ID = t2.ID INNER JOIN (
SELECT ID,GRADE AS ' 科学' - 加入ID上的科学数据
FROM@ tbl
WHERE [SUB。] = ' SCIENCE'
) AS t3 ON t2.ID = t3.ID
结果:
1 D B- A
2 A B B-
3 B + A A
阅读关于加入' [ ^ ]
要了解情况,请参阅: SQL连接的可视化表示 [ ^ ]
好的,如果你想以动态的方式获取数据,我们需要做一些技巧。参见示例:
IF ( OBJECT_ID (' #tbl') IS < span class =code-keyword> NOT NULL ) DROP TABLE #tbl
CREATE TABLE #tbl(ID INT ,[SUB。] NVARCHAR ( 30 ),GRADE NVARCHAR ( 5 ))
< span class =code-keyword> 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 ,' 科学',' B - ')
INSERT INTO #tbl(ID,[SUB。],GRADE)
VALUES ( 3 ,' M ATHS',' B +')
INSERT INTO #tbl(ID,[SUB。],GRADE)
VALUES ( 3 ,' ENGLISH',' A')
INSERT INTO #tbl(ID,[SUB。],GRADE)
VALUES ( 3 ,' SCIENCE',< span class =code-string>' A')
- - 例如,我在第二个表中获取唯一成绩
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
- 定义SUB的动态列。
DECLARE @cols NVARCHAR ( 200 )
SET @cols = STUFF(( SELECT DISTINCT ' ],[' + [SUB。]
FROM #tbl
ORDER BY ' ],[' + [SUB。]
FOR XML PATH(' ')), 1 , 2 ,' ')+ ' ]'
- 定义数据表
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)
- 定义数据透视表
DECLARE @ pt NVARCHAR (MAX)
SET @ pt = ' SELECT ID,' + @cols + ' ' +
' FROM(' + @ dt + ' )AS DT' +
' PIVOT(MAX(IDG)FOR [SUB。] IN(' + @cols + ' ))AS PT'
EXEC ( @ pt )
- 清理;)
DROP TABLE #Grades
DROP 表 #tbl
结果:
<前郎=文字> ID英语数学科学
1 3 5 1
2 2 1 3
3 1 4 1
根据需要更改代码;)
另一个解决方案是使用临时表:
- 如何创建@tbl?
- 请参阅solution1
DECLARE @ dst TABLE (ID INT ,MATHS NVARCHAR ( 5 ) NULL ,ENGLISH NVARCHAR ( 5 ) NULL ,SCIENCE NVARCHAR ( 5 ) NULL )
- 添加数学
< span class =code-keyword> INSERT INTO @ dst (ID,MATHS)
SELECT ID,GRADE AS ' MATHS'
FROM @ tbl
WHERE [SUB。] = ' MATHS'
- 取消注释以检查值
- SELECT *
- FROM @dst
- 更新ID以添加ENGLISH
更新 @ 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
- 取消注释以检查值
- SELECT *
- FROM @dst
- 更新ID以添加科学
更新 @ dst SET t1.SCIENCE = t2.SCIENCE
FROM @ dst AS t1 INNER JOIN (
SELECT ID,GRADE AS ' 科学
FROM @ tbl
WHERE [SUB。] = ' SCIENCE'
) AS t2 ON t1.ID = t2.ID
SELECT *
FROM @ dst < /跨度>
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>?
解决方案
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 ;)
[EDIT]
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
这篇关于如何以数据透视表格形式显示水晶报表上的数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文