sql-server - 如何动态将行转换为列并为每列设置不同的列名
问题描述
如何将行转换为列并为每列创建不同的名称?
create table #TempTable (InvoiceNum int,State varchar(2), ChargeName varchar(50), PercentageRate decimal(5,3), FlatRate decimal(5,2))
insert into #TempTable values (235736, 'AZ','Inspection & Policy Fee', NULL,250.00)
,(235736, 'AZ','Surplus Line Tax',0.03,NULL)
,(235736, 'AZ','Stamping Fee',0.002,NULL
)
我需要这样的东西:
更新:
使用示例我能够取消它,但结果不是我想要的:
create table #TempTable (InvoiceNum int,State varchar(2), ChargeName varchar(50), PercentageRate decimal(5,3), FlatRate decimal(5,2))
insert into #TempTable values (235736, 'AZ','Inspection & Policy Fee', NULL,250.00)
,(235736, 'AZ','Surplus Line Tax',0.03,NULL)
,(235736, 'AZ','Stamping Fee',0.002,NULL)
--select * from @TempTable
Declare @SQL nvarchar(max),
@query nvarchar(max)
select @SQL = STUFF((SELECT ',' + QUOTENAME(ChargeName)
from #TempTable
group by ChargeName, InvoiceNum
order by InvoiceNum
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
--select @SQL
set @SQL = 'SELECT ' + @SQL + ' from
(
select PercentageRate, ChargeName
from #TempTable
) x
pivot
(
max(PercentageRate)
for ChargeName in (' + @SQL + ')
) p '
exec sp_executesql @SQL;
更新:
运行下面的查询给了我这个:
为什么 ChargeName 不在第一行?我希望看到它是这样的:我错过了什么?
declare @TempTable table (InvoiceNum int,StateID varchar(2), ChargeName varchar(50), PercentageRate decimal(5,3), FlatRate decimal(5,2))
insert into @TempTable values (235736, 'AZ','Inspection & Policy Fee', NULL,250.00)
,(235736, 'AZ','Surplus Line Tax',0.03,NULL)
,(235736, 'AZ','Stamping Fee',0.002,NULL)
select
InvoiceNum,
ChargeName,
StateID,
PercentageRate,
FlatRate,
row_number() over (partition by InvoiceNum order by ChargeName) as RN
into #TempTable
from @TempTable #TempTable
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
+ QUOTENAME(RN)
FROM (SELECT DISTINCT RN FROM #TempTable) AS RN
--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
N'SELECT InvoiceNum, ' + @ColumnName + '
FROM #TempTable
PIVOT(MAX(ChargeName)
FOR RN IN (' + @ColumnName + ')) AS PVTTable'
EXEC sp_executesql @DynamicPivotQuery
drop table #TempTable
解决方案
我会根据需要多次加入临时表。鉴于您的#TempTable
SELECT T1.InvoiceNum,
Tax1_Jurisdiction = T1.State, Tax1_TaxType = T1.ChargeName, Tax1_Percent = T1.PercentageRate, Tax1_FixedRate = T1.FlatRate,
Tax2_Jurisdiction = T2.State, Tax2_TaxType = T2.ChargeName, Tax2_Percent = T2.PercentageRate, Tax2_FixedRate = T2.FlatRate,
Tax3_Jurisdiction = T3.State, Tax3_TaxType = T3.ChargeName, Tax3_Percent = T3.PercentageRate, Tax3_FixedRate = T3.FlatRate
FROM #TempTable T1
JOIN #TempTable T2 ON T1.InvoiceNum = T2.InvoiceNum
JOIN #TempTable T3 ON T1.InvoiceNum = T3.InvoiceNum
WHERE T1.ChargeName = 'Inspection & Policy Fee'
AND T2.ChargeName = 'Surplus Line Tax'
AND T3.ChargeName = 'Stamping Fee'
;
推荐阅读
- javascript - Selenium 检查 WhatsApp 文件上传是否已完成
- sql - 设置完整性的子选择语句
- javascript - Fetch 函数被调用两次
- postgresql - 如何在 postgres 中选择当月的第一天和下个月的第一天(从当月开始)
- java - 一次投射 Spark 数据框现有模式
- c - CTRL + C 不会杀死 C 脚本
- java - 无法查看 PDF 内容和下载 PDF - Apache POI
- javascript - highlight.js 在本地完美运行 - 在 Web 服务器上它根本不起作用(没有控制台问题)
- ssl - 命令行:heroku pg:psql ... 获取“psql:错误:SSL 错误:证书验证失败”
- api - Swagger ASP.NET Core Minimal API 包含 XML 注释文件