请帮我透镜下面的SQL表

本文介绍了请帮我透镜下面的SQL表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 SourceAccountNoGSTNumberA11231A11232A11233A21241A21242A31243A31244A31251A31252A41253 预计产量 output expectedAccountNoColumn1Column2Column3Column4A1 123112321233A2 12411242A3 1243124412511252A4 1253 b $ b 我的尝试： What I have tried:select * from (select AccountNo, GSTIN from [GST Sales Report]) as sPIVOT(GSTIN FOR GSTIN in () )as pvt 推荐答案 您需要将值放在FOR列表中并使用函数，例如 You need to put the values in that FOR list and use a function e.g. clare @source table (AccountNo varchar(2), GSTNumber varchar (5))insert into @source (AccountNo, GSTNumber) values('A1','GST1'),('A1','GST2'),('A1','GST3'),('A2','GST1'),('A2','GST2'),('A3','GST1'),('A3','GST2'),('A3','GST3'),('A3','GST4'),('A4','GST1')SELECT * FROM (select AccountNo, GSTNumber from @source) srcPIVOT(MAX(GSTNumber)FOR GSTNumber in (GST1, GST2, GST3, GST4)) as pvt你可以轻松使用MIN（GSTNumber），但你必须有一个函数You could just have easily used MIN(GSTNumber), but you do have to have a function there 对于动态列，你可以尝试下面的解决方案 For Dynamic columns you can try below solutionCREATE table #source (AccountNo varchar(2), GSTNumber varchar (5) ,GSTAmount INT)insert into #source (AccountNo, GSTNumber,GSTAmount) values('A1','GST1',1231),('A1','GST2',1232),('A1','GST3',1233),('A2','GST1',1241),('A2','GST2',1242),('A3','GST1',1243),('A3','GST2',1244),('A3','GST3',1251),('A3','GST4',1252),('A4','GST1',1253)DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)SELECT @cols = STUFF ( ( SELECT ',' + QUOTENAME(GSTNumber) FROM #source GROUP BY GSTNumber ORDER BY GSTNumber FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)'), 1,1,'' );SET @query = 'SELECT AccountNo,' + @cols + ' FROM ( SELECT AccountNo,GSTNumber,GSTAmount FROM #source ) x PIVOT ( MAX(GStAmount) FOR GSTNumber IN (' + @cols + ') ) p ';EXECUTE(@query); 这篇关于请帮我透镜下面的SQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！