添加链接
link之家
链接快照平台
  • 输入网页链接,自动生成快照
  • 标签化管理网页链接
相关文章推荐
高大的小熊猫  ·  fatal error C1083 ...·  9 月前    · 
聪明伶俐的领带  ·  Mockito cannot ...·  1 年前    · 
Name   Date      Value  Name   Date      Value  Name   Date      Value
---------------------------------------------------------------------
Test 1/1/2001    10      Foo  5/4/2011   15      My   15/5/2005  36
Test 2/1/2001    17      Foo  6/4/2011   321     My   25/7/2005  75
Test 3/1/2001    52

我需要显示尽可能多的列,就像我的名字列中所存在的那样

我怎样才能在Sql中做到这一点?

1 个评论
ljh
请注意[SQL Server 2008的最大容量],你在选择语句中最多只能有4,096个列。msdn.microsoft.com/en-us/library/ms143432.aspx
sql
sql-server-2008
pivot
unpivot
Oliver
Oliver
发布于 2013-03-21
2 个回答
Taryn
Taryn
发布于 2013-03-22
已采纳
0 人赞同

为了得到你想要的结果,你将不得不取消表格中各列的透视,并应用透视函数。

解除透视可以使用UNPIVOT 函数,也可以使用CROSS APPLYVALUES

UNPIVOT。

select rn, 
  col +'_'+cast(dr as varchar(10)) col, 
  new_values
  select name, 
    convert(varchar(10), date, 101) date, 
    cast(value as varchar(10)) value,
    dense_rank() over(order by name) dr,
    row_number() over(partition by name order by date) rn
  from yourtable
unpivot
  new_values
  for col in (name, date, value)
) un;

CROSS APPLY:

select rn, 
  col +'_'+cast(dr as varchar(10)) col, 
  c.value
  select name, 
    convert(varchar(10), date, 101) date, 
    cast(value as varchar(10)) value,
    dense_rank() over(order by name) dr,
    row_number() over(partition by name order by date) rn
  from yourtable
cross apply
  values
    ('Name', name), ('Date', date), ('Value', Value)
) c (col, value);

参见SQL Fiddle与两个版本的演示。 这样就得到了结果。

| RN |     COL | NEW_VALUES |
-----------------------------
|  1 |  name_1 |        Foo |
|  1 |  date_1 | 04/05/2011 |
|  1 | value_1 |         15 |
|  2 |  name_1 |        Foo |
|  2 |  date_1 | 04/06/2011 |
|  2 | value_1 |        321 |
|  1 |  name_2 |         My |
|  1 |  date_2 | 05/15/2005 |
|  1 | value_2 |         36 |

这些查询采取你现有的列值,并将它们转换为行。一旦它们变成了行,你就通过使用窗口函数来创建新的列名 dense_rank.

一旦数据被转换为行,你就可以使用新的列名(用dense_rank 的值创建)并应用PIVOT 函数。

PIVOT与UNPIVOT。

select name_1, date_1, value_1,
  name_2, date_2, value_2,
  name_3, date_3, value_3
  select rn, 
    col +'_'+cast(dr as varchar(10)) col, 
    new_values
    select name, 
      convert(varchar(10), date, 101) date, 
      cast(value as varchar(10)) value,
      dense_rank() over(order by name) dr,
      row_number() over(partition by name order by date) rn
    from yourtable
  unpivot
    new_values
    for col in (name, date, value)
) src
pivot
  max(new_values)
  for col in (name_1, date_1, value_1,
              name_2, date_2, value_2,
              name_3, date_3, value_3)
) piv;

SQL Fiddle与演示

带CROSS APPLY的PIVOT。

select name_1, date_1, value_1,
  name_2, date_2, value_2,
  name_3, date_3, value_3
  select rn, 
    col +'_'+cast(dr as varchar(10)) col, 
    c.value
    select name, 
      convert(varchar(10), date, 101) date, 
      cast(value as varchar(10)) value,
      dense_rank() over(order by name) dr,
      row_number() over(partition by name order by date) rn
    from yourtable
  cross apply
    values
      ('Name', name), ('Date', date), ('Value', Value)
  ) c (col, value)
) src
pivot
  max(value)
  for col in (name_1, date_1, value_1,
              name_2, date_2, value_2,
              name_3, date_3, value_3)
) piv;

SQL Fiddle与演示

Dyanmic PIVOT:

如果你有有限的或已知数量的列,上述版本将非常有效,如果没有,你将需要使用动态SQL。

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(col +'_'+cast(dr as varchar(10)))
                      select dense_rank() over(order by name) dr
                      from yourtable
                    cross apply
                      values(1, 'Name'), (2, 'Date'), (3, 'Value')
                    ) c (sort, col)
                    group by col, dr, sort
                    order by dr, sort
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
set @query = 'SELECT ' + @cols + ' 
                select rn, 
                  col +''_''+cast(dr as varchar(10)) col, 
                  c.value
                  select name, 
                    convert(varchar(10), date, 101) date, 
                    cast(value as varchar(10)) value,
                    dense_rank() over(order by name) dr,
                    row_number() over(partition by name order by date) rn
                  from yourtable
                cross apply
                  values
                    (''Name'', name), (''Date'', date), (''Value'', Value)
                ) c (col, value)
            pivot 
                max(value)
                for col in (' + @cols + ')
execute(@query)

SQL Fiddle with Demo

每个查询的结果是。

| NAME_1 |     DATE_1 | VALUE_1 | NAME_2 |     DATE_2 | VALUE_2 | NAME_3 |     DATE_3 | VALUE_3 |
-------------------------------------------------------------------------------------------------
|    Foo | 04/05/2011 |      15 |     My | 05/15/2005 |      36 |   Test | 01/01/2001 |      10 |
|    Foo | 04/06/2011 |     321 |     My | 07/25/2005 |      75 |   Test | 01/02/2001 |      17 |
| (null) |     (null) |  (null) | (null) |     (null) |  (null) |   Test | 01/03/2001 |      52 |
    
Zane
bluefeet已经达到了回答的最高水平。
@Zane 这肯定是一个伟大的答案。但不是我见过的最长或最全面的答案。)
AbsoluteƵERØ
AbsoluteƵERØ
发布于 2013-03-22
0 人赞同

用手或用程序。大多数人都习惯于以线性方式显示输出(默认)。如果有人要求你这样做,你可以告诉他们这不是应用程序的工作方式。你可以把结果集导出到csv,然后导入到像Excel这样的东西中,用手重新格式化,或者用ASP.net或PHP这样的服务器端语言把结果格式化成一个表格。

当你解析输出时,你可以检查最后一个var Name和当前的var Name。如果它们不一样,就增加一列。编写脚本还是很麻烦的,因为它们很可能是按顺序从数据库中出来的。所以你会有一个序列,比如test, test, test, foo, foo,这意味着你需要创建一个多维数组来组织数据以获得列数。然后在此基础上设置表格,用一个计数器来计算行名,然后是下面的数据。

我不知道你熟悉哪种应用程序,所以在PHP中,多维数组的输出会是这样的。