列值需要设置为另一个列标题 [英] column value need to set as another column header

问题描述

先生，这是我的sql： -

Sir, Here is my sql:-

SELECT   
CONVERT(varchar, COUNT(CASE WHEN studentatt.status = 'P' THEN 1 END) +  ISNULL(MAX(specialAttendance.AttObt), 0) +
ISNULL(dbo.udf_ExtraAttendance(Student.StudentID,CONVERT(DATETIME, '01/Apr/2014', 102),
CONVERT(DATETIME, getdate(), 102)), 0))  as [Present],
CONVERT (varchar, COUNT(StudentAtt.Status) + ISNULL(MAX(specialAttendance.AttMax), 0)) as [Total]
FROM   Student
LEFT OUTER JOIN  specialAttendance  ON Student.StudentID = specialAttendance.StudentID
LEFT OUTER JOIN StudentAtt ON StudentAtt.StudentID = Student.StudentID
WHERE     (StudentAtt.Dated BETWEEN CONVERT(DATETIME, '01/Apr/2014', 102)   AND CONVERT(DATETIME, getdate(), 102))
AND (teach_type = 'L' OR teach_type = 'T'  or teach_type = 'P') AND (StudentAtt.SubjectID IN (2))
GROUP BY  Student.Roll_No, Student.Student, Student.StudentID, Student.CourseID,  Student.BatchID,student.classesid,
specialAttendance.AttObt,  dbo.udf_ExtraAttendance(Student.StudentID, CONVERT(DATETIME, '01/Apr/2014', 102),
CONVERT(DATETIME, getdate(), 102)), Student.Sem         HAVING (Student.CourseID = 1492)   And
(Student.Sem = 5) AND (Student.classesid = 17)

查询结果如下： -



[现在] - [总计]

9 - 20

5 - 20

6 - 20

7 - 20





i需要的结果如下： -



[礼物（20）]

9

5

6

7



我怎么能这样做先生。

query result is like:-

[Present] -- [Total]
9 -- 20
5 -- 20
6 -- 20
7 -- 20


i need the result like:-

[Present(20)]
9
5
6
7

how can i do it sir.

推荐答案

技术上可行，虽然我怀疑这是不错的做法：



- 将Total的结果输入变量：

It is technically possible, although I doubt if it's good practice:

- Get the result for Total into a variable:

DECLARE @total INT = (SELECT ... FROM ...)

- 使用动态SQL创建临时表。确保创建一个全局临时表（使用##前缀），否则它将不可用于脚本的其余部分

- Create a temporary table using dynamic SQL. Make sure to create a global temp table (using the ## prefix), or it will not be available for the rest of the script

DECLARE @sql VARCHAR(MAX)

SET @sql = 'CREATE TABLE ##temptable (Present[' + @total + '] INT NOT NULL) ON [PRIMARY]'

EXECUTE (@sql);
GO

- 运行原始查询并将结果放入临时表

- Run your original query and put the results into the temp table

SELECT .... INTO ##temptable FROM ..........

- 从temptable中选择你的结果：

- Select your results from the temptable:

SELECT * FROM ##temptable

| Present[20] |
|      9      |
|      5      |
|      6      |
|      7      |

这篇关于列值需要设置为另一个列标题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！