34,838
社区成员




--> 测试数据: TB
if object_id('TB') is not null drop table TB
create table TB (id int,Name varchar(1),A int,B int,C int,D int,E int,F int,G int,H int,I int)
insert into TB
select 1,'A',1,2,3,4,5,6,7,8,9 union all
select 2,'B',9,11,22,33,44,55,66,77,88
select * from TB
SELECT IDD=IDENTITY(INT,1,1),NAME INTO #T
FROM SYSCOLUMNS
WHERE ID=OBJECT_ID('TB') AND NAME NOT IN('ID','NAME')
--SELECT * FROM #T
SELECT
(IDD-1)/4 AS ID,
MAX(CASE WHEN (IDD-1)%4=0 THEN NAME END) AS NAME1,
MAX(CASE WHEN (IDD-1)%4=1 THEN NAME END) AS NAME2,
MAX(CASE WHEN (IDD-1)%4=2 THEN NAME END) AS NAME3,
MAX(CASE WHEN (IDD-1)%4=3 THEN NAME END) AS NAME4 INTO #T2
FROM #T
GROUP BY (IDD-1)/4
DECLARE @S NVARCHAR(4000)
SELECT
@S=ISNULL(@S+' UNION ALL ','')+'SELECT ID,NAME,'+NAME1+ISNULL(','+NAME2,',0')+ISNULL(','+NAME3,',0')+ISNULL(','+NAME4,',0')+' FROM TB'
FROM
#T2
SELECT @S
--DROP TABLE #T,#T2
EXEC('SELECT ID,NAME,A,B,C,D FROM TB
UNION ALL '+@S)