请帮我透镜下面的SQL表 [英] Please help me pivot SQL table below

问题描述

Source	
AccountNo	GSTNumber
A1	1231
A1	1232
A1	1233
A2	1241
A2	1242
A3	1243
A3	1244
A3	1251
A3	1252
A4	1253

预计产量

output expected

AccountNo	Column1	Column2	Column3	Column4
A1	          1231	1232	1233	
A2	          1241	1242		
A3	          1243	1244	1251	1252
A4	          1253			

< br $> b $ b

我的尝试：

What I have tried:

select * from 
(select AccountNo, GSTIN from [GST Sales Report]) as s
PIVOT
(
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) src
	PIVOT
	(
		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 solution

CREATE  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表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！