TSQL中怎么将ID相同的行合并在同一行，且不同列？

[胖乐]

数据表有很多行，截图只是一部分。谢谢，很急

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([字段1] nvarchar(21),[字段2] int)
Insert #T
select N'A',1 union all
select N'A',12 union all
select N'B',2 union all
select N'B',23 union all
select N'C',3 union all
select N'C',334 union all
select N'C',355
Go
--测试数据结束

DECLARE @sql VARCHAR(MAX)
SET @sql = ';WITH cte AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY 字段1 ORDER BY 字段2) rn FROM #T
)
select 字段1'
;WITH cte AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY 字段1 ORDER BY 字段2) rn FROM #T
)
SELECT  @sql = @sql + ',max(case rn when ' + RTRIM(rn)
        + ' then 字段2 else null end)[字段' + RTRIM(rn) + ']'
FROM    ( SELECT DISTINCT
                    rn
          FROM      cte
        ) a
SET @sql = @sql
    + ' from cte group by 字段1'
EXEC(@sql)

[leo_lesley]

--  给你一个行列转换的例子，如果列信息不固定，根据你的情况改成动态的
create table t (col1 varchar(10) , col2 int)
go
insert t 
select 'A',1
union select 'A',12
union select 'B',2
union select 'B',23
union select 'C',3
union select 'C',334
union select 'C',355


select col1 , max([1]), max([2]), max([3])
from (
select id=ROW_NUMBER() over(partition by col1 order by col2) , * from t) a pivot (max(col2) for id in ([1],[2],[3])) b
group by col1


go
drop table t 

[好奇都是要学的]

引用 2 楼 leo_lesley 的回复:

--  给你一个行列转换的例子，如果列信息不固定，根据你的情况改成动态的
create table t (col1 varchar(10) , col2 int)
go
insert t 
select 'A',1
union select 'A',12
union select 'B',2
union select 'B',23
union select 'C',3
union select 'C',334
union select 'C',355


select col1 , max([1]), max([2]), max([3])
from (
select id=ROW_NUMBER() over(partition by col1 order by col2) , * from t) a pivot (max(col2) for id in ([1],[2],[3])) b
group by col1


go
drop table t 

你这个行转列 必须知道 行的个数 然后 在转列， 跟case一样， 万一 行的个数不固定那。

[好奇都是要学的]

SELECT *,ROW_NUMBER()OVER(PARTITION BY 字段1 ORDER BY 字段2) rn into #a FROM #T
select * from #a
declare @sql varchar(8000)
select @sql='select [字段1] '
select @sql=@sql +', min(case when rn=' +RTRIM(rn)+' then [字段2] else null end ) ''' +RTRIM(rn)+''''
from  ( SELECT DISTINCT rn FROM #a) a
set @sql=@sql  + ' from #a group by 字段1'
print @sql
exec (@sql)
drop table #a