在Sql Server中进行透视 [英] Pivoting in Sql Server

问题描述

亲爱的所有人，



我有一张这样的桌子：

Dear All,

I am having a table like this :

SNO EmpCode Name    Day OT_Day  Date                WorkingHours    OT Hours
1   338     A       1   1_OT    Jul  1 2013 12:00AM 8:51            1:15
2   338     A       2   2_OT    Jul  2 2013 12:00AM 9:14            1:15
3   338     A       3   3_OT    Jul  3 2013 12:00AM 8:51            1:21
4   338     A       4   4_OT    Jul  4 2013 12:00AM 8:44            1:16
5   338     A       5   5_OT    Jul  5 2013 12:00AM 8:42            1:15
6   338     A       6   6_OT    Jul  6 2013 12:00AM 2:07            1:16

现在使用旋转我想生成如下输出：

Now using pivoting I want to generate the output like this :

EmpCode Name 1     1_OT  2    2_OT  3    3_OT  4    4_OT...................
338     A    8:51  1:15  9:14 1:15  8:51 1:21  8:44 1:16...................

这意味着我想转动两个柱子ns Day和OT_Day。



提前致谢。

请帮忙...............

That mean I would like to pivot two columns Day and OT_Day.

Thanks in advance.
Please help...............

推荐答案

试试这个：

Try this:

CREATE TABLE #atable (SNO INT, EmpCode VARCHAR(30), [Name] VARCHAR(5), [Day] INT, OT_Day VARCHAR(30), Date DATETIME, WorkingHours VARCHAR(30), [OT Hours] VARCHAR(30))

INSERT INTO #atable (SNO, EmpCode, [Name], [Day], [OT_Day], Date, WorkingHours, [OT Hours])
SELECT 1, 338, 'A', 1, '1_OT', 'Jul 1 2013 12:00AM',  '8:51', '1:15'
UNION ALL SELECT 2, 338, 'A', 2, '2_OT', 'Jul  2 2013 12:00AM', '9:14', '1:15'
UNION ALL SELECT 3, 338, 'A', 3, '3_OT', 'Jul  3 2013 12:00AM', '8:51', '1:21'
UNION ALL SELECT 4, 338, 'A', 4, '4_OT', 'Jul  4 2013 12:00AM', '8:44', '1:16'
UNION ALL SELECT 5, 338, 'A', 5, '5_OT', 'Jul  5 2013 12:00AM', '8:42', '1:15'
UNION ALL SELECT 6, 338, 'A', 6, '6_OT', 'Jul  6 2013 12:00AM', '2:07', '1:16'
--UNION ALL SELECT 10, 338, 'A', 10, '10_OT', 'Jul  10 2013 12:00AM', '9:07', '0:59'

DECLARE @cols VARCHAR(300)
DECLARE @dt VARCHAR(2000)
DECLARE @pt VARCHAR(MAX)

SET @cols = STUFF((SELECT DISTINCT '],[' + [aDay]
					FROM (
							SELECT CONVERT(VARCHAR(10), [Day]) AS [aDay]
							FROM #atable
							UNION ALL
							SELECT CONVERT(VARCHAR(10), [OT_Day]) AS [aDay]
							FROM #atable
						) AS A
					--ORDER BY '],[' + [aDay]
			FOR XML PATH('')),1,2,'') + ']'

--SELECT @cols 

SET @dt = N'SELECT EmpCode, [Name], CONVERT(VARCHAR(10),[Day]) AS [aDay], WorkingHours AS [Hrs]
		FROM #atable
		UNION ALL
		SELECT EmpCode, [Name], [OT_Day] AS [aDay], [OT Hours] AS [Hrs]
		FROM #atable'
--EXEC(@dt)

SET @pt = N'SELECT EmpCode, [Name], ' + @cols + ' ' +
			'FROM (' + @dt + ') AS DT ' + 
			'PIVOT(MAX(Hrs) FOR [aDay] IN(' + @cols + ')) AS PT '
EXEC(@pt)

DROP TABLE #atable

结果：正如所料;）

Result: as expected ;)

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