SQL 前两列重复合成一条记录，第3列横放（如图）

[luowen83]

create table aa
(
UserID int ,
UserName nvarchar(50),
CityName nvarchar(50)
);

insert into aa (UserID,UserName,CityName) values (1,'上海','a')
insert into aa (UserID,UserName,CityName) values (1,'上海','e')
insert into aa (UserID,UserName,CityName) values (1,'上海','c')
insert into aa (UserID,UserName,CityName) values (2,'北京','b')
insert into aa (UserID,UserName,CityName) values (2,'北京','d')


以上内容希望得到如下图结果 ：



感谢！！！

[OwenZeng_DBA]

SELECT  UserID ,
        username ,
        MIN(CASE rn
              WHEN 1 THEN CityName
              ELSE 'zz'
            END) field1 ,
        MAX(CASE rn
              WHEN 2 THEN CityName
              ELSE '1'
            END) AS fild2 ,
        MAX(CASE rn
              WHEN 3 THEN CityName
              ELSE ''
            END) AS fild3
FROM    ( SELECT    * ,
                    ROW_NUMBER() OVER ( PARTITION BY username ORDER BY UserID ) AS rn
          FROM      AA
        ) AS t
GROUP BY UserID ,
        username
             

[OwenZeng_DBA]

结果如下图

[道素]

如果你这里的CityName数量不是固定的，那么你需要用动态语句实现

declare  @cols nvarchar(max),@sql nvarchar(max)
select @cols=isnull(@cols+',','')+'Field'+ltrim(ID) from (select row_number()over(partition by UserID,UserName order by getdate()) as ID from  aa ) as a group by ID
set @sql=N'
select * from (
select *,''Field''+ltrim(row_number()over(partition by UserID,UserName order by getdate())) as Field from  aa
) as t pivot(max(CityName) for Field in ('+@cols+N')) p'
EXEC(@sql)

+--------+----------+--------+--------+--------+
| UserID | UserName | Field1 | Field2 | Field3 |
+--------+----------+--------+--------+--------+
| 2      | 北京       | b      | d      | NULL   |
| 1      | 上海       | a      | e      | c      |
+--------+----------+--------+--------+--------+

[luowen83]

感谢两位！ 道素，广义列好