Sql Server 2008表行到表列的转换 [英] Sql Server 2008 table row to table column conversion
问题描述
全部好，
我在Sql server 2008中有一个表，它由Sql脚本动态填充，此脚本可以插入100到100000或更多行这张桌子。我的问题是我需要一个字段显示为表格列，另一个字段显示为值。
这里是详细信息
表名：班级
字段：
班级varchar
Rollno varchar
科目varchar
Marks varchar
从班级中选择*将以这种方式显示
Class Rollno主题标记
1 1 eng 56
1 2 eng 70
1 3 eng 80
1 4 eng 10
1 1数学67
1 2数学89
1 3数学90
1 4数学45
1 1 Sci 45
1 2 Sci 56
1 3 Sci 76
1 4 Sci 89
2 1 eng 68
2 2 eng 65
2 1 Sci 70
2 2 Sci 65
3 1法语89
3 1西班牙语55
3 1英语65
3 2法语56
3 2西班牙语24
3 2英语78
3 3法语65
3 3西班牙语34
3 3英语56
可以有n条记录达到100000多行
我需要以这种方式显示它：
班级Rollno Eng数学科学法语西班牙语
1 1 56 67 45
1 2 70 89 56
1 3 80 90 76
1 4 10 45 89
2 1 68 70
2 2 65 65
3 1 65 89 55
3 2 78 56 24
3 3 56 65 34
我试过Pivot并且它对我不起作用，可能是我用错误的方式做了
我要求你帮我解决这个问题紧急
问候，
Aspboomer
HI all,
I have a table in Sql server 2008 which is dynamically populated by a Sql script , this script may insert 100 to 100000 or more rows to this table. my issue is I need one field to be displayed as table column and the other as it value.
here are the details
Table Name: Class
Fields:
Class varchar
Rollno varchar
Subject varchar
Marks varchar
Select * from class will display in this way
Class Rollno Subject Marks
1 1 eng 56
1 2 eng 70
1 3 eng 80
1 4 eng 10
1 1 maths 67
1 2 maths 89
1 3 maths 90
1 4 maths 45
1 1 Sci 45
1 2 Sci 56
1 3 Sci 76
1 4 Sci 89
2 1 eng 68
2 2 eng 65
2 1 Sci 70
2 2 Sci 65
3 1 French 89
3 1 Spanish 55
3 1 English 65
3 2 French 56
3 2 Spanish 24
3 2 English 78
3 3 French 65
3 3 Spanish 34
3 3 English 56
could have n number of records reaching 100000 plus more rows
I need to display it in this fashion:
Class Rollno Eng Maths Sci French Spanish
1 1 56 67 45
1 2 70 89 56
1 3 80 90 76
1 4 10 45 89
2 1 68 70
2 2 65 65
3 1 65 89 55
3 2 78 56 24
3 3 56 65 34
I have tried Pivot and it did not work for me , may be I did it in a wrong way
I request you to help me with this issue as its urgent
regards,
Aspboomer
推荐答案
DECLARE @tbl AS TABLE (class INT, rollno INT, Subject VARCHAR (100), MARK INT)
INSERT INTO @tbl
VALUES
(1, 1, 'eng', 56), (1, 2, 'eng', 70),
(1, 3, 'eng', 80), (1, 4, 'eng', 10),
(1, 1, 'maths', 67), (1, 2, 'maths', 89),
(1, 3, 'maths', 90), (1, 4, 'maths', 45),
(1, 1, 'Sci', 45), (1, 2, 'Sci', 56),
(1, 3, 'Sci', 76), (1, 4, 'Sci', 89),
(2, 1, 'eng', 68), (2, 2, 'eng', 65),
(2, 1, 'Sci', 70), (2, 2, 'Sci', 65)
SELECT Class, Rollno ,
sum(CASE WHEN SUBJECT='Eng' THEN MARK ELSE 0 END) AS Eng,
sum(CASE WHEN SUBJECT='Sci' THEN MARK ELSE 0 END) AS Science,
sum(CASE WHEN SUBJECT='Maths' THEN MARK ELSE 0 END) AS Maths
FROM @tbl
GROUP BY class ,rollno
根据您的要求在案例条件中添加主题
add the subjects in the case condition as per your requirement
请检查...
你可以用Pivot做它..但我使用这种方法进行透视。
我正在使用#Temp表执行查询
-------------------------------- --------------------------------- -----------------------------
Please Check...
You Can Do It With Pivot.. But I Use This Method For Pivoting.
I Am Using #Temp Table To Execute Query
----------------------------------------------------------------------------------------------
--Select * into #Temp
--From
--(
--SELECT 1 Class,
-- 1 Rollno,
-- 'eng' Subject,
-- 56 Marks
--UNION ALL
--SELECT 1,
-- 2,
-- 'eng',
-- 70
--UNION ALL
--SELECT 1,
-- 3,
-- 'eng',
-- 80
--UNION ALL
--SELECT 1,
-- 4,
-- 'eng',
-- 10
--UNION ALL
--SELECT 1,
-- 1,
-- 'maths',
-- 67
--UNION ALL
--SELECT 1,
-- 2,
-- 'maths',
-- 89
--UNION ALL
--SELECT 1,
-- 3,
-- 'maths',
-- 90
--UNION ALL
--SELECT 1,
-- 4,
-- 'maths',
-- 45
--UNION ALL
--SELECT 1,
-- 1,
-- 'Sci',
-- 45
--UNION ALL
--SELECT 1,
-- 2,
-- 'Sci',
-- 56
--UNION ALL
--SELECT 1,
-- 3,
-- 'Sci',
-- 76
--UNION ALL
--SELECT 1,
-- 4,
-- 'Sci',
-- 89
--UNION ALL
--SELECT 2,
-- 1,
-- 'eng',
-- 68
--UNION ALL
--SELECT 2,
-- 2,
-- 'eng',
-- 65
--UNION ALL
--SELECT 2,
-- 1,
-- 'Sci',
-- 70
--UNION ALL
--SELECT 2,
-- 2,
-- 'Sci',
-- 65
--UNION ALL
--SELECT 3,
-- 1,
-- 'French',
-- 89
--UNION ALL
--SELECT 3,
-- 1,
-- 'Spanish',
-- 55
--UNION ALL
--SELECT 3,
-- 1,
-- 'eng',
-- 65
--UNION ALL
--SELECT 3,
-- 2,
-- 'French',
-- 56
--UNION ALL
--SELECT 3,
-- 2,
-- 'Spanish',
-- 24
--UNION ALL
--SELECT 3,
-- 2,
-- 'eng',
-- 78
--UNION ALL
--SELECT 3,
-- 3,
-- 'French',
-- 65
--UNION ALL
--SELECT 3,
-- 3,
-- 'Spanish',
-- 34
--UNION ALL
--SELECT 3,
-- 3,
-- 'eng',
-- 56
--)X
Select * from #Temp M
Select Distinct Class,RollNo,
IsNull((Select Marks from #Temp S Where S.Class = M.Class And S.RollNo = M.RollNo And Subject = 'Eng'),0) Eng
,IsNull((Select Marks from #Temp S Where S.Class = M.Class And S.RollNo = M.RollNo And Subject = 'Maths'),0) Maths
,IsNull((Select Marks from #Temp S Where S.Class = M.Class And S.RollNo = M.RollNo And Subject = 'Sci'),0) Sci
,IsNull((Select Marks from #Temp S Where S.Class = M.Class And S.RollNo = M.RollNo And Subject = 'French'),0) French
,IsNull((Select Marks from #Temp S Where S.Class = M.Class And S.RollNo = M.RollNo And Subject = 'Spanish'),0) Spanish
from #Temp M
这篇关于Sql Server 2008表行到表列的转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
