SQL Server动态列创建 [英] SQL Server dynamic columns creation

问题描述

我有一个表，其中的列和值如下

I have a table with column and values as below

我如何在第二个表格列中以DYNAMIC列名获取结果-第一个以"prgmg_product_id"开头，其余列为源ID 1"，源ID 2"，源ID" 3英寸

How do I fetch the result as in the second tabular column with the DYNAMIC column names as -- first with "prgmg_product_id" and the rest of the column as "source ID 1","source ID 2", "source ID 3"

推荐答案

要使用Dynamic SQL实现此目的，以下内容将有所帮助:

To achieve this using Dynamic SQL, the below will help:

CREATE TABLE #Prgmg (
    prgmg_product_id INT
    ,source_id_other INT
    );

INSERT #Prgmg (
    prgmg_product_id
    ,source_id_other
    )
VALUES (3310,11478)
    ,(3337,10833)
    ,(3354,11466)
    ,(4039,4846)
    ,(4039,65454)
    ,(4039,65456);

DECLARE @DYColumns NVARCHAR(1000)
    ,@DYSqlQuery NVARCHAR(4000);

--  CREATE THE COLUMNS REQUIRED
SET @DYColumns = STUFF((
            SELECT DISTINCT ',' 
                    + N'sourceID' 
                    + CAST(ROW_NUMBER() OVER (PARTITION BY prgmg_product_id ORDER BY prgmg_product_id, source_id_other) AS NVARCHAR(10))    
            FROM #Prgmg
            FOR XML PATH('')
            ), 1, 1, '');

-- CREATE THE DYNAMIC SQL AND ADD IN THE CREATED COLUMNS
SET @DYSqlQuery = '
    SELECT prgmg_product_id,' 
        +   @DYColumns 
        +   ' FROM (
                SELECT prgmg_product_id
                    ,CAST(N''sourceID'' + CAST(ROW_NUMBER() OVER (
                        PARTITION BY prgmg_product_id ORDER BY prgmg_product_id, source_id_other
                        ) AS NVARCHAR(10)) AS NVARCHAR(100)) AS Col
                    ,source_id_other
                FROM #Prgmg S1
        ) X
        PIVOT(MIN(source_id_other) FOR Col IN (' + @DYColumns + ')) P'

EXECUTE sp_executesql @DYSqlQuery;

尽管这确实为您提供了解决方案，但您应该花一些时间来了解所使用的概念.例如使用 ROW_NUMBER 创建列，以及如何对应于 PIVOT 的使用.

Whilst this does offer you the solution, you should spend time understanding the concepts used. Such as the creation of the Columns required using ROW_NUMBER and the how that maps to the use of the PIVOT.

这篇关于SQL Server动态列创建的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！