isnull pivot server sql_在SQL服务器枢轴中为空？(Null in SQL server pivot?)

在SQL服务器枢轴中为空？(Null in SQL server pivot?)

我有这个问题

DECLARE @Test TABLE

(

RowID INT IDENTITY(1,1) PRIMARY KEY

,[Name]VARCHAR(10) NOT NULL

,tool VARCHAR(10) NOT NULL,

stam NVARCHAR(MAX)

);

INSERT @Test VALUES ('john', 'vocals','1');

INSERT @Test VALUES ('john', 'guitar','1');

INSERT @Test VALUES ('paul', 'vocals','1');

INSERT @Test VALUES ('paul', 'bass','1');

INSERT @Test VALUES ('george', 'vocals','1');

INSERT @Test VALUES ('george', 1,'1');

INSERT @Test VALUES ('ringo', 'vocals','1');

INSERT @Test VALUES ('ringo', 3,'1');

INSERT @Test VALUES ('ringo', 'drums','1');

INSERT @Test VALUES ('yoko', 'vocals','1');

INSERT @Test VALUES ('royi', 'vocals','1');

INSERT @Test VALUES ('royi', 'guitar','1');

;WITH PivotSource

AS

(

SELECT t.[Name], t.[tool]

FROM @Test t

)

SELECT *

FROM PivotSource

PIVOT ( max(tool) FOR tool IN ([vocals], [guitar], [bass],[drums]) ) pvt;

结果是：

有没有办法用"" (空字符串)替换null ？ ( 不修改CTE数据！)

I have this query

DECLARE @Test TABLE

(

RowID INT IDENTITY(1,1) PRIMARY KEY

,[Name]VARCHAR(10) NOT NULL

,tool VARCHAR(10) NOT NULL,

stam NVARCHAR(MAX)

);

INSERT @Test VALUES ('john', 'vocals','1');

INSERT @Test VALUES ('john', 'guitar','1');

INSERT @Test VALUES ('paul', 'vocals','1');

INSERT @Test VALUES ('paul', 'bass','1');

INSERT @Test VALUES ('george', 'vocals','1');

INSERT @Test VALUES ('george', 1,'1');

INSERT @Test VALUES ('ringo', 'vocals','1');

INSERT @Test VALUES ('ringo', 3,'1');

INSERT @Test VALUES ('ringo', 'drums','1');

INSERT @Test VALUES ('yoko', 'vocals','1');

INSERT @Test VALUES ('royi', 'vocals','1');

INSERT @Test VALUES ('royi', 'guitar','1');

;WITH PivotSource

AS

(

SELECT t.[Name], t.[tool]

FROM @Test t

)

SELECT *

FROM PivotSource

PIVOT ( max(tool) FOR tool IN ([vocals], [guitar], [bass],[drums]) ) pvt;

the result is :

is there any way to replace null with "" ( empty string) ? ( without modifying the CTE data !)

原文：https://stackoverflow.com/questions/9567807

更新时间：2019-11-19 02:52

最满意答案

使用：

SELECT pvt.Name

, isnull(pvt.[vocals], '') [vocals]

, isnull(pvt.[guitar], '') [guitar]

, isnull(pvt.[bass], '') [bass]

, isnull(pvt.[drums], '') [drums]

FROM PivotSource

PIVOT

(

max(tool)

FOR tool

IN ([vocals], [guitar], [bass], [drums])

) pvt;

输出：

Name vocals guitar bass drums

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

george vocals

john vocals guitar

paul vocals bass

ringo vocals drums

royi vocals guitar

yoko vocals

Use:

SELECT pvt.Name

, isnull(pvt.[vocals], '') [vocals]

, isnull(pvt.[guitar], '') [guitar]

, isnull(pvt.[bass], '') [bass]

, isnull(pvt.[drums], '') [drums]

FROM PivotSource

PIVOT

(

max(tool)

FOR tool

IN ([vocals], [guitar], [bass], [drums])

) pvt;

Output:

Name vocals guitar bass drums

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

george vocals

john vocals guitar

paul vocals bass

ringo vocals drums

royi vocals guitar

yoko vocals

2012-03-05

相关问答

SELECT 'Miles' AS Type, SUM(CASE WHEN date = lastweek THEN miles ELSE 0 END) as lastweek

, SUM(CASE WHEN date = thisweek THEN miles ELSE 0 END) as thisweek

SUM(CASE WHEN date = lastweek THEN miles ELSE 0 END) - SUM(CASE WHEN date = thisweek THEN miles

...

使用： SELECT pvt.Name

, isnull(pvt.[vocals], '') [vocals]

, isnull(pvt.[guitar], '') [guitar]

, isnull(pvt.[bass], '') [bass]

, isnull(pvt.[drums], '') [drums]

FROM PivotSource

PIVOT

(

max(tool)

FOR tool

IN ([vocals], [gu

...

我认为问题在于，在第一个SELECT语句中，您应该使用[1]而不是1作为列名称。 SELECT Series_ID

, [1] AS Jan

, [2] AS Feb

, [3] AS Mar

, [4] AS Apr

, [5] AS May

, [6] AS Jun

, [7] AS Jul

, [8] AS Aug

, [9] AS Sep

, [10]

...

不，不会总是得到与原始表相同的表。 这取决于我们正在查看的数据类型。 检查SQL Fiddles以获取@Mahmoud Gamal的数据。 SQL小提琴与SUM SQL小提琴MAX No, will not always get the same table as the original table. It depends on the kind of data that we are looking at. Check the SQL Fiddles for @Mahmoud Gamal 's

...

你的想法是正确的，你需要先unpivot ，以获得你想要的最终结果。 您需要将多列Billings ， Collections和Debtors转换为多行，然后将Period值转换为列。 您没有指定正在使用的SQL Server版本，但从SQL Server 2005开始，您可以使用CROSS APPLY进行取消注释： select

CompanyCode,

AccountClass,

period,

[Type],

Value

from yourtable t

cross a

...

看看这篇文章： 使用PIVOT和UNPIVOT 引用： The following is annotated syntax for PIVOT.

SELECT ,

[first pivoted column] AS ,

[second pivoted column] AS ,

...

[last pivoted column] AS

...

第二个块使用动态查询评估。 '符号只是字符串文字分隔符。 The second block is using dynamic query evaluation. The ' symbols are simply string literal delimiters.

这有点凌乱，但在这里。 首先我使用rank()函数来了解ErrorField的位置(1,2或3)。 所有这一切，我用这个数字为枢轴。 你需要两个枢轴和它们之间的连接。 WITH AuxTable (Data_Error_Key, ErrorField, ErrorValue, NumeroError)

AS

(

SELECT Data_Error_Key, ErrorField, ErrorValue, RANK() OVER (PARTITION BY Data_Error_Key ORDER

...

使用isnull(nullif()) ： declare @ProductId varchar(6);

set @ProductId = 'wq2745'

begin;

select

Quarters = QuarterName

, QuarterValue

from (

select month_key

, sum(Q1) Q1

, sum(Q2) Q2

, sum(Q3) Q3

, sum(Q4) Q4

from

...

UNPIVOT然后PIVOT： SELECT Objective,

[January],

[February],

[March],

[April],

[May],

CASE WHEN Objective IN ('NoOfAllDrugTests','NoOfAllAlcoholTests') THEN CAST([January]+[February]+[March]+[April]+[May]

...