如何将行转换为列

I have a table named Shift and I am defining OffDays in this table using below mentioned columns.

我有一个名为Shift的表,我使用下面提到的列在此表中定义OffDays。

   CREATE TABLE [tbl_Shift](

    [OffDay1] [nvarchar](25) NOT NULL CONSTRAINT [DF_tbl_Shift_OffDay1]  DEFAULT (N'Sunday'),
    [IsAlternateOffDay2] [bit] NULL,
    [OffDay2] [nvarchar](25) NULL
    )

INSERT INTO [tbl_Shift] VALUES ('Sunday', 'True', 'Saturday')

AlternateOffDay is bit so if it is True than OffDay2 can be defined. I want the result to be shown like below in case I have OffDay2 as Saturday.

AlternateOffDay是位,如果它是True,则可以定义OffDay2。我希望结果显示如下,如果我有星期六的OffDay2。

Holidays 
----------
Sunday 
Saturday

I have tried this but the result comes up in 2 columns and 1 row and also it skips 2nd if first is not null but that's not the problem. I just want them to be in 2 rows.

我试过这个,但结果出现在2列和1行中,如果第一个不是null,它也会跳过第2个,但这不是问题。我只想让它们分成两排。

Select DISTINCT ISNULL(OffDay1,OffDay2) from [HRM].[tbl_Shift]

1 个解决方案

#1

In this case, the simplest solution is to use a UNION (which implicitly does a DISTINCT):

在这种情况下,最简单的解决方案是使用UNION(隐式执行DISTINCT):

(simplified, just add any WHERE clause as necessary to e.g. ignore OffDay2 where the flag is not set)

(简化,只需添加任何WHERE子句,例如忽略未设置标志的OffDay2)

SELECT OffDay1 FROM [HRM].[tbl_Shift]
UNION
SELECT OffDay2 FROM [HRM].[tbl_Shift]

Alternatively, you could look into UNPIVOT which is used for switching column values to multiple rows. Something like this:

或者,您可以查看UNPIVOT,它用于将列值切换为多行。像这样的东西:

SELECT DISTINCT TheDay
FROM   
   (SELECT OffDay1, OffDay2  
   FROM tbl_Shift) p  
UNPIVOT  
   (TheDay FOR EachDay IN   
      (OffDay1, OffDay2)  
)AS unpvt;

#1