如何得到这种格式的表？

Question

有如下表：
CREATE TABLE [dbo].[Table_2](
[LID] [varchar](50) NOT NULL,
[PName] [varchar](50) NOT NULL,
[EName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED 
(
[LID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]测试数据：
INSERT INTO [dbo].[Table_2] ([LID],[PName],[EName])VALUES('A0001','铁','北京')
INSERT INTO [dbo].[Table_2] ([LID],[PName],[EName])VALUES('A0002','钢','北京')
INSERT INTO [dbo].[Table_2] ([LID],[PName],[EName])VALUES('A0003','铁','北京')
INSERT INTO [dbo].[Table_2] ([LID],[PName],[EName])VALUES('A0004','铁','北京')
INSERT INTO [dbo].[Table_2] ([LID],[PName],[EName])VALUES('A0005','铁','北京')
INSERT INTO [dbo].[Table_2] ([LID],[PName],[EName])VALUES('A0006','铁','北京')
INSERT INTO [dbo].[Table_2] ([LID],[PName],[EName])VALUES('A0007','铁','北京')
INSERT INTO [dbo].[Table_2] ([LID],[PName],[EName])VALUES('A0008','铁','北京')
INSERT INTO [dbo].[Table_2] ([LID],[PName],[EName])VALUES('A0009','铁','北京')
INSERT INTO [dbo].[Table_2] ([LID],[PName],[EName])VALUES('A0010','铁','上海')
INSERT INTO [dbo].[Table_2] ([LID],[PName],[EName])VALUES('A0011','铁','上海')
INSERT INTO [dbo].[Table_2] ([LID],[PName],[EName])VALUES('A0012','铁','上海')
INSERT INTO [dbo].[Table_2] ([LID],[PName],[EName])VALUES('A0013','铁','上海')
INSERT INTO [dbo].[Table_2] ([LID],[PName],[EName])VALUES('A0014','铜','西安')
INSERT INTO [dbo].[Table_2] ([LID],[PName],[EName])VALUES('A0021','铜','北京')
INSERT INTO [dbo].[Table_2] ([LID],[PName],[EName])VALUES('A0022','铜','北京')
INSERT INTO [dbo].[Table_2] ([LID],[PName],[EName])VALUES('A0024','铜','西安')
目的：得到如下格式的表PID        LID1     LID2     LID3    LID4    LID5     Pname      ename
001        A0001   A0003    A0004    A0005   A0006     铁         北京
002        A0007   A0008    A0009    A0010             铁         北京
003        A0002                                       钢         北京
004        A0014   A0024                               铜         西安
005        A0011   A0012    A0013                      铁         上海
006        A0021   A0022                               铜         北京PID是结果顺序号,将pname和ename相同的记录合并在一起，如果符合记录够5条，则自动生成新的记录，直到不够5条，例如001和002
谢谢大家的帮助！！

Answer 1

CREATE TABLE [Table_2](
[LID] [varchar](50) NOT NULL,
[PName] [varchar](50) NOT NULL,
[EName] [varchar](50) NOT NULL,
)INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0001','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0002','钢','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0003','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0004','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0005','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0006','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0007','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0008','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0009','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0010','铁','上海')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0011','铁','上海')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0012','铁','上海')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0013','铁','上海')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0014','铜','西安')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0021','铜','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0022','铜','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0024','铜','西安')select max(case (px - 1) % 5 when 0 then lid else '' end) lid1,
       max(case (px - 1) % 5 when 1 then lid else '' end) lid2,
       max(case (px - 1) % 5 when 2 then lid else '' end) lid3,
       max(case (px - 1) % 5 when 3 then lid else '' end) lid4,
       max(case (px - 1) % 5 when 4 then lid else '' end) lid5,
       Pname ,
       ename 
from
(
  select t.*,px = (select count(1) from [Table_2] where Pname = t.Pname and ename = t.ename and lid < t.lid) + 1 from [Table_2] t
) m
group by Pname, ename , (px - 1)/5drop table [Table_2]/*
lid1                                               lid2                                               lid3                                               lid4                                               lid5                                               Pname                                              ename                                              
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- 
A0002                                                                                                                                                                                                                                                          钢                                                  北京
A0001                                              A0003                                              A0004                                              A0005                                              A0006                                              铁                                                  北京
A0007                                              A0008                                              A0009                                                                                                                                                    铁                                                  北京
A0010                                              A0011                                              A0012                                              A0013                                                                                                 铁                                                  上海
A0021                                              A0022                                                                                                                                                                                                       铜                                                  北京
A0014                                              A0024                                                                                                                                                                                                       铜                                                  西安（所影响的行数为 6 行）
*/

Answer 2

CREATE TABLE [Table_2](
[LID] [varchar](50) NOT NULL,
[PName] [varchar](50) NOT NULL,
[EName] [varchar](50) NOT NULL,
)INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0001','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0002','钢','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0003','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0004','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0005','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0006','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0007','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0008','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0009','铁','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0010','铁','上海')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0011','铁','上海')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0012','铁','上海')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0013','铁','上海')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0014','铜','西安')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0021','铜','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0022','铜','北京')
INSERT INTO [Table_2] ([LID],[PName],[EName])VALUES('A0024','铜','西安')select PID = right('00' + ltrim((select count(1) from 
(
select max(case (px - 1) % 5 when 0 then lid else '' end) lid1,
       max(case (px - 1) % 5 when 1 then lid else '' end) lid2,
       max(case (px - 1) % 5 when 2 then lid else '' end) lid3,
       max(case (px - 1) % 5 when 3 then lid else '' end) lid4,
       max(case (px - 1) % 5 when 4 then lid else '' end) lid5,
       Pname ,
       ename 
from
(
  select t.*,px = (select count(1) from [Table_2] where Pname = t.Pname and ename = t.ename and lid < t.lid) + 1 from [Table_2] t
) m
group by Pname, ename , (px - 1)/5
) p 
where EName < q.EName or (EName = q.EName and PName < q.PName) or (EName = q.EName and PName = q.PName and lid1 < q.lid1)) + 1),3),q.* from
(
select max(case (px - 1) % 5 when 0 then lid else '' end) lid1,
       max(case (px - 1) % 5 when 1 then lid else '' end) lid2,
       max(case (px - 1) % 5 when 2 then lid else '' end) lid3,
       max(case (px - 1) % 5 when 3 then lid else '' end) lid4,
       max(case (px - 1) % 5 when 4 then lid else '' end) lid5,
       Pname ,
       ename 
from
(
  select t.*,px = (select count(1) from [Table_2] where Pname = t.Pname and ename = t.ename and lid < t.lid) + 1 from [Table_2] t
) m
group by Pname, ename , (px - 1)/5
) qdrop table [Table_2]/*
PID    lid1                                               lid2                                               lid3                                               lid4                                               lid5                                               Pname                                              ename                                              
------ -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- 
001    A0002                                                                                                                                                                                                                                                          钢                                                  北京
002    A0001                                              A0003                                              A0004                                              A0005                                              A0006                                              铁                                                  北京
003    A0007                                              A0008                                              A0009                                                                                                                                                    铁                                                  北京
005    A0010                                              A0011                                              A0012                                              A0013                                                                                                 铁                                                  上海
004    A0021                                              A0022                                                                                                                                                                                                       铜                                                  北京
006    A0014                                              A0024                                                                                                                                                                                                       铜                                                  西安（所影响的行数为 6 行）
*/

Answer 3

CREATE TABLE [dbo].[Table_2](
[LID] [varchar](50) NOT NULL,
[PName] [varchar](50) NOT NULL,
[EName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED  
(
[LID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]select no=(select COUNT(1) from Table_2 where PName=a.PName and EName=a.EName and LID<=a.LID),
* from Table_2 aselect row_number()over(order by getdate()) as no,LID1=max( case when no%6=1 then lid end),
       LID2=max( case when no%6=2 then lid end),
       LID3=max( case when no%6=3 then lid end),
       LID4=max( case when no%6=4 then lid end),
       LID5=max( case when no%6=5 then lid end),Pname,ename
       from 
       (select no=(select COUNT(1) from Table_2 where PName=a.PName and EName=a.EName and LID<=a.LID),
* from Table_2 a) a
group by Pname,ename,no/6 order by EName,PName/*
no                   LID1                                               LID2                                               LID3                                               LID4                                               LID5                                               Pname                                              ename
-------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
1                    A0002                                              NULL                                               NULL                                               NULL                                               NULL                                               钢                                                  北京
2                    A0001                                              A0003                                              A0004                                              A0005                                              A0006                                              铁                                                  北京
3                    A0008                                              A0009                                              NULL                                               NULL                                               NULL                                               铁                                                  北京
4                    A0021                                              A0022                                              NULL                                               NULL                                               NULL                                               铜                                                  北京
5                    A0010                                              A0011                                              A0012                                              A0013                                              NULL                                               铁                                                  上海
6                    A0014                                              A0024                                              NULL                                               NULL                                               NULL                                               铜                                                  西安

Answer 4

顶2楼ps:LZ给的结果数据与测试数据不一致