在ms SQL中透视列 [英] Pivot columns in ms SQL

问题描述

我需要使用pivot从以下数据创建时间表：

和记录包含内部联接。我已尝试并提供代码但无法获取它

我希望这是足够的数据

I need to use pivot to create time table from the following data:
and records contain inner join.I have tried and provided code but not able to get it
I hope it is sufficient data

starttime endtime subjname dd
9            11    History Monday
11           13    Physics Tuesday

我正在使用此查询生成上面的列

I'm using this query to generate above columns

select 
    start_time, end_time, s.subj_name, tt.dd
from 
    time_table tt 
inner join 
    subj s on tt.subjid = s.subjid 

我可以使用下面的代码转动我的结果

I am able to pivot my result using below code

--Declare necessary variables
DECLARE   @SQLQuery AS NVARCHAR(MAX)
DECLARE   @PivotColumns AS NVARCHAR(MAX)

--Get unique values of pivot column  
SELECT @PivotColumns = COALESCE(@PivotColumns + ',','') + QUOTENAME(dd)
FROM (SELECT DISTINCT(dd)
      FROM time_table tt
      WHERE divid = 1 AND tt.active = 1) AS PivotExample

SELECT @PivotColumns

--Create the dynamic query with all the values for 
--pivot column at runtime
SET   @SQLQuery = 
    N'SELECT start_time,end_time, ' +   @PivotColumns + ' 
    FROM [dbo].[time_table]  
    PIVOT( SUM(subjid) 
          FOR dd IN (' + @PivotColumns + ')) AS P' 

SELECT   @SQLQuery

--Execute dynamic query
EXEC sp_executesql @SQLQuery

输出为

And getting output as

Starttime endtime Monday Tuesday Wednesday
9          11     7        8       null
11         13     null     7         9

这里我得到了主题，但我想要的主题名称与上述查询中显示的不同，请帮我解决这个问题。我试图给出max（）agreegate函数，因为它是一个varchar，但我想因为加入而得到错误

谢谢。

Here i am getting subjectid and but I want Subject Name which is in different as shown in above query,Help me out with this issue.I have tried to give max() agreegate function since it is an varchar but i guess getting error due to join
Thank you.

推荐答案

如果使用看起来像

SELECT * FROM 
(
    -- source query
) src
PIVOT
(
    -- pivot definition
)

而不是您使用的格式，然后您可以轻松添加连接

rather than the format you are using then you can easily add the join

--Create the dynamic query with all the values for 
--pivot column at runtime
SET   @SQLQuery = 
    N'SELECT * FROM
	(SELECT start_time,end_time,subj_name,dd
    FROM [dbo].[time_table] tt 
	INNER JOIN subj s on tt.subjid = s.subjid 
	) SRC
    PIVOT( MAX(subj_name) 
          FOR dd IN (' + @PivotColumns + ')) AS P' 

注意我正在使用 MAX（）而不是 SUM（）来获取课程名称

Notice I'm using MAX() rather than SUM() to get the course name

这篇关于在ms SQL中透视列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！