SQL面试题-SQL行转列

需求截图

教师号	星期号	是否有课
1	2	有
1	3	有
2	1	有
2	2	有
3	2	有
1	2	有

 

教师号	星期一	星期二	星期三
1	0	2	1
2	1	1	0
3	0	1	0

/*
    SQL面试题-SQL行转列
    LDH @ 2021-10-14
    https://www.cnblogs.com/LifeDecidesHappiness/p/9020685.html
*/

USE TestDB;
GO
-- 删除临时表
IF OBJECT_ID('tempdb..#Table') IS NOT NULL
BEGIN
    DROP TABLE #Table;
END;

IF OBJECT_ID('tempdb..#Table2') IS NOT NULL
BEGIN
    DROP TABLE #Table2;
END;


CREATE TABLE #Table
(
    TeacherNo INT NOT NULL,
    WeekNo NVARCHAR(10) NOT NULL,
    ClassNum INT NOT NULL
);

CREATE TABLE #Table2
(
    TeacherNo INT NOT NULL,
    WeekNo INT NOT NULL,
    HasClass NVARCHAR(2)
);

INSERT INTO #Table2
(
    TeacherNo,
    WeekNo,
    HasClass
)
VALUES
(1, 2, N'有'),
(1, 3, N'有'),
(2, 1, N'有'),
(2, 2, N'有'),
(3, 2, N'有'),
(1, 2, N'有');

SELECT TeacherNo,
       WeekNo,
       HasClass
FROM #Table2;

/*
   WITH it (TeacherNo, WeekNo, HasClass)
AS (SELECT TeacherNo,
           WeekNo = (CASE WeekNo
                         WHEN 1 THEN
                             N'星期一'
                         WHEN 2 THEN
                             N'星期二'
                         WHEN 3 THEN
                             N'星期三'
                     END
                    ),
           HasClass = (CASE HasClass
                           WHEN '有' THEN
                               1
                           ELSE
                               0
                       END
                      )
    FROM #Table2)

SELECT it.TeacherNo,
       it.WeekNo,
       ClassNum = COUNT(it.HasClass)
FROM it
GROUP BY it.TeacherNo,
         it.WeekNo
ORDER BY it.TeacherNo ASC;
*/

WITH it (TeacherNo, WeekNo, HasClass)
AS (SELECT TeacherNo,
           WeekNo = (CASE WeekNo
                         WHEN 1 THEN
                             N'星期一'
                         WHEN 2 THEN
                             N'星期二'
                         WHEN 3 THEN
                             N'星期三'
                     END
                    ),
           HasClass = (CASE HasClass
                           WHEN '有' THEN
                               1
                           ELSE
                               0
                       END
                      )
    FROM #Table2)
INSERT INTO #Table
(
    TeacherNo,
    WeekNo,
    ClassNum
)
SELECT it.TeacherNo,
       it.WeekNo,
       ClassNum = COUNT(it.HasClass)
FROM it
GROUP BY it.TeacherNo,
         it.WeekNo
ORDER BY it.TeacherNo ASC;

--    行转列
SELECT '教师号' = X.TeacherNo,
       '星期一' = ISNULL(X.星期一, ''),
       '星期二' = ISNULL(X.星期二, ''),
       '星期三' = ISNULL(X.星期三, '')
FROM #Table AS Temp
    PIVOT
    (
        SUM(ClassNum)
        FOR Temp.WeekNo IN (星期一, 星期二, 星期三)
    ) AS X;