SQL 前两列重复合成一条记录,第3列横放(如图)

luowen83 2017-12-02 11:46:01
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')


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



感谢!!!
...全文
128 4 点赞 打赏 收藏 举报
写回复
4 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
luowen83 2017-12-04
感谢两位! 道素,广义列好
  • 打赏
  • 举报
回复
道素 2017-12-04
如果你这里的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      |
+--------+----------+--------+--------+--------+
  • 打赏
  • 举报
回复
OwenZeng_DBA 2017-12-03
结果如下图
  • 打赏
  • 举报
回复
OwenZeng_DBA 2017-12-03
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
             
  • 打赏
  • 举报
回复
相关推荐
发帖
疑难问题
加入

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2017-12-02 11:46
社区公告
暂无公告