SQL 行转列(列的值不规则的数目)

﹏ヽ暗。殇╰゛Y 2021-11-02 01:36 346阅读 0赞

--创建一个临时表用来存储数据 
    create  table #tmp_SNValue_Table (FieldName nvarchar(20), [Value] nvarchar(max))
    
    insert into #tmp_SNValue_Table
    select 'DDUDF01','123456789012345'
    insert into #tmp_SNValue_Table
    select 'DDUDF01','123456789012346'
    insert into #tmp_SNValue_Table
    select 'DDUDF02','123456789012347'
    
    select * from #tmp_SNValue_Table

目前的效果:

![340630-20180724170124822-1149648479.png][]

--定义一个变量存储SQL语句
    declare @ls_TSQL nvarchar(max) =''
    
    --用来存储需要查询出来的列(column).
    DECLARE @ls_FieldName NVARCHAR(100)
    
    --动态列. (ColumnName1,ColumnName2)
    SELECT @ls_FieldName=ISNULL(@ls_FieldName+',','')+ QUOTENAME(FieldName) FROM  #tmp_SNValue_Table GROUP BY FieldName
    
    
    --查询并且 行转列(包含空列, 不规则的数目)
     set @ls_TSQL = @ls_TSQL+ '
      SELECT '+@ls_FieldName+' FROM (
    SELECT FieldName,value,row_number() over  (partition by FieldName ORDER BY FieldName ASC) rowno 
    FROM #tmp_SNValue_Table) r PIVOT(max(value) FOR FieldName IN('+@ls_FieldName+') )g 
      
      '
     print @ls_TSQL
     exec(@ls_TSQL)

想要的效果(只是做查询, 不做动态insert)

![340630-20180724170651358-479049977.png][]

如果需要动态列插入

create table #tmp_Sca_Pac_Dtl(
    	DDUDF01 nvarchar(100) null,
    	DDUDF02 nvarchar(100) null,
    	DDUDF03 nvarchar(100) null,
    	DDUDF04 nvarchar(100) null,
    	DDUDF05 nvarchar(100) null,
    	DDUDF06 nvarchar(100) null,
    	DDUDF07 nvarchar(100) null,
    	DDUDF08 nvarchar(100) null,
    	DDUDF09 nvarchar(100) null,
    	DDUDF10 nvarchar(100) null,
    	DDUDF11 nvarchar(100) null,
    	DDUDF12 nvarchar(100) null,
    	DDUDF13 nvarchar(100) null,
    	DDUDF14 nvarchar(100) null,
    	DDUDF15 nvarchar(100) null,
    	DDUDF16 nvarchar(100) null,
    	DDUDF17 nvarchar(100) null,
    	DDUDF18 nvarchar(100) null,
    	DDUDF19 nvarchar(100) null,
    	DDUDF20 nvarchar(100) null
    )
    
    
    declare @ls_TSQL nvarchar(max) =''
    DECLARE @ls_FieldName NVARCHAR(100)
    SELECT @ls_FieldName=ISNULL(@ls_FieldName+',','')+ QUOTENAME(FieldName) FROM  #tmp_SNValue_Table GROUP BY FieldName
    --动态插入另一个Temp table里面
     set @ls_TSQL = 'insert into #tmp_Sca_Pac_Dtl ('+@ls_FieldName+')'
    
     --set @ls_TSQL = 'insert into #tmp_Sca_Pac_Dtl ('+@ls_FieldName+')'
     set @ls_TSQL = @ls_TSQL+ '
      SELECT '+@ls_FieldName+' FROM (
    SELECT FieldName,value,row_number() over  (partition by FieldName ORDER BY FieldName ASC) rowno 
    FROM #tmp_SNValue_Table) r PIVOT(max(value) FOR FieldName IN('+@ls_FieldName+') )g 
      
      '
     print @ls_TSQL
     exec(@ls_TSQL)
     select * from #tmp_Sca_Pac_Dtl

效果

![340630-20180724170906245-812808981.png][]

转载于:https://www.cnblogs.com/hesijian/p/9361209.html

[340630-20180724170124822-1149648479.png]: /images/20211101/4394bc085a374ba7b753a4f26438dedd.png
[340630-20180724170651358-479049977.png]: /images/20211101/9f8c56a01caa4de1a191a276e587bde6.png
[340630-20180724170906245-812808981.png]: /images/20211101/3b34b084eaad468eb5b1e8b017524a9e.png