在SQL Server中将行向上移动的空值

I have data in format

我有格式的数据

    ID Name Jan Feb  Mar
    ---------------------
    1  xyz  1   null null
    1  xyz  2   null null 
    1  xyz  null  3  null
    1  xyz  null null 4 

I need the data in below format in SQL Server 2008

我需要SQL Server 2008中以下格式的数据

 ID   Name   Jan Feb    Mar
 ----------------------------
  1     xyz     1    3     4
                2   null  null

Can anyone help? I updated the question as id and name should not repeat.

有人可以帮忙吗?我更新了问题,因为ID和名称不应重复。

Thanks

2 个解决方案

#1

You can use the following query:

您可以使用以下查询:

SELECT ID, Name, [Jan], [Feb], [Mar]
FROM (
   SELECT ID, Name, Val, Mon,
          ROW_NUMBER() OVER (PARTITION BY ID, Name, Mon ORDER BY val) AS rn
   FROM (
      SELECT ID, Name, Jan, Feb, Mar
      FROM mytable ) p
   UNPIVOT 
      (Val FOR Mon IN (Jan, Feb, Mar) ) AS unpvt
) src
PIVOT (
   MAX(Val) FOR Mon IN ([Jan], [Feb], [Mar]) ) AS pvt 

Using UNPIVOT you can get rid of all NULL values contained in Jan, Feb, Mar columns. You can then use PIVOT to reconstruct the table to its initial form.

使用UNPIVOT,您可以删除Jan,Feb,Mar列中包含的所有NULL值。然后,您可以使用PIVOT将表重建为其初始形式。

Demo here

EDIT:

If you don't want the ID, Name values repeated over successive rows, then you'd better handle this on the client side, as this is rather a data formatting requirement.

如果你不想在连续的行上重复ID,Name值,那么你最好在客户端处理这个,因为这是一个数据格式化要求。

Anyway, you can easily achieve this effect with pure SQL using rn of the inner sub-query:

无论如何,使用内部子查询的rn,您可以使用纯SQL轻松实现此效果:

SELECT CASE when rn = 1 THEN CAST(ID AS VARCHAR(8)) ELSE '' END AS ID, 
       CASE when rn = 1 THEN Name ELSE '' END AS Name, 
       [Jan], [Feb], [Mar]      
FROM (
   SELECT ID, Name, Val, Mon,
          ROW_NUMBER() OVER (PARTITION BY ID, Name, Mon ORDER BY val) AS rn
   FROM (
      SELECT ID, Name, Jan, Feb, Mar
      FROM mytable ) p
   UNPIVOT 
      (Val FOR Mon IN (Jan, Feb, Mar) ) AS unpvt
) src
PIVOT (
   MAX(Val) FOR Mon IN ([Jan], [Feb], [Mar]) ) AS pvt 

Demo here

#2

select   1 ID, 'xyz' Name, 1 Jan,  null Feb, null Mar into #temp union all select
1,  'xyz', 2 ,  null ,null  union all select
1,  'xyz' , null , 3 , null union all select
1,  'xyz',  null, null, 4 

select * from #temp

;with x as(
select ROW_NUMBER()over(partition by id,name, case when jan is null then null else 1 end,
 case when feb is null then null else 1 end,
  case when mar is null then null else 1 end order by name,id) rid,ID,name,jan,feb,mar from #temp
)
select ID, name, SUM(jan)jan, SUM(feb) feb, SUM(mar) mar from x group by rid,ID,name

RESULT

ID          name jan         feb         mar
----------- ---- ----------- ----------- -----------
1           xyz  1           3           4
1           xyz  2           NULL        NULL