###### tags: `D-CODE`

# SQL

---

:::info
[ Isolation Levels](https://totoroliu.medium.com/%E8%B3%87%E6%96%99%E5%BA%AB-isolation-levels-c587d3103ba4)

* SQL null無法比對運算，因此要用ISNULL把值改為空白後方能比對

:::

---

:::spoiler 常用

	RIGHT('00000000' + CAST(t.pat_no AS varchar), 8)	
	(CAST(FORMAT(DATEADD(YEAR,-1911,  CAST(a.enc_time AS date)), N'yyyMM') AS int)
	CAST(STR(SUBSTRING(a.his_opd_no, 1, 7) + 19110000) AS date)  AS ODR_RO_DATE

:::

---

:::spoiler ROWS BETWEEN
以「視窗框架」：ROWS BETWEEN 2 PRECEDING AND CURRENT ROW為例，這是指定一個視窗，此視窗是從目前資料列前面的第二個資料列作為起始點，而以目前資料列作為結束點，此視窗函數可以操作的資料列視窗大小為三筆資料列。
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：此為預設值，這是指定一個視窗，此視窗從資料分割的第一個資料列作為起始點，而以資料分割的目前資料列作為結束點。
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING：這是指定一個視窗，此視窗從資料分割的目前資料列作為起始點，而以資料分割的最後一個資料列作為結束點。
ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING：這是指定一個視窗，此視窗是從目前資料列後面的第二個資料列作為起始點，而以目前資料列後面的第十個資料列作為結束點。
:::

---

:::spoiler UNPIVOT


SELECT *
    FROM
        (
          Select GroupCol,col2,col3,col4........ FROM SourceTable
          -- 3.1 欲進行反轉置的資料來源
        ) AS p
    UNPIVOT
        (
          ValueCol FOR unPivotCol IN (col2,col3,col4.........) -- ㄧ定要不包含群組欄位
          -- 3.2 ValueCol 和 unPivotCol 為使用者自行命名的欄位名稱
          -- 3.3 ValueCol 的資料為資料列（Row）資料轉成資料行（Column）資料（非群組欄位轉為ValueCol）
          -- 3.4 unPivotCol 的資料為 col2欄位資料、col3欄位資料、col4欄位資料........
        ) AS pv  
        
      
SELECT *
    FROM
        (
          Select Employee,事假,病假,公假,陪產假 FROM #UNPIVOT
        ) AS p
    UNPIVOT
        (
          Hours FOR Kind IN (事假,病假,公假,陪產假)
        ) AS pv

-- OR

SELECT *
    FROM #UNPIVOT AS p
    UNPIVOT
        (
          Hours FOR Kind IN (事假,病假,公假,陪產假)
        ) AS pv

-- AAAAA的陪產假為NULL，UNPIVOT後，AAAAA的假別就不會有陪產假

:::

---
:::spoiler TRY CATCH
BEGIN TRY
BEGIN TRANSACTION
IF @@ERROR = 0

--沒問題就確認交易完成，資料也會在這時候確認新增和修改
COMMIT TRANSACTION
ELSE
--若是有問題就取消交易，讓資料不被這三段T-SQL所影響
ROLLBACK TRANSACTION

END TRY

BEGIN CATCH

END CATCH
:::

---

:::spoiler ALTER
alter TABLE [dbo].[tableName] ALTER COLUMN [FiledName] [nvarchar](40)
:::

---

:::spoiler 查詢主KEY
select From INFORMATION_SCHEMA.KEY_COLUMN_USAGE

:::

---

:::spoiler 欄位合併
SELECT ProductId,
(
SELECT ',' + PO_A.[Owner]
FROM ProductOwner PO_A
WHERE PO_A.ProductId = PO_B.ProductId
FOR XML PATH('')
) AS Owners
FROM ProductOwner PO_B
:::

---

:::spoiler 找出重覆資料
SELECT * FROM table

GROUP BY id

HAVING count(*)>1
:::

---

:::spoiler PATINDEX
SELECT *
FROM TBDEP a
ORDER BY CASE WHEN PATINDEX( '%(舊)%' ,depname) > 0 THEN depno ELSE '' END
:::

---
:::spoiler CTE 日期
WITH mycte AS
(
SELECT CAST('1900-01-01' AS DATETIME) DateValue
UNION ALL
SELECT DateValue + 1
FROM mycte
WHERE DateValue + 1 < '2050-12-31'
)
SELECT DateValue
FROM mycte
OPTION (MAXRECURSION 0)
:::

---

:::spoiler PIVOT

DECLARE @Year nvarchar(40) = '106';
DECLARE @Semester nvarchar(40) ='1';
DECLARE @DayfgID nvarchar(5) ='1';

--select isnull(a.Vaild,0) Vaild,COUNT(1) Amt
--from tVoteTeCategoryUsers a
--join tVoteTeAnswers b
--on a.TeCategoryID=b.TeCategoryID
--and a.StudentID=b.StudentID
--join tVoteTeCategory c
--on a.TeCategoryID=c.TeCategoryID
--where c.ACADYear = @Year and c.Semester = @Semester
--group by isnull(a.Vaild,0)

SELECT [0] inVaild,[1] Vaild
FROM (
select isnull(a.Vaild,0) Vaild
from tVoteTeCategoryUsers a
join tVoteTeAnswers b
on a.TeCategoryID=b.TeCategoryID
and a.StudentID=b.StudentID
join tVoteTeCategory c
on a.TeCategoryID=c.TeCategoryID
where c.ACADYear = @Year and c.Semester = @Semester
) as GroupTable
PIVOT
(
count(Vaild)
FOR Vaild IN ([0], [1])
) AS PivotTable

--https://blog.xuite.net/f8789/DCLoveEP/30800686-SQL+-+%E4%BD%BF%E7%94%A8+PIVOT
SELECT
*
FROM (
SELECT

a.fundsno3, --學校配合款
a.years,
SUM(a.smoney) AS smoney,
SUM(a.appmoney) AS appmoney_核定前金額, --核定前金額,
(
SELECT SUM(smoney)
FROM ITSCHOOLDEPBUDGET
WHERE fundsno3 = a.fundsno3 AND
years = '109'

) AS AA,
(
SELECT SUM(smoney)
FROM ITSCHOOLDEPBUDGET
WHERE fundsno3 = a.fundsno3 AND
years = '110'
) AS BB
FROM ITSCHOOLDEPBUDGET a --預算明細檔
INNER JOIN TBREFCODE b --系統代碼檔
ON b.refcodeno = 'budfunds' AND b.refcode = a.fundsno3
INNER JOIN (
SELECT depno,
CASE WHEN ISNULL(upper,'') = '0000'
THEN depno
ELSE upper
END AS upper
FROM TBDEP --學校部門表
) c
ON c.depno = a.depno
WHERE LEFT(a.budsubno,1) = '4' AND --預算科目
LEFT(a.fundsno3,1) <> 'P' AND --學校配合款
a.years BETWEEN '109' AND '110' AND
a.fundsno3 = '419200'
GROUP BY b.upper,a.fundsno3,b.refname,c.upper,a.years
--ORDER BY b.upper,a.fundsno3,b.refname,c.upper,a.years
) t
PIVOT (
-- 設定彙總欄位及方式
MAX(years)
-- 設定轉置欄位，並指定轉置欄位中需彙總的條件值作為新欄位
FOR years IN ( [109], [110])
) p;
:::

---

:::spoiler STRING_SPLIT

SELECT b.inno, b.money,b.indate, *
FROM TBPENNY_DETIAL a --收款收據-收款收據明細資料表
LEFT JOIN TBINDETAIL b --收入相關-收入明細資料表
ON a.doc_id IN (
SELECT value
FROM STRING_SPLIT(b.pennyautoid, ',')
)
WHERE a.pennyno = '111600-11002220002'

2014版寫法

SELECT Split.a.value('.', 'NVARCHAR(MAX)') AS Value
FROM (
SELECT CAST ('<M>' + REPLACE(CAST (nid_rec AS nvarchar(MAX)) , ',', '</M><M>') + '</M>' AS XML) AS Data
FROM hg_mnid
WHERE nid_id = '06BC'
AND nid_code IN ('011')

) AS A
CROSS APPLY Data.nodes ('/M') AS Split(a)
:::

---

:::spoiler 一對多筆資料

WITH CTE_ITFORM
             AS (
                    SELECT  formno,
                            wruserno,
                            wrusername,
                            depno,
                            (
                                SELECT depname
                                FROM TBDEP
                                WHERE TBDEP.depno = ITFORM.depno
                            ) AS depname,
                            CASE
                              WHEN isbt = 'purchase' THEN '請購'
                              WHEN isbt = 'normal'   THEN '借支'
                            END AS isbt,
                            casename,
                            (
                                SELECT TOP 1 budno + ',' + (
                                                            SELECT budname
                                                            FROM ITSCHOOLDEPBUDGET
                                                            WHERE budno = ITFORM_DETIAL.budno
                                                            )
                                FROM ITFORM_DETIAL
                                WHERE formno = ITFORM.formno
                            ) AS budno,
                            (
                                SELECT SUM(bmoney)
                                FROM ITFORM_DETIAL
                                WHERE ITFORM_DETIAL.formno = ITFORM.formno
                            ) AS bmoney
                    FROM ITFORM
                    WHERE   kind = '1'  AND
            status >= 1 AND
            status < 9  AND
            EXISTS (
                     SELECT 1
                     FROM ITFORM_DETIAL
                            INNER JOIN ITSCHOOLDEPBUDGET
                                        ON ITSCHOOLDEPBUDGET.budno = ITFORM_DETIAL.budno
                            INNER JOIN ITSCHOOLDEP
                                        ON ITSCHOOLDEP.planno = ITSCHOOLDEPBUDGET.planno
                     WHERE (
                              ITSCHOOLDEP.edate = CONVERT(VARCHAR,CAST(YEAR(GETDATE()) AS NVARCHAR) + '-06' + '-01',111) OR
                              ITSCHOOLDEP.edate = CONVERT(VARCHAR,CAST(YEAR(GETDATE()) AS NVARCHAR) + '-12' + '-01',111)
                           )
                   )


                ),

                  CET_WRUSERNO
            AS (
                    SELECT wruserno
                    FROM ITFORM
                    WHERE   kind = '1'  AND
            status >= 1 AND
            status < 9  AND
            EXISTS (
                     SELECT 1
                     FROM ITFORM_DETIAL
                            INNER JOIN ITSCHOOLDEPBUDGET
                                        ON ITSCHOOLDEPBUDGET.budno = ITFORM_DETIAL.budno
                            INNER JOIN ITSCHOOLDEP
                                        ON ITSCHOOLDEP.planno = ITSCHOOLDEPBUDGET.planno
                     WHERE (
                              ITSCHOOLDEP.edate = CONVERT(VARCHAR,CAST(YEAR(GETDATE()) AS NVARCHAR) + '-06' + '-01',111) OR
                              ITSCHOOLDEP.edate = CONVERT(VARCHAR,CAST(YEAR(GETDATE()) AS NVARCHAR) + '-12' + '-01',111)
                           )
                   )
                    GROUP BY wruserno
               )


            SELECT wruserno,
                   (
                        SELECT  b.formno    + ':' +
                                b.isbt      + ':' +
                                b.casename  + ':' +
                                b.budno     + ':' +
                                CAST ( b.bmoney AS Varchar )  + ';'
                        FROM CTE_ITFORM b
                        WHERE b.wruserno = a.wruserno
                        FOR XML PATH('')
                   ) AS formno
            FROM CET_WRUSERNO a
            UNION
            SELECT 'ACC' AS wruserno,
                   (
                        SELECT  b.formno    + ':' +
                                b.isbt      + ':' +
                                b.casename  + ':' +
                                b.budno     + ':' +
                                CAST ( b.bmoney AS Varchar )  + ':' +
                                b.depname     + ':' +
                                b.wrusername + ';'
                        FROM CTE_ITFORM b
                        FOR XML PATH('')
                   ) AS formno
            FROM CTE_ITFORM b

:::

---

:::spoiler 抓小數點後幾位數

SELECT MONEY,*
FROM ITFORM_BUYACT
WHERE PARSENAME(MONEY, 1) > 0
:::

---

:::spoiler 往前抓到有資料為止
select bankno,sdate,remark
from TBINOUTDAY
where sdate = (
SELECT TOP 1 h.sdate
FROM (
SELECT a.sdate AS sdate
FROM TBINOUTDAY a
WHERE a.remark is not null AND a.sdate <= '2020-11-30'
GROUP BY a.sdate
) h
ORDER BY h.sdate DESC
)
order by bankno DESC
:::

---
:::spoiler 指標
CREATE TABLE [#Table_name]
(
COLUMN_NAME nvarchar(100)
)

INSERT INTO #Table_name (COLUMN_NAME)
SELECT a.COLUMN_NAME
FROM (
SELECT b.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES a
LEFT JOIN INFORMATION_SCHEMA.COLUMNS b ON a.TABLE_NAME=b.TABLE_NAME
WHERE TABLE_TYPE='BASE TABLE'
AND a.Table_name IN (
'tSteBuy'
) --輸入想要的表格名稱

) a

--1.不回傳受影響的資料列(可節省網路傳輸),通常如果沒有要看的話,會寫不回傳,又能節省網路傳輸)
--2.雖然不回傳,但不影響@@ROWCOUNT,所以如果想知道影響的資料列,還是能用@@ROWCOUNT來讀取
SET NOCOUNT ON

DECLARE @COLUMN_NAME NVARCHAR(MAX)

--宣告資料指標內容
DECLARE MYCURSOR CURSOR FOR
(
SELECT COLUMN_NAME FROM #Table_name
)
--開啟資料指標
OPEN MYCURSOR
--取得資料指標內第一筆資料
FETCH NEXT FROM MYCURSOR INTO @COLUMN_NAME
--當成功取的資料進入處理程序
WHILE @@FETCH_STATUS = 0
BEGIN

----資料處理區----
-- 找出哪些欄位比較常用(超過原始資料筆數的70%)
DECLARE @query NVARCHAR(MAX) = N'
SELECT ''' + @COLUMN_NAME + ''', '+ 'COUNT(' + @COLUMN_NAME + ')
FROM tSteBuy
WHERE ISNULL(CONVERT(varchar,' + @COLUMN_NAME + '),' + '''' + '''' + ') <> ''''
HAVING COUNT(' + @COLUMN_NAME + ') > (SELECT COUNT(formno) FROM tSteBuy) * 0.7

'
--SELECT @query
EXECUTE sp_executesql @query
--SELECT @COLUMN_NAME , @COLUMN_NAME
--FROM ITFORM
--WHERE ISNULL(@COLUMN_NAME, '') <> ''

------------------
--取得下一筆資料
FETCH NEXT FROM MYCURSOR INTO @COLUMN_NAME
END

--關閉資料指標
CLOSE MYCURSOR

--移除資料指標參考
DEALLOCATE MYCURSOR

--移除暫存表
DROP TABLE #Table_name

:::

---

:::spoiler usp_Del
CREATE PROCEDURE usp_Del_Products
@ProdctID int
AS
BEGIN
SET NOCOUNT ON;

DECLARE @count int

SET @count = (SELECT count(*) FROM [dbo].[Products] WHERE ProdctID = @ProdctID)

IF @count > 0
BEGIN
BEGIN TRY
BEGIN TRANSACTION

DELETE FROM Products WHERE ProdctID = @ProdctID;

IF @@ERROR = 0
BEGIN
commit transaction;--確認交易
END
ELSE
BEGIN
rollback transaction;--回復交易
END
END TRY
BEGIN CATCH
--DB查詢用
SELECT ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage,
ERROR_LINE() AS ErrorLine,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState

--系統拋回訊息用
DECLARE @ErrorMessage As VARCHAR(1000) = CHAR(10)+'錯誤代碼：' +CAST(ERROR_NUMBER() AS VARCHAR)
+CHAR(10)+'錯誤訊息：'+ ERROR_MESSAGE()
+CHAR(10)+'錯誤行號：'+ CAST(ERROR_LINE() AS VARCHAR)
+CHAR(10)+'錯誤程序名稱：'+ ISNULL(ERROR_PROCEDURE(),'')
DECLARE @ErrorSeverity As Numeric = ERROR_SEVERITY()
DECLARE @ErrorState As Numeric = ERROR_STATE()
RAISERROR( @ErrorMessage, @ErrorSeverity, @ErrorState);--回傳錯誤資訊
END CATCH
END
END
:::

---

:::spoiler MERGE
MERGE 語法是 SQL Server 2008 的新語法，可根據與來源資料表聯結的結果，在目標資料表上執行插入、更新或刪除作業。以下就來以範例的方式來實作...

Code：

--建立新資料表名為 大倉庫
Create Table 大倉庫
(
品名 nvarchar(50),
庫存量 integer
);

--新增大倉庫資料表資料
Insert into 大倉庫
Values('中文書', 10),('英文書', 5),('法文書', 1);

--建立新資料表名為 進退貨倉庫
Create Table 進退貨倉庫
(
品名 nvarchar(50),
進退貨量 integer
);

--新增進退貨倉庫資料表資料
Insert into 進退貨倉庫
Values('中文書', 5),('英文書', -5),('日文書', 6);

--查詢 大倉庫 與 進退貨倉庫 兩資料表的資料
Select * From dbo.大倉庫
Select * From dbo.進退貨倉庫

--將兩張表 MERGE
--當兩張表有資料 MERGE 時，且 庫存量 加上 進退貨量 等於零時，則刪除資料
--當兩張表有資料 MERGE 時，將 庫存量 加上 進退貨量 更新到 庫存量
--當兩張表沒有資料 MERGE 時，將 進退貨倉庫 的資料新增到 大倉庫 中
MERGE INTO 大倉庫
USING 進退貨倉庫
ON 大倉庫.品名 = 進退貨倉庫.品名
WHEN MATCHED AND (大倉庫.庫存量 + 進退貨倉庫.進退貨量 = 0) THEN
DELETE
WHEN MATCHED THEN
UPDATE SET 大倉庫.庫存量 = 大倉庫.庫存量 + 進退貨倉庫.進退貨量
WHEN NOT MATCHED THEN
INSERT VALUES(進退貨倉庫.品名, 進退貨倉庫.進退貨量);

--查詢 MERGE 後的結果
Select * From dbo.大倉庫
Select * From dbo.進退貨倉庫
:::

---

:::spoiler ASP.NET BeginTransaction

using (ACAD_NPUSTEntities pubkind = new ACAD_NPUSTEntities())
                    {
                        using (ACAD_NPUSTEntities pubcod = new ACAD_NPUSTEntities())
                        {
                            using (var pubkindTs = pubkind.Database.BeginTransaction())
                            {
                                using (var pubcodTs = pubcod.Database.BeginTransaction())
                                {
                                    pubkind.pubkind.Add(m);
                                    pubkind.Configuration.ValidateOnSaveEnabled = false;
                                    //pubkind.SaveChanges();

                                    for (int i = 0; i < n; i++)
                                    {
                                        var r = new pubcod();
                                        //r.codekind = m.codekind;
                                        //r.code = Request["dt_code[]"].Split(',')[i];
                                        //r.codsc = Request["dt_codsc[]"].Split(',')[i];
                                        //r.ecodsc = Request["dt_ecodsc[]"].Split(',')[i];
                                        //r.status = Request["dt_status[]"].Split(',')[i];
                                        //r.updat_date = DateTime.Now;
                                        //r.user_id = getUserAuthInfo().id ?? null;
                                        //r.updat_time = m.updat_date.Value.ToString("HHmmss");
                                        pubcod.pubcod.Add(r);
                                        //pubcod.SaveChanges();
                                    }

                                    try
                                    {
                                        pubkind.SaveChanges();
                                        pubcod.SaveChanges();

                                        pubkindTs.Commit();
                                        pubcodTs.Commit();

                                    }
                                    catch (Exception)
                                    {
                                        pubkindTs.Rollback();
                                        pubcodTs.Rollback();
                                        TempData["CommMsg"] = "新增作業失敗 !";
                                        return View();
                                    }
                                }
                            }
                        }
                    }
                }

:::

---

:::spoiler TRANSACTION

DECLARE @count int
	  
	SET @count = (SELECT count(*) FROM [dbo].[Products] WHERE ProdctID = @ProdctID)

	IF @count > 0
	 BEGIN  
	   BEGIN TRY	
			BEGIN TRANSACTION

			DELETE FROM Products WHERE ProdctID = @ProdctID;

			IF @@ERROR = 0
			 BEGIN
				commit transaction;--確認交易
			 END 
			ELSE
			 BEGIN
				rollback transaction;--回復交易
			 END
	   END TRY
	   BEGIN CATCH
	     --DB查詢用
		 SELECT ERROR_NUMBER() AS ErrorNumber,
			    ERROR_MESSAGE() AS ErrorMessage,
			    ERROR_LINE() AS ErrorLine,
			    ERROR_PROCEDURE() AS ErrorProcedure,
			    ERROR_SEVERITY() AS ErrorSeverity,
			    ERROR_STATE() AS ErrorState

	     --系統拋回訊息用
		 DECLARE @ErrorMessage As VARCHAR(1000) = CHAR(10)+'錯誤代碼：' +CAST(ERROR_NUMBER() AS VARCHAR)
												 +CHAR(10)+'錯誤訊息：'+	ERROR_MESSAGE()
												 +CHAR(10)+'錯誤行號：'+	CAST(ERROR_LINE() AS VARCHAR)
												 +CHAR(10)+'錯誤程序名稱：'+	ISNULL(ERROR_PROCEDURE(),'')
		 DECLARE @ErrorSeverity As Numeric = ERROR_SEVERITY()
		 DECLARE @ErrorState As Numeric = ERROR_STATE()
		 RAISERROR( @ErrorMessage, @ErrorSeverity, @ErrorState);--回傳錯誤資訊
	   END CATCH
	 END

:::

---
:::spoiler STRING_AGG 資料集組成字串的串接功能

SELECT
	STRING_AGG(name, ',')
FROM F1Driver

:::

---
:::spoiler 流水號

select RIGHT(REPLICATE('0', 6) + CAST(ISNULL(MAX(CAST(CourseNo AS INT)),0)+1 AS varchar) , 6) AS CourseNo

:::

---

:::spoiler SQL Function

USE [Test_NCYU_DEV]
GO
/****** Object:  UserDefinedFunction [dbo].[getPubcod]    Script Date: 2022/6/23 上午 08:36:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- 建立 FUNCTION
/*
08:班級
*/
ALTER FUNCTION [dbo].[getPubcod](@codekind varchar(5), @code varchar(5), @pattern varchar(1)) 
RETURNS varchar(1000)  -- 500若不夠，請加大 
BEGIN
    DECLARE @string varchar(1000)  -- 500若不夠，請加大 
    SET @string = ''

	IF @pattern = '1'
		BEGIN
			 SELECT @string = @string + code + '-' + codsc + ','
			 FROM pubcod
			 WHERE codekind = @codekind AND code = @code
		END
	ELSE
		BEGIN
			 SELECT @string = @string + codsc + ','
			 FROM pubcod
			 WHERE codekind = @codekind AND code = @code
		END
 
	IF LEN(@string) > 1   
	BEGIN
		SET @string = LEFT(@string, LEN(@string) - 1 ) -- 把最後面的逗號後刪除
	END
 
    RETURN @string
END

:::

---

:::spoiler 暫存表

----增加索引 >> 已有執行
--USE [Test_NCYU_DEV]
--GO
--CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
--ON [dbo].[selstch] ([syear],[sem])
--INCLUDE ([stuno],[deptno],[seqno],[cono],[seldomain],[credit])


-- 清除暫存表
IF object_id('tempdb..#crsopenf_CTE') IS NOT NULL DROP TABLE #crsopenf_CTE;


--學生選課資料(暫存表)
SELECT DISTINCT
	b.syear   	   			, b.sem                 , a.acadno, c.colno
	, a.deptno AS sut_deptno, a.secno               , e.secnam, a.grade
	, a.clacod              , a.stuno               , d.cname , b.deptno
	, b.seqno               , b.attdepno            , b.att_y , b.att_cls
	, b.att_grou            , f.seldomain           , f.cono  , f.credit
	, b.termnum             , b.credit AS crs_credit, b.smn	
INTO #crsopenf_CTE
FROM stufile a
INNER JOIN selstch f ON a.stuno = f.stuno
INNER JOIN crsopenf b ON f.syear = b.syear AND f.sem = b.sem AND f.deptno = b.deptno AND f.seqno = b.seqno
INNER JOIN pubdep c ON a.deptno = c.deptno
INNER JOIN sclperson d ON a.idno = d.idno
INNER JOIN pubsec e ON a.deptno = e.deptno AND a.secno = e.secno
WHERE b.actcd <> '1' AND b.domainno NOT IN ('0001', '0002', '0000') AND f.syear = '110' AND f.sem = '1' 
--AND a.stuno = '1100404' 
	

--上課時間
;WITH crstime_CTE AS (
	SELECT
　	  crstime.syear,
	  crstime.sem,
	  clweek,
	  bcl,
	  ecl,
	  crstime.deptno,
	  crstime.seqno 
	FROM crstime,
	     #crsopenf_CTE
	WHERE crstime.syear = #crsopenf_CTE.syear
　	  AND crstime.sem =  #crsopenf_CTE.sem
　	  AND crstime.deptno = #crsopenf_CTE.deptno
	  AND crstime.seqno = #crsopenf_CTE.seqno
),
-- 將上課時間拆成星期一到星期日，並將節次的資料整合在一格內
week_all AS (
	SELECT DISTINCT 
				(
					 SELECT DISTINCT b.bcl + '-' + b.ecl + ','
					 FROM crstime_CTE b 
					 WHERE a.syear = b.syear AND a.sem = b.sem AND a.deptno = b.deptno AND a.seqno = b.seqno AND a.clweek = b.clweek 
					 FOR XML PATH('')
				) AS t_t,			 
				a.syear,
				a.sem,
				a.deptno,
				a.seqno, 
				a.clweek
	FROM crstime_CTE a
)
 
SELECT  a.syear,
		a.sem,
		a.acadno,
		a.colno,
		a.sut_deptno,
		a.secno,
		a.secnam,
		a.grade,
		a.clacod,
		a.stuno,
		a.cname,
		a.deptno,
		a.seqno,
		a.attdepno,
		a.att_y,
		a.att_cls,
		a.att_grou,
		a.seldomain,
		a.cono,
		a.credit,
		a.termnum,
		a.crs_credit,
		a.smn,
	    week_1.t_t AS t_t1,
		week_2.t_t AS t_t2,
		week_3.t_t AS t_t3,
		week_4.t_t AS t_t4,
		week_5.t_t AS t_t5,
		week_6.t_t AS t_t6,
		week_7.t_t AS t_t7		 
FROM #crsopenf_CTE a
LEFT JOIN (SELECT * FROM week_all a WHERE a.clweek = 1) AS week_1 ON a.syear = week_1.syear AND a.sem = week_1.sem AND a.deptno = week_1.deptno AND a.seqno = week_1.seqno
LEFT JOIN (SELECT * FROM week_all a WHERE a.clweek = 2) AS week_2 ON a.syear = week_2.syear AND a.sem = week_2.sem AND a.deptno = week_2.deptno AND a.seqno = week_2.seqno
LEFT JOIN (SELECT * FROM week_all a WHERE a.clweek = 3) AS week_3 ON a.syear = week_3.syear AND a.sem = week_3.sem AND a.deptno = week_3.deptno AND a.seqno = week_3.seqno
LEFT JOIN (SELECT * FROM week_all a WHERE a.clweek = 4) AS week_4 ON a.syear = week_4.syear AND a.sem = week_4.sem AND a.deptno = week_4.deptno AND a.seqno = week_4.seqno
LEFT JOIN (SELECT * FROM week_all a WHERE a.clweek = 5) AS week_5 ON a.syear = week_5.syear AND a.sem = week_5.sem AND a.deptno = week_5.deptno AND a.seqno = week_5.seqno
LEFT JOIN (SELECT * FROM week_all a WHERE a.clweek = 6) AS week_6 ON a.syear = week_6.syear AND a.sem = week_6.sem AND a.deptno = week_6.deptno AND a.seqno = week_6.seqno
LEFT JOIN (SELECT * FROM week_all a WHERE a.clweek = 7) AS week_7 ON a.syear = week_7.syear AND a.sem = week_7.sem AND a.deptno = week_7.deptno AND a.seqno = week_7.seqno
--WHERE a.syear = '110' 
--  AND a.sem = '1'
--  AND a.deptno = '151'
--  AND a.seqno = '0014'

:::

---

:::spoiler CUBE
[CUBE](https://www.mssqltips.com/sqlservertip/6315/group-by-in-sql-server-with-cube-rollup-and-grouping-sets-examples/)
:::

---

:::spoiler CTE + PIVOT

--查詢學生選課的教室及上課時間
--學生選課資料
WITH crsopenf_cte AS ( 
    
	SELECT DISTINCT  
			  b.syear,
			  b.sem,
			  a.acadno,
			  c.colno,
			  a.deptno AS sut_deptno,
			  a.secno,
			  e.secnam,
			  a.grade,
			  a.clacod,
			  a.stuno,
			  d.cname,
			  b.deptno,
			  b.seqno,
			  b.attdepno,
			  b.att_y,
			  b.att_cls,
			  b.att_grou,
			  f.seldomain,
			  f.cono,
			  f.credit,
			  b.termnum,
			  b.credit AS crs_credit,
			  b.smn	
	FROM stufile a,
		 crsopenf b,
		 pubdep c,
		 sclperson d,
		 selstch f,
		 pubsec e
	WHERE a.deptno = e.deptno
	AND a.secno = e.secno
	AND a.deptno = c.deptno
	AND a.idno = d.idno
	AND a.stuno = f.stuno
	AND f.syear = b.syear
	AND f.sem = b.sem
	AND f.deptno = b.deptno
	AND f.seqno = b.seqno
	AND f.syear = '110'
	AND f.sem = '1'
    --AND a.stuno = '1100404'
	AND b.actcd <> '1'
	AND b.domainno NOT IN ('0001', '0002', '0000')

),--上課教室
 class_room_cte AS (
	
    SELECT crsbu.buna,
		   crstime.roomno,
		   crstime.syear,
		   crstime.sem,
		   crstime.deptno,
		   crstime.seqno
    FROM crstime,
         crsbu 
    WHERE crsbu.buno=crstime.buno
      AND crsbu.areano=crstime.areano
)
--上課時間
, crstime_cte AS (

	SELECT
　	  crstime.syear,
	  crstime.sem,  
	  clweek,
	  bcl,
	  ecl,
	  crstime.deptno,
	  crstime.seqno
	FROM crstime 
 
) 
 

SELECT 
	   p.syear,
	   p.sem,
	   p.acadno,
	   p.colno,
	   p.sut_deptno,
	   p.secno,
	   p.secnam,
	   p.grade,
	   p.clacod,
	   p.stuno,
	   p.cname,
	   p.deptno,
	   p.seqno,
	   p.attdepno,
	   p.att_y,
	   p.att_cls,
	   p.att_grou,
	   p.seldomain,
	   p.cono,
	   p.credit,
	   p.smn,
	   (
			SELECT DISTINCT b.buna + '-' + roomno + ',' 
			FROM class_room_cte b
			WHERE p.syear = b.syear AND p.sem = b.sem AND p.deptno = b.deptno AND p.seqno = b.seqno	 
			FOR XML PATH('')
	   ) AS room, --教室
	   p.[1], --星期1
	   p.[2], --星期2
	   p.[3], --星期3
	   p.[4], --星期4
	   p.[5], --星期5
	   p.[6], --星期6
	   p.[7]  --星期7	 
FROM ( 
	SELECT  a.syear,
			a.sem,
			a.acadno,
			a.colno,
			a.sut_deptno,
			a.secno,
			a.secnam,
			a.grade,
			a.clacod,
			a.stuno,
			a.cname,
			a.deptno,
			a.seqno,
			a.attdepno,
			a.att_y,
			a.att_cls,
			a.att_grou,
			a.seldomain,
			a.cono,
			a.credit,
			a.termnum,
			a.crs_credit,
			a.smn,
			x.clweek AS col,
			x.t_t AS value
	FROM crsopenf_cte a
			OUTER APPLY
			(
				SELECT b.clweek, --星期
					   (
							 SELECT DISTINCT c.bcl + '-' + c.ecl + ','
							 FROM crstime_cte c
							 WHERE b.syear = c.syear AND b.sem = c.sem AND b.deptno = c.deptno AND b.seqno = c.seqno AND b.clweek = c.clweek
							 FOR XML PATH('')
					   ) t_t --上課時間
				FROM crstime_cte b
				WHERE a.syear = b.syear
				  AND a.sem = b.sem
				  --AND a.stuno = b.stuno
				  AND a.deptno = b.deptno
				  AND a.seqno = b.seqno
			) x
) t PIVOT (
     MAX([value]) 
	 FOR [col] IN ("1", "2", "3", "4", "5", "6", "7")
) p
--ORDER BY p.stuno,
--		 p.deptno,
--         p.seqno,
--		 p.cono
 

:::

---

:::spoiler REPLICATE
SELECT *,
REPLICATE('1', h.level)
FROM questionnaires_cte h
:::

---

:::spoiler 判斷table或暫存表是否存在

　　IF EXISTS( SELECT * FROM sys.tables WHERE name = 'Ins_opd_check' )
	BEGIN  
		DROP TABLE Ins_opd_check		
	END

	IF object_id('tempdb..#chek_die') IS NOT NULL
	BEGIN  
		DROP TABLE #chek_die	
	END

:::

---

:::spoiler 日期轉換

 CAST(STR('1050101' + 19110000) AS DATETIME)
 
 RIGHT('00000000' + CAST(t.pat_no AS varchar), 8)	
 (CAST(FORMAT(DATEADD(YEAR,-1911,  CAST(a.enc_time AS date)), N'yyyMM') AS int))　
 CAST(STR(SUBSTRING(a.his_opd_no, 1, 7) + 19110000) AS date)  AS ODR_RO_DATE

:::

---

:::spoiler 欄位描述

SELECT *
FROM (
	  SELECT a.TABLE_NAME,
			a.COLUMN_NAME,
			 (
			   SELECT tc.CONSTRAINT_TYPE 
			   FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
					 JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS kcu
					  ON tc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME　AND tc.TABLE_SCHEMA = kcu.TABLE_SCHEMA　AND tc.TABLE_NAME = kcu.TABLE_NAME
			   WHERE tc.TABLE_NAME = a.TABLE_NAME
				 AND kcu.COLUMN_NAME = a.COLUMN_NAME  
			) AS CONSTRAINT_TYPE,
			( 
				 SELECT value   
				 FROM fn_listextendedproperty (NULL, 'schema', a.TABLE_SCHEMA, 'table', a.TABLE_NAME, 'column', default)   
				 WHERE name='MS_Description' and objtype='COLUMN' 
				   AND objname Collate Chinese_Taiwan_Stroke_CI_AS = a.COLUMN_NAME   
			) AS 欄位描述,
		  a.DATA_TYPE,		 
		  a.IS_NULLABLE
	 FROM (
		  SELECT a.TABLE_NAME,
			  b.COLUMN_NAME,
			  a.TABLE_SCHEMA,
			  b.DATA_TYPE,
			  b.IS_NULLABLE,
			  b.CHARACTER_SET_SCHEMA
		  FROM INFORMATION_SCHEMA.TABLES a
			LEFT JOIN INFORMATION_SCHEMA.COLUMNS b
			 ON a.TABLE_NAME = b.TABLE_NAME    
		  --WHERE a.TABLE_NAME LIKE '%opd_enc%'   
		  --　AND b.COLUMN_NAME = 'enc_json'
　	 ) a
) t
WHERE 
　　　　--t.TABLE_NAME LIKE '%D%'   
	  　--AND
	   t.COLUMN_NAME LIKE '%aplx_nor_a_no%'　
	　--AND
	　--CAST(t.欄位描述 AS nvarchar(100)) LIKE '%序號%'
　
--SELECT *
--FROM ho_micd

:::

---

:::spoiler split

WITH split AS (　

	SELECT *
	FROM (
		SELECT Split.a.value('.', 'NVARCHAR(MAX)') AS Value 
		FROM (	 
				SELECT CAST ('<M>' + REPLACE(CAST (nid_rec AS nvarchar(MAX)) , ',', '</M><M>') + '</M>' AS XML) AS Data
				FROM hg_mnid
				WHERE nid_id = '9301'
				  AND nid_code IN ('0')
　			       
		) AS A 
				CROSS APPLY Data.nodes ('/M') AS Split(a)
	) T
	WHERE T.Value LIKE '%糖尿病已進入第二階段管理照護不得再申報第一階段管理照護費%'  

)

SELECT *
FROM (
	SELECT Split.a.value('.', 'NVARCHAR(MAX)') AS Value 
	FROM (	 
			SELECT CAST ('<M>' + REPLACE(CAST (Value AS nvarchar(MAX)) , ':', '</M><M>') + '</M>' AS XML) AS Data
			FROM split			　
　			       
	) AS A 
			CROSS APPLY Data.nodes ('/M') AS Split(a)
) T

:::

---

:::spoiler SQL TO JSON

SELECT '' AS Id,
	　--　A.No AS No, 
		JSON_QUERY(
			'[' + 
					STUFF((
							SELECT ',' + '"' + a.hwchd_cid + '"' 
							FROM hw_mchd b 
							WHERE a.hwchd_oid = b.hwchd_oid
							FOR XML PATH ('')
						), 1, 1, '') 
			　+ ']'
		) AS json
FROM hw_mchd a
FOR JSON AUTO
 

:::

---