行到列在SQL中更改 [英] rows to columns change in sql

问题描述

我的桌子上有

结果ID
1
b 2
c 3
d 4
更改Answer1 Answer2 Answer3 Answer4
a b c d

并且结果行是dynamnic.i在gridview中具有视图，所以我要查询.

感谢

my table has contain

Result id
a 1
b 2
c 3
d 4
change in Answer1 Answer2 Answer3 Answer4
a b c d

and the Result row is dynamnic.i have view in gridview so i want query.

thanks

推荐答案

使用 PIVOT [< ^ ]命令.

Use the PIVOT[^] command.

SELECT * FROM myTable
PIVOT(MAX(Result) FOR id IN ([1], [2], [3], [4])) myPivot

由于枢轴需要在其末尾放置一个名称myPivot，即使您可能不使用它也是如此.此外，PIVOT需要使用聚合函数，因此在这种情况下为MAX.

Because the pivot needs a name myPivot is placed at the end, even though you might not use it. Also the PIVOT needs to use a aggregate function, so MAX in this case.

如果要构建动态枢轴，请使用它:

If you would like to build dynamic pivots, use it:

DECLARE @cols NVARCHAR(2000)
DECLARE @qry NVARCHAR(2000)

SET  @cols = STUFF(( SELECT DISTINCT '],[' + myT.id
                FROM myTable AS myT ORDER BY '],[' + myT.id
                FOR XML PATH('')), 1, 2, '') + ']'

SET  @qry = 'SELECT * ' +
                'FROM myTable ' +
                'PIVOT(MAX(Result) FOR id IN (' + @cols + ')) PT'

EXEC(@qry)

STUFF和FOR XML PATH像这样将行"转换为列:[1]，[2]，[3] ...，依此类推.

更有趣的是使用行标题和列标题构建枢轴:

STUFF and FOR XML PATH "convert" rows to colums like this: [1],[2],[3]... and so on.

More interesting is to build pivots with rowheaders and columnheaders:

DECLARE @cols NVARCHAR(2000)
DECLARE @dts NVARCHAR(10)
DECLARE @dte NVARCHAR(10)
DECLARE @subqry NVARCHAR(2000)
DECLARE @pivqry NVARCHAR(2000)

SET  @cols = STUFF(( SELECT DISTINCT '],[' + D.DocumentName
                FROM Documents AS D ORDER BY '],[' + D.DocumentName
                FOR XML PATH('')), 1, 2, '') + ']'

--date range for subqry
SET @dts = '2011-09-01'
SET @dte = '2011-09-30'

--count documents for some Departments
SET  @subqry = 'SELECT Dep.[DepartmentName], Doc.[DocumentName], COUNT(Doc.[DocumentName]) AS [DocCount] ' +
                'FROM Documents AS Doc ' +
                '    JOIN LEFT Departments AS Dep ON Doc.DepID = Dep.ID ' +
                'WHERE Doc.CreationDate BETWEEN ''' + @dts + ''' AND  ''' + @dte + '''' +
                'GROUP BY Dep.DepartmentName, Doc.DocumentName'

--sum documents for each department
SET  @pivqry = 'SELECT [Departmentname], ' + @cols + ' ' +
                'FROM (' + @subqry + ') AS DT ' +
                'PIVOT(SUM([DocCount]) FOR [DocumentName] IN (' + @cols + ')) PT ' +
                'ORDER BY [Departmentname]'

EXEC(@pivqry)

结果示例:

部门名称	DocA	DocB	DocC	DocD
Dep1	50	100	150	200
Dep2	55	80	105	130
Dep3	23	48	76	92
Dep4	16	33	62	89

Example result:

DepartmentName	DocA	DocB	DocC	DocD
Dep1	50	100	150	200
Dep2	55	80	105	130
Dep3	23	48	76	92
Dep4	16	33	62	89

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