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 语句 ？？？
sql
[解决办法]
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
12125NULLNULL
2326812NULL
35856NULLNULL
445NULLNULLNULL
*/
--比较简陋的办法 应该有更好的
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 行受影响)
*/
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
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
*/