SQl 抽出四个字段 排序问题 ？？？

[舞台中央的我]

我现在有一个表A 有五个字段
职工号 补贴A 补贴B 补贴C 补贴D
1 21 NULL 25 NULL
2 NULl 32 68 12
3 NULL NULL 58 56
4 NULL 45 NULL NUll

我想在 想 抽出的结果是
职工号 补贴A 补贴B 补贴C 补贴D
1 21 25 NULL NULL
2 32 68 12 NULL
3 58 56 NULl NULL
4 45 NULl NULL NULL

就是把 有值的 往前放 没值的 往后面放 求Sql 语句 ？？？

[Shawn]

if OBJECT_ID('tempdb..#temp', 'u') is not null   drop table #temp;
go
create table #temp( [职工号] varchar(100), [补贴A] INT, [补贴B] INT, [补贴C] INT, [补贴D] INT);
insert #temp
select '1','21',null,'25',null union all
select '2',null,'32','68','12' union all
select '3',null,null,'58','56' union all
select '4',null,'45',null,null 

--SQL:
SELECT *
FROM
(
	SELECT 职工号,xx,补贴类型=(CASE rowid WHEN 1 THEN '补贴A' WHEN 2 THEN '补贴B' WHEN 3 THEN '补贴C' WHEN 4 THEN '补贴D' END) FROM
	(
		select rowid=ROW_NUMBER() OVER(PARTITION BY 职工号 ORDER BY GETDATE()),职工号,xx
		from #temp a
		UNPIVOT
		(xx FOR yy IN([补贴A],[补贴B],[补贴C],[补贴D])) b
	) t
) m
PIVOT
(MAX(xx) FOR 补贴类型 IN([补贴A],[补贴B],[补贴C],[补贴D])) n
/*
职工号	补贴A	补贴B	补贴C	补贴D
1	21	25	NULL	NULL
2	32	68	12	NULL
3	58	56	NULL	NULL
4	45	NULL	NULL	NULL
*/

[天-笑]

--比较简陋的办法 应该有更好的

create   Function   Fn_SplitTab(@c   varchar(2048),@split   varchar(2))   
returns   @SplitTab   table(A   INT ,B int ,C int ,D int)   
as   
begin   
	declare @i int = 1
	while(charindex(@split,@c)<>0)   
	begin   
		if @i = 1
		begin
			insert   @SplitTab(A)   values   (substring(@c,1,charindex(@split,@c)-1))  
		end
		if @i = 2
		begin
			update   @SplitTab set B  = substring(@c,1,charindex(@split,@c)-1)
		end
		if @i = 3
		begin
			update   @SplitTab set C  = substring(@c,1,charindex(@split,@c)-1)
		end
		if @i = 4
		begin
			update   @SplitTab set D  = substring(@c,1,charindex(@split,@c)-1)

		end
		set   @c   =   stuff(@c,1,charindex(@split,@c),'')   
		set @i = @i + 1
	end   
	return   
end   

--------------------------

declare @T table (ID int,A int,B int, C int, D int)
insert into @T
select 1,21, null, 25 ,null union all
select 2,null, 32, 68 ,12 union all
select 3,null, null, 58 ,56 union all
select 4,null, 45, null ,null union all
select 4,12, NULL, null ,null 
;WITH T as (
	select  *,
		case when A is not null then convert(varchar(8),A) + '|' else '' end +
		case when B is not null then convert(varchar(8),B) + '|' else '' end +
		case when C is not null then convert(varchar(8),C) + '|' else '' end +
		case when D is not null then convert(varchar(8),D) + '|' else '' end  as ABCD
	from @t
)
select a.ID,b.*
from T a cross apply dbo.Fn_SplitTab(a.ABCD,'|') b
/*
(5 行受影响)
ID          A           B           C           D
----------- ----------- ----------- ----------- -----------
1           21          25          NULL        NULL
2           32          68          12          NULL
3           58          56          NULL        NULL
4           45          NULL        NULL        NULL
4           12          NULL        NULL        NULL

(5 行受影响)
*/

[lzw_0736]

WITH a1 (id,a,b,c,d) AS
(
SELECT 1,21,NULL,25,NULL UNION ALL
SELECT 2,NULl,32,68,12 UNION ALL
SELECT 3,NULL,NULL,58,56 UNION ALL
SELECT 4,NULL,45,NULL,NULL
)
,a2 AS
(
SELECT id
,ISNULL(RTRIM(a),'')+','+ISNULL(RTRIM(b),'')+','+ISNULL(RTRIM(c),'')+','+ISNULL(RTRIM(d),'') msg
FROM a1
)
,a3 AS
(
SELECT a.id,CASE WHEN b.msg='' THEN NULL ELSE b.msg END msg
FROM
(SELECT id, msg=CONVERT(XML, '<root><v>'+replace(RTRIM(LTRIM(msg)),',','</v><v>')+'</v></root>') FROM a2) a
OUTER APPLY
(SELECT msg = C.v.value('.','NVARCHAR(MAX)') FROM a.msg.nodes('/root/v') C(v)) b
)
,a4 AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY id ORDER BY CASE WHEN msg IS null THEN 2 ELSE 1 END) re
FROM a3
)
SELECT *
FROM a4
PIVOT (MAX(msg) FOR re IN ([1],[2],[3],[4])) b

[Andy__Huang]

create table #tb(职工号 int,补贴A int,补贴B int,补贴C int,补贴D int)
insert into #tb
select 1,21,NULL,25,NULL
union all select 2,NULL,32,68,12
union all select 3,NULL,NULL,58,56
union all select 4,NULL,45,NULL,NULL

;with cte as 
(select 职工号,SUBSTRING(补贴,number,CHARINDEX(',',补贴+',',number)-number) as 补贴
	,rn=ROW_NUMBER() over(partition by 职工号 order by getdate())
from 
(select 职工号,case when 补贴A IS null then '' else STR(补贴A)+',' end+
	case when 补贴B IS null then '' else STR(补贴B)+',' end+
	case when 补贴C IS null then '' else STR(补贴C)+',' end+
	case when 补贴D IS null then '' else STR(补贴D)+',' end+
	case when 补贴A IS null then ',' else '' end+
	case when 补贴B IS null then ',' else '' end+
	case when 补贴C IS null then ',' else '' end+
	case when 补贴D IS null then ',' else '' end+','
	as 补贴
from #tb
)a, master..spt_values 
where number >=1 and type='p'  
	and number<len(补贴)  and substring(','+补贴,number,1)=','
)
select 职工号,max(case when rn=1 then 补贴 end) as 补贴A,
	max(case when rn=2 then 补贴 end) as 补贴B,
	max(case when rn=3 then 补贴 end) as 补贴C,
	max(case when rn=4 then 补贴 end) as 补贴D
from cte 
group by 职工号
	

/*
职工号	补贴A	补贴B	补贴C	补贴D
1	        21	        25		
2	        32	        68	        12	
3	        58	        56		
4	        45			
*/