我使用的是SQL Server 2012,自学成才,很抱歉有任何错误。
我有一个表,我有pivot,结果表有空值,如下所示:
Pepsi Coke Gatorade
--------------------------
Vanilla NULL NULL
Cherry NULL NULL
NULL Vanilla NULL
NULL Cherry NULL
NULL NULL Lime
NULL NULL Fruit Punch
NULL NULL Grape
我的问题是,如果有一种方法来重新排列表,使不为空的行显示在各自列的顶部,这样,如果另一个用户要添加不同类型的饮料,如水,查询将自动排序新添加的列。如果不能做到这一点,我们将感谢其他建议。
期望结果的示例如下:
Pepsi Coke Gatorade
------------------------------
Vanilla Vanilla Lime
Cherry Cherry Fruit Punch
NULL NULL Grape
我已经尝试过从from子句中进行一系列的外部连接,但是,我无法找到一种方法来生成结果,而不显式调用列和/或表名。
select distinct t1.Pepsi, t2.Coke from
(select
#test.Pepsi as Pepsi,
ROW_NUMBER() over (order by Pepsi) r
from #test
where Pepsi is not null) as t1
full outer join
(select
#test.Coke as Coke,
ROW_NUMBER() over (order by Coke) r
from #test
where Coke is not null) as t2
on t1.r=t2.r
原表如下所示:
Drink Type Price Location etc
Coke Vanilla
Coke Cherry
Gatorade Lime
Gatorade Grape
.
.
.
Pepsi Cherry
任何建议或帮助都是非常感激的。 DECLARE @t TABLE ([Drink] varchar(max), [Type] varchar(max), [Price] money )
INSERT @t
([Drink],[Type])
VALUES
('Coke','Vanilla'),
('Coke','Cherry'),
('Gatorade','Lime'),
('Gatorade','Grape'),
('Gatorade','Fruit Punch'),
('Pepsi','Vanilla'),
('Pepsi','Cherry')
;WITH t AS (
SELECT
[Drink],[Type],
ROW_NUMBER() OVER(PARTITION BY [Drink] ORDER BY [Type] DESC) rn
FROM @t
)
SELECT [Pepsi],[Coke],[Gatorade]
FROM t
PIVOT(MAX([Type]) FOR [Drink] IN ([Pepsi],[Coke],[Gatorade])) p