none
请教个行转列 RRS feed

  • 问题

  • A    B
    1    2
    1    3
    1    11
    2    222
    2    5
    2    6
    3    3
    3    33

    以上结果集,标准SQL如何转换成

    A    A1    A2    A3
    1    2      3     11
    2    222  5     6
    3    3      33   NULL
    最好是标准SQL,能用静态的更好。
    2012年6月7日 13:07

答案

  • declare @T table([A] int,[B] int)
    insert @T
    select 1,2 union all
    select 1,3 union all
    select 1,11 union all
    select 2,222 union all
    select 2,5 union all
    select 2,6 union all
    select 3,3 union all
    select 3,33
    
    ;with maco as
    (
    select *,row_number() over (partition by [A] order by (select 1)) as id from @T
    )
    
    select A ,
      max(case id when '1' then B else null end) A1,
      max(case id when '2' then B else null end) A2,
      max(case id when '3' then B else null end) A3
    from maco group by A
    
    /*
    A           A1          A2          A3
    ----------- ----------- ----------- -----------
    1           2           3           11
    2           222         5           6
    3           3           33          NULL
    */

    • 已标记为答案 GodOMG 2012年6月8日 8:55
    2012年6月8日 5:09
    版主

全部回复

  • Take look at 'Using PIVOT and UNPIVOT' in books online, has sample code there.
    2012年6月7日 13:32
  • 固定列数的话,可以参考这样的处理

    WITH
    DATA AS(
    	SELECT
    		colid = ROW_NUMBER() OVER(PARTITION BY A ORDER BY B),
    		B
    	FROM TB
    )
    SELECT
    	A1 = [1],
    	A2 = [2],
    	A3 = [3]
    FROM DATA
    	PIVOT(
    		MAX(B)
    		FOR colid IN(
    			[1], [2], [3]
    		)
    	)P

    2012年6月8日 0:55
  • 列数不固定的话,就只有动态 T-SQL 了

    2012年6月8日 0:55
  • 按照这个SQL没得出需要的结果集╮(╯▽╰)╭

    只得出了

    A1    A2    A3

    3      6      NULL

    另外能否用标准SQL实现? 列数固定的情况下.

    2012年6月8日 2:03
  • 你的语句有错误

    关键字 'with' 附近有语法错误。如果此语句是公用表表达式或 xmlnamespaces 子句,那么前一个语句必须以分号结尾。


    给我写信: QQ我:点击这里给我发消息

    2012年6月8日 2:11
  • 结果不对


    给我写信: QQ我:点击这里给我发消息

    2012年6月8日 2:16
  • 桦仔,你好,你贴的结果集也不对啊.

    需要的结果集我1楼贴出来了..

    2012年6月8日 2:19
  • declare @T table([A] int,[B] int)
    insert @T
    select 1,2 union all
    select 1,3 union all
    select 1,11 union all
    select 2,222 union all
    select 2,5 union all
    select 2,6 union all
    select 3,3 union all
    select 3,33
    
    ;with maco as
    (
    select *,row_number() over (partition by [A] order by (select 1)) as id from @T
    )
    
    select A ,
      max(case id when '1' then B else null end) A1,
      max(case id when '2' then B else null end) A2,
      max(case id when '3' then B else null end) A3
    from maco group by A
    
    /*
    A           A1          A2          A3
    ----------- ----------- ----------- -----------
    1           2           3           11
    2           222         5           6
    3           3           33          NULL
    */

    • 已标记为答案 GodOMG 2012年6月8日 8:55
    2012年6月8日 5:09
    版主
  • declare @T table([A] int,[B] int)
    insert @T
    select 1,2 union all
    select 1,3 union all
    select 1,11 union all
    select 2,222 union all
    select 2,5 union all
    select 2,6 union all
    select 3,3 union all
    select 3,33
    
    ;with maco as
    (
    select *,row_number() over (partition by [A] order by (select 1)) as id from @T
    )
    select A,[1] as A1,[2] as A2,[3] as A3 
    from maco 
    pivot (max(B) for id in ([1],[2],[3])) b
    
    /*
    A           A1          A2          A3
    ----------- ----------- ----------- -----------
    1           2           3           11
    2           222         5           6
    3           3           33          NULL
    */


    2012年6月8日 5:15
    版主
  • 写个动态的:

    create table tb([A] int,[B] int)
    insert tb
    select 1,2 union all
    select 1,3 union all
    select 1,11 union all
    select 2,222 union all
    select 2,5 union all
    select 2,6 union all
    select 3,3 union all
    select 3,33
    
    declare @sql varchar(8000)
    select @sql = isnull(@sql + '],[' , '') +ltrim(id) from 
    ( 
    	select *,row_number() over (partition by [A] order by (select 1)) as id from tb
    ) t group by id
    set @sql = '[' + @sql + ']'
    exec ('select * from (
    select *,row_number() over (partition by [A] order by (select 1)) as id from tb) 
    a pivot (max(B) for id in (' + @sql + ')) b')
    /*
    A           1           2           3
    ----------- ----------- ----------- -----------
    1           2           3           11
    2           222         5           6
    3           3           33          NULL
    */
    

    2012年6月8日 5:21
    版主
  • 不明白max(B)聚合函数有什么用,今天想了一天也没有想出来,pivot要使用聚合函数,弄得我思想转不过来

    给我写信: QQ我:点击这里给我发消息

    2012年6月8日 6:40
  •   max(case id when '1' then B else null end) A1

    为什麽要加个max

    为什么结果不是

    因为select 第一行的时候不能select 第二行啊


    给我写信: QQ我:点击这里给我发消息

    2012年6月8日 6:49
  • 因为后面有group by 所以这个前面要使用集合函数,故这个位置有个max函数。 
    2012年6月8日 8:25
    版主
  • 十分感谢maco wang

    我研究研究.

    2012年6月8日 8:56
  • 用avg、 min这些聚合函数不行吗?

    给我写信: QQ我:点击这里给我发消息

    2012年6月8日 15:58
  • 不明白sqlserver怎麽合并的,看来对sqlserver不熟悉

    给我写信: QQ我:点击这里给我发消息

    2012年6月8日 16:03