请帮我透镜下面的SQL表
问题描述：
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 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);