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