sqlserver拆分逗號詳解

一、sqlserver逗號行轉列

要將一列中包含逗號的數據進行行轉列，可以使用sqlserver內置函數pivot。假設有以下數據：

NAME    FRUIT
A       Apple,Banana
B       Orange,Strawberry
C       Grapes

將FRUIT列進行行轉列，可以這樣寫：

SELECT NAME, [1] AS FRUIT1, [2] AS FRUIT2
FROM(
SELECT NAME, VALUE, ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY (SELECT NULL)) AS RN
FROM TABLENAME
CROSS APPLY STRING_SPLIT(FRUIT, ',')
) SRC
PIVOT(MAX(VALUE)) FOR RN IN([1], [2])) P

結果如下：

NAME   FRUIT1   FRUIT2
A      Apple    Banana
B      Orange   Strawberry
C      Grapes   NULL

二、sqlserver逗號分割

有時候需要將一列中包含逗號分割的字符串進行解析成多行數據。可以使用sqlserver的內置函數STRING_SPLIT。假設有以下數據：

ID    DATA
1     A,B,C
2     D,E,F

將DATA列進行逗號分割，可以這樣寫：

SELECT ID, VALUE
FROM TABLENAME
CROSS APPLY STRING_SPLIT(DATA, ',')

結果如下：

ID    VALUE
1     A
1     B
1     C
2     D
2     E
2     F

三、sqlserver行轉列用逗號隔開

有時候需要將一列中的多行數據轉換成一行並用逗號隔開。可以使用sqlserver的內置函數STUFF和FOR XML PATH。假設有以下數據：

ID    TYPE
1     A
1     B
1     C
2     D
2     E
2     F

將TYPE列進行行轉列用逗號隔開，可以這樣寫：

SELECT ID, STUFF((SELECT ',' + TYPE
                  FROM TABLENAME
                  WHERE ID = T.ID
                  FOR XML PATH ('')), 1, 1, '') AS TYPES
FROM TABLENAME T
GROUP BY ID

結果如下：

ID    TYPES
1     A,B,C
2     D,E,F

四、sqlserver逗號拼接字符串

有時候需要將多行數據拼接成一行並用逗號隔開。可以使用sqlserver的內置函數STUFF和FOR XML PATH。假設有以下數據：

ID    TYPE
1     A
1     B
1     C
2     D
2     E
2     F

將TYPE列進行逗號拼接，可以這樣寫：

SELECT STUFF((SELECT ',' + TYPE
              FROM TABLENAME
              FOR XML PATH('')), 1, 1, '') AS TYPES

結果如下：

TYPES
A,B,C,D,E,F

五、sqlserver將一列按逗號拆分多列

有時候需要將一列中包含逗號的數據進行拆分成多列。可以使用sqlserver的內置函數PARSENAME。假設有以下數據：

NAME    FRUIT
A       Apple,Banana
B       Orange,Strawberry
C       Grapes

將FRUIT列按逗號拆分成多列，可以這樣寫：

SELECT NAME, PARSENAME(REPLACE(FRUIT, ',', '.'), 2) AS FRUIT1, PARSENAME(REPLACE(FRUIT, ',', '.'), 1) AS FRUIT2
FROM TABLENAME

結果如下：

NAME   FRUIT1   FRUIT2
A      Banana   Apple
B      Strawberry   Orange
C      NULL   Grapes

六、sqlserver一條記錄拆分多條

有時候需要將一條記錄中包含逗號的數據進行拆分成多條。可以使用sqlserver的內置函數STRING_SPLIT。假設有以下數據：

ID    NAME    FRUIT
1     A       Apple,Banana
2     B       Orange,Strawberry
3     C       Grapes

將FRUIT列按逗號拆分成多條，可以這樣寫：

SELECT ID, NAME, VALUE AS FRUIT
FROM TABLENAME
CROSS APPLY STRING_SPLIT(FRUIT, ',')

結果如下：

ID    NAME    FRUIT
1     A       Apple
1     A       Banana
2     B       Orange
2     B       Strawberry
3     C       Grapes

七、sqlserver實現分頁查詢

當數據量較大時，需要進行分頁查詢。可以使用sqlserver的內置函數ROW_NUMBER() OVER()。假設有以下數據：

ID    NAME
1     A
2     B
3     C
4     D
5     E
6     F
7     G
8     H
9     I
10    J

使用ROW_NUMBER() OVER()實現分頁查詢，可以這樣寫：

DECLARE @pageSize INT = 5 --每頁顯示條數
DECLARE @pageNum INT = 2 --當前頁數
SELECT ID, NAME
FROM(
SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RowNum, ID, NAME
FROM TABLENAME
) T
WHERE T.RowNum > (@pageNum - 1) * @pageSize AND T.RowNum <= @pageNum * @pageSize

結果如下：

ID    NAME
6     F
7     G
8     H
9     I
10    J

八、sqlserver分組查詢

有時候需要對數據進行分組查詢。可以使用sqlserver的內置函數GROUP BY。假設有以下數據：

ID    NAME    AGE
1     A       20
2     B       18
3     C       20
4     D       21
5     E       18
6     F       22
7     G       20
8     H       21
9     I       20
10    J       22

使用GROUP BY實現年齡分組查詢，可以這樣寫：

SELECT AGE, COUNT(*) AS COUNT
FROM TABLENAME
GROUP BY AGE

結果如下：

AGE   COUNT
18    2
20    4
21    2
22    2

九、sqlserver加序號

有時候需要對數據進行加序號操作。可以使用sqlserver的內置函數ROW_NUMBER() OVER()。假設有以下數據：

NAME    AGE
A       20
B       18
C       20
D       21
E       18
F       22
G       20
H       21
I       20
J       22

使用ROW_NUMBER() OVER()實現加序號操作，可以這樣寫：

SELECT ROW_NUMBER() OVER(ORDER BY NAME) AS RowNum, NAME, AGE
FROM TABLENAME

結果如下：

RowNum   NAME   AGE
1        A      20
2        B      18
3        C      20
4        D      21
5        E      18
6        F      22
7        G      20
8        H      21
9        I      20
10       J      22