在SQL Server中动态创建表 [英] Dynamic table creation in SQL Server

问题描述

我有这样的情况

+---------+-----------+------------+
| FieldNo | FieldName | Substring  |
+---------+-----------+------------+
|       1 | A         | 8          |
|       1 | A         | A          |
|       1 | A         | DC         |
|       2 | B         | 7          |
|       3 | C         | 22         |
|       3 | C         | 37         |
+---------+-----------+------------+

需要这样的输出:

+----+------+------+
| A  |  B   |  C   |
+----+------+------+
| 8  | 7    | 22   |
| A  | Null | 37   |
| DC | Null | Null |
+----+------+------+

任何建议如何在SQL Server中做到这一点?

Any suggestions how I can do this in SQL Server?

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(Field_name) 
                    from bear_crossjoin
                    group by FIELD_NAME, FIELDNUMBER
                    order by FIELDNUMBER
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @cols + N' from 
             (
                select substring, Field_name
                from bear_crossjoin
            ) x
            pivot 
            (
                max(substring)
                for field_name in (' + @cols + N')
            ) p '

exec sp_executesql @query

推荐答案

我假设表名称为tblTemp.试试这个

I've assumed the table name as tblTemp. Try this

DECLARE @colList NVARCHAR(max)
DECLARE @query NVARCHAR(max)

select @colList = coalesce(@colList + ',', '') +  convert(varchar(12),TT.FIELDNAME)
from (SELECT DISTINCT FIELDNAME FROM tblTemp) TT
order by TT.FIELDNAME


SET @query = 'SELECT ' + @colList + ' FROM
(
SELECT row_number() over(partition by FIELDNO
                        order by FIELDNO) seq,
       FieldName, [Substring]
FROM tblTemp ) als
PIVOT
( MAX([SUBSTRING])
  FOR FieldName IN (' + @colList + ')
) piv'

exec sp_executesql @query

小提琴

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