sql server 2014将数据与列名相结合
sql server 2014将数据与列名相结合
提问于 2016-08-24 13:27:17
我有一张这样的桌子
Customerno | 8-10 | 10-12 | 12-14 | 14-16 | 16-18 | 18-20|
1 null null Thursday null null null
2 friday null null null wednesday monday我想要一个像这样的基于上面表格的表格
customerno | monday(8-10)| monday(10-12)| monday(12-14) | |friday(18-20)
1 false false false false
2 false false false false仅当客户在该时间和日期访问时才为true
我该怎么做呢？
回答 1
Stack Overflow用户
发布于 2016-08-24 18:00:52
它的代码非常长，我找不到任何其他方法来做到这一点：
DECLARE @CUSTOMER TABLE
(
CUSTOMERID INT,
[MONDAY(8-10)] VARCHAR(10),[MONDAY(10-12)] VARCHAR(10),[MONDAY(12-14)] VARCHAR(10),
[MONDAY(14-16)] VARCHAR(10),[MONDAY(16-18)] VARCHAR(10),[MONDAY(18-20)] VARCHAR(10),
[TUESDAY(8-10)] VARCHAR(10),[TUESDAY(10-12)] VARCHAR(10),[TUESDAY(12-14)] VARCHAR(10),
[TUESDAY(14-16)] VARCHAR(10),[TUESDAY(16-18)] VARCHAR(10),[TUESDAY(18-20)] VARCHAR(10),
[WEDNESDAY(8-10)] VARCHAR(10),[WEDNESDAY(10-12)] VARCHAR(10),[WEDNESDAY(12-14)] VARCHAR(10),
[WEDNESDAY(14-16)] VARCHAR(10),[WEDNESDAY(16-18)] VARCHAR(10),[WEDNESDAY(18-20)] VARCHAR(10),
[THURSDAY(8-10)] VARCHAR(10),[THURSDAY(10-12)] VARCHAR(10),[THURSDAY(12-14)] VARCHAR(10),
[THURSDAY(14-16)] VARCHAR(10),[THURSDAY(16-18)] VARCHAR(10),[THURSDAY(18-20)] VARCHAR(10),
[FRIDAY(8-10)] VARCHAR(10),[FRIDAY(10-12)] VARCHAR(10),[FRIDAY(12-14)] VARCHAR(10),
[FRIDAY(14-16)] VARCHAR(10),[FRIDAY(16-18)] VARCHAR(10),[FRIDAY(18-20)] VARCHAR(10)
)
DECLARE @ID INT,@810 VARCHAR(10),@1012 VARCHAR(10),@1214 VARCHAR(10),@1416 VARCHAR(10),@1618 VARCHAR(10),@1820 VARCHAR(10)
DECLARE @MONDAY810 VARCHAR(10),@MONDAY1012 VARCHAR(10),@MONDAY1214 VARCHAR(10)
DECLARE @MONDAY1416 VARCHAR(10),@MONDAY1618 VARCHAR(10),@MONDAY1820 VARCHAR(10)
DECLARE @TUESDAY810 VARCHAR(10),@TUESDAY1012 VARCHAR(10),@TUESDAY1214 VARCHAR(10)
DECLARE @TUESDAY1416 VARCHAR(10),@TUESDAY1618 VARCHAR(10),@TUESDAY1820 VARCHAR(10)
DECLARE @WEDNESDAY810 VARCHAR(10),@WEDNESDAY1012 VARCHAR(10),@WEDNESDAY1214 VARCHAR(10)
DECLARE @WEDNESDAY1416 VARCHAR(10),@WEDNESDAY1618 VARCHAR(10),@WEDNESDAY1820 VARCHAR(10)
DECLARE @THURSDAY810 VARCHAR(10),@THURSDAY1012 VARCHAR(10),@THURSDAY1214 VARCHAR(10)
DECLARE @THURSDAY1416 VARCHAR(10),@THURSDAY1618 VARCHAR(10),@THURSDAY1820 VARCHAR(10)
DECLARE @FRIDAY810 VARCHAR(10),@FRIDAY1012 VARCHAR(10),@FRIDAY1214 VARCHAR(10)
DECLARE @FRIDAY1416 VARCHAR(10),@FRIDAY1618 VARCHAR(10),@FRIDAY1820 VARCHAR(10)
DECLARE C CURSOR FOR
SELECT CUSTOMERNO,[8-10],[10-12],[12-14],[14-16],[16-18],[18-20] FROM CUSTOMER
OPEN C
FETCH NEXT FROM C INTO @ID,@810,@1012,@1214,@1416,@1618,@1820
WHILE @@FETCH_STATUS=0
BEGIN
----// 8-10 //----------
IF @810 LIKE '%MONDAY%' SET @MONDAY810='TRUE'
ELSE IF @810 LIKE '%TUESDAY%' SET @TUESDAY810='TRUE'
ELSE IF @810 LIKE '%WEDNESDAY%' SET @WEDNESDAY810='TRUE'
ELSE IF @810 LIKE '%THURSDAY%' SET @THURSDAY810='TRUE'
ELSE IF @810 LIKE '%FRIDAY%' SET @FRIDAY810='TRUE'
ELSE
BEGIN
SET @MONDAY810='False'
SET @TUESDAY810='False'
SET @WEDNESDAY810='False'
SET @THURSDAY810='False'
SET @FRIDAY810='False'
END
----// 10-12 //----------
IF @1012 LIKE '%MONDAY%' SET @MONDAY1012='TRUE'
ELSE IF @1012 LIKE '%TUESDAY%' SET @TUESDAY1012='TRUE'
ELSE IF @1012 LIKE '%WEDNESDAY%' SET @WEDNESDAY1012='TRUE'
ELSE IF @1012 LIKE '%THURSDAY%' SET @THURSDAY1012='TRUE'
ELSE IF @1012 LIKE '%FRIDAY%' SET @FRIDAY1012='TRUE'
ELSE
BEGIN
SET @MONDAY1012='False'
SET @TUESDAY1012='False'
SET @WEDNESDAY1012='False'
SET @THURSDAY1012='False'
SET @FRIDAY1012='False'
END
----// 12-14 //----------
IF @1214 LIKE '%MONDAY%' SET @MONDAY1214='TRUE'
ELSE IF @1214 LIKE '%TUESDAY%' SET @TUESDAY1214='TRUE'
ELSE IF @1214 LIKE '%WEDNESDAY%' SET @WEDNESDAY1214='TRUE'
ELSE IF @1214 LIKE '%THURSDAY%' SET @THURSDAY1214='TRUE'
ELSE IF @1214 LIKE '%FRIDAY%' SET @FRIDAY1214='TRUE'
ELSE
BEGIN
SET @MONDAY1214='False'
SET @TUESDAY1214='False'
SET @WEDNESDAY1214='False'
SET @THURSDAY1214='False'
SET @FRIDAY1214='False'
END
----// 14-16//----------
IF @1416 LIKE '%MONDAY%' SET @MONDAY1416='TRUE'
ELSE IF @1416 LIKE '%TUESDAY%' SET @TUESDAY1416='TRUE'
ELSE IF @1416 LIKE '%WEDNESDAY%' SET @WEDNESDAY1416='TRUE'
ELSE IF @1416 LIKE '%THURSDAY%' SET @THURSDAY1416='TRUE'
ELSE IF @1416 LIKE '%FRIDAY%' SET @FRIDAY1416='TRUE'
ELSE
BEGIN
SET @MONDAY1416='False'
SET @TUESDAY1416='False'
SET @WEDNESDAY1416='False'
SET @THURSDAY1416='False'
SET @FRIDAY1416='False'
END
----// 16-18 //----------
IF @1618 LIKE '%MONDAY%' SET @MONDAY1618='TRUE'
ELSE IF @1618 LIKE '%TUESDAY%' SET @TUESDAY1618='TRUE'
ELSE IF @1618 LIKE '%WEDNESDAY%' SET @WEDNESDAY1618='TRUE'
ELSE IF @1618 LIKE '%THURSDAY%' SET @THURSDAY1618='TRUE'
ELSE IF @1618 LIKE '%FRIDAY%' SET @FRIDAY1618='TRUE'
ELSE
BEGIN
SET @MONDAY1618='False'
SET @TUESDAY1618='False'
SET @WEDNESDAY1618='False'
SET @THURSDAY1618='False'
SET @FRIDAY1618='False'
END
----// 18-20 //----------
IF @1820 LIKE '%MONDAY%' SET @MONDAY1820='TRUE' ELSE
IF @1820 LIKE '%TUESDAY%' SET @TUESDAY1820='TRUE'
ELSE IF @1820 LIKE '%WEDNESDAY%' SET @WEDNESDAY1820='TRUE'
ELSE IF @1820 LIKE '%THURSDAY%' SET @THURSDAY1820='TRUE'
ELSE IF @1820 LIKE '%FRIDAY%' SET @FRIDAY1820='TRUE'
ELSE
BEGIN
SET @MONDAY1820='False'
SET @TUESDAY1820='False'
SET @WEDNESDAY1820='False'
SET @THURSDAY1820='False'
SET @FRIDAY1820='False'
END
IF @MONDAY810 IS NULL SET @MONDAY810 ='TRUE'
IF @MONDAY1012 IS NULL SET @MONDAY1012 ='TRUE'
IF @MONDAY1214 IS NULL SET @MONDAY1214 ='TRUE'
IF @MONDAY1416 IS NULL SET @MONDAY1416 ='TRUE'
IF @MONDAY1618 IS NULL SET @MONDAY1618 ='TRUE'
IF @MONDAY1820 IS NULL SET @MONDAY1820 ='TRUE'
IF @TUESDAY810 IS NULL SET @TUESDAY810 ='TRUE'
IF @TUESDAY1012 IS NULL SET @TUESDAY1012 ='TRUE'
IF @TUESDAY1214 IS NULL SET @TUESDAY1214 ='TRUE'
IF @TUESDAY1416 IS NULL SET @TUESDAY1416 ='TRUE'
IF @TUESDAY1618 IS NULL SET @TUESDAY1618 ='TRUE'
IF @TUESDAY1820 IS NULL SET @TUESDAY1820 ='TRUE'
IF @WEDNESDAY810 IS NULL SET @WEDNESDAY810 ='TRUE'
IF @WEDNESDAY1012 IS NULL SET @WEDNESDAY1012='TRUE'
IF @WEDNESDAY1214 IS NULL SET @WEDNESDAY1214='TRUE'
IF @WEDNESDAY1416 IS NULL SET @WEDNESDAY1416='TRUE'
IF @WEDNESDAY1618 IS NULL SET @WEDNESDAY1618='TRUE'
IF @WEDNESDAY1820 IS NULL SET @WEDNESDAY1820='TRUE'
IF @THURSDAY810 IS NULL SET @THURSDAY810 ='TRUE'
IF @THURSDAY1012 IS NULL SET @THURSDAY1012 ='TRUE'
IF @THURSDAY1214 IS NULL SET @THURSDAY1214 ='TRUE'
IF @THURSDAY1416 IS NULL SET @THURSDAY1416 ='TRUE'
IF @THURSDAY1618 IS NULL SET @THURSDAY1618 ='TRUE'
IF @THURSDAY1820 IS NULL SET @THURSDAY1820 ='TRUE'
IF @FRIDAY810 IS NULL SET @FRIDAY810 ='TRUE'
IF @FRIDAY1012 IS NULL SET @FRIDAY1012 ='TRUE'
IF @FRIDAY1214 IS NULL SET @FRIDAY1214 ='TRUE'
IF @FRIDAY1416 IS NULL SET @FRIDAY1416 ='TRUE'
IF @FRIDAY1618 IS NULL SET @FRIDAY1618 ='TRUE'
IF @FRIDAY1820 IS NULL SET @FRIDAY1820 ='TRUE'
INSERT INTO @CUSTOMER
VALUES
( @ID,@MONDAY810 ,@MONDAY1012 ,@MONDAY1214,
@MONDAY1416 ,@MONDAY1618 ,@MONDAY1820,
@TUESDAY810 ,@TUESDAY1012 ,@TUESDAY1214,
@TUESDAY1416 ,@TUESDAY1618 ,@TUESDAY1820,
@WEDNESDAY810 ,@WEDNESDAY1012 ,@WEDNESDAY1214,
@WEDNESDAY1416 ,@WEDNESDAY1618 ,@WEDNESDAY1820,
@THURSDAY810 ,@THURSDAY1012 ,@THURSDAY1214,
@THURSDAY1416 ,@THURSDAY1618 ,@THURSDAY1820,
@FRIDAY810 ,@FRIDAY1012 ,@FRIDAY1214,
@FRIDAY1416 ,@FRIDAY1618 ,@FRIDAY1820 )
FETCH NEXT FROM C INTO @ID,@810,@1012,@1214,@1416,@1618,@1820
END
CLOSE C
DEALLOCATE C
SELECT * FROM @CUSTOMER页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/39115065
复制相关文章
相似问题
腾讯云开发者
