使用PIVOT和varchar列的SQL Server 2008 R2无法正常工作 [英] SQL Server 2008 R2 using PIVOT with varchar columns not working

问题描述

我正在使用SQL Server 2008 R2，我有这个简单的表

I'm using SQL Server 2008 R2, I have this simple table

我想做的是从此表中进行选择并获得以下结果

What I was trying to do is make a selection from this table and get this following result

x |      1     |       2     |      3
--+------------+-------------+------------
1 |   first 1  |    first 2  |    first 3
2 |   Second 1 |    second 2 |    second 3 

我认为可以通过PIVOT

对于PIVOT以及我使用PIVOT和Count()找到的所有搜索结果我都不了解. SUM()，AVG()在我的表中不起作用，因为我正在尝试在varchar列上PIVOT

I don't know much about PIVOT AND all my search result found using PIVOT with Count() . SUM(), AVG() which will not work in my table since I'm trying to PIVOT on a varchar column

问题我在使用正确的功能吗?还是有其他我需要了解的解决此问题的方法?任何帮助将不胜感激

Question am I using the right function? Or is there something else I need to know to solve this issue? Any help will be appreciated

我尝试了这个，但没有运气

PIVOT(count(x) FOR value IN ([1],[2],[3]) )as total 
PIVOT(count(y) FOR value IN ([1],[2],[3]) )as total  // This one is the nearest 
of what i wand  but instead of the column value values i get 0  

这里是查询，如果有人要测试

Here is the query if any one to test it

CREATE TABLE #test (x int , y int , value Varchar(50))
INSERT INTO #test VALUES(1,51,'first 1')
INSERT INTO #test VALUES(1,52,'first 2')
INSERT INTO #test VALUES(1,53,'first 3')
INSERT INTO #test VALUES(2,51,'Second 1')
INSERT INTO #test VALUES(2,52,'Second 2')
INSERT INTO #test VALUES(2,53,'Second 3')
SELECT * FROM #test
  PIVOT(count(y) FOR value IN ([1],[2],[3]) )as total 
 DROP TABLE #test 

推荐答案

使用PIVOT函数时，IN子句中的值必须与您选择的值匹配.您当前的数据不包括1、2或3.您可以使用row_number()为每个x分配一个值:

When you are using the PIVOT function the values inside the IN clause need to match a value that you are selecting. Your current data does not include 1, 2, or 3. You can use row_number() to assign a value for each x:

select x, [1], [2], [3]
from
(
  select x, value,
    row_number() over(partition by x order by y) rn
  from test
) d
pivot
(
  max(value)
  for rn in ([1], [2], [3])
) piv;

请参见带演示的SQL提琴.如果每个x的值数量未知，那么您将要使用动态SQL:

See SQL Fiddle with Demo. If you then have a unknown number of values for each x, then you will want to use dynamic SQL:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(row_number() over(partition by x order by y)) 
                    from test
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT x,' + @cols + ' 
            from 
            (
              select x, value,
                row_number() over(partition by x order by y) rn
              from test
            ) x
            pivot 
            (
                max(value)
                for rn in (' + @cols + ')
            ) p '

execute(@query);

请参见带有演示的SQL提琴

这篇关于使用PIVOT和varchar列的SQL Server 2008 R2无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！