如何从一个表中选择单个列中的多个值，然后插入到其他表中 [英] How to select multiple values in single column from one table and then insert into other table

问题描述

ID | Colu |

- | ----- | -----

1 | ul，rl，a |

- | ----- | ----

2 | al，si |




表上的
Colu包含2个变量，如何选择变量以及如何使用存储过程插入到其他表中

ID|Colu|
--|-----|-----
1 |ul,rl,a|
--|-----|----
2 |al,si|


above table Colu contain 2 variables, how that select variables and how to insert into other table by using stored procedure

推荐答案

DECLARE @cols VARCHAR(MAX)

WITH CTE
AS
(
    SELECT
        ROW_NUMBER() OVER(PARTITION BY [DATE] ORDER BY [DATE]) AS RowNbr,
        convert(varchar, [DATE], 103) AS [Date]
    FROM
        Table1
)
SELECT @cols=STUFF
(
    (
        SELECT
            ',' +QUOTENAME([Date])
        FROM
            CTE
        WHERE
            CTE.RowNbr=1
    FOR XML PATH('')
    )
,1,1,'')

DECLARE @query NVARCHAR(4000)=
N'SELECT
    *
FROM
(
    SELECT top(1)
        Table1.NAME,
        CAST(Table1.[Y/N] AS INT) AS [Y/N],
        convert(varchar, Table1.[DATE], 103) AS [Date]
    FROM
        Table1
) AS p
PIVOT
(
    MAX([Y/N])
    FOR [Date] IN ('+@cols+')
) AS pvt'

EXECUTE(@query)

这篇关于如何从一个表中选择单个列中的多个值，然后插入到其他表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！