All geek questions in one place

T-SQL Summary

I have a group of people. Lets call them A, B, C. I have a table that shows how much they were paid each month ....

PERSON|MONTH|PAID A JAN 10 A FEB 20 B JAN 10 B FEB 20 B SEP 30 C JAN 10 C JUNE 20 C JULY 30 C SEP 40

This table can and continues for years and years.

Is there a way to rotate this table (nothing, as I see it, you really need to aggregate, which is usually done using control points) In a table that looks like this:

  JAN FEB MAR APR MAY JUN JUL AGU SEP A 10 20 B 10 20 - - - - - - 30 C 10 - - - - 20 30 - 40

Don't come across something similar before, but suppose it's a common problem of any ideas?

+5
source share
2 answers

If you are using SQL Server 2005 (or higher), here is the code:

 DECLARE @cols VARCHAR(1000) DECLARE @sqlquery VARCHAR(2000) SELECT @cols = STUFF(( SELECT distinct ',' + QuoteName([Month]) FROM YourTable FOR XML PATH('') ), 1, 1, '') SET @sqlquery = 'SELECT * FROM (SELECT Person, Month, Paid FROM YourTable ) base PIVOT (Sum(Paid) FOR [Person] IN (' + @cols + ')) AS finalpivot' EXECUTE ( @sqlquery )

This will work no matter how much status you have. It dynamically collects the request using PIVOT . The only way to make PIVOT with dynamic columns is to collect the query dynamically, which can be done in SQL Server.

Other examples:

+3
source

I am not sure why you need a dynamic number of columns, since there are always 12 months of the year. Also your monthly names seem a bit inconsistent in length.

Example result set:

 SELECT * FROM (SELECT 'A' [PERSON],'JAN' [MONTH],'10' [PAID] UNION SELECT 'A','FEB',20 UNION SELECT 'B','JAN',10 UNION SELECT 'B','FEB',20 UNION SELECT 'B','SEP',30 UNION SELECT 'C','JAN',10 UNION SELECT 'C','JUNE',20 UNION SELECT 'C','JULY',30 UNION SELECT 'C','SEP',40) AS A PIVOT (SUM([PAID]) FOR [MONTH] IN ([JAN],[FEB],[MARCH],[APRIL],[MAY],[JUNE],[JULY],[AUG],[SEP],[OCT],[NOV],[DEC])) p

Against your table, this would be:

 SELECT [PERSON],[MONTH],[PAID] FROM [YOURTABLE] PIVOT (SUM([PAID]) FOR [MONTH] IN ([JAN],[FEB],[MARCH],[APRIL],[MAY],[JUNE],[JULY],[AUG],[SEP],[OCT],[NOV],[DEC])) p

If you add the year column, it will look like this:

 SELECT * FROM (SELECT 'A' [PERSON],'JAN' [MONTH],'10' [PAID], 2011 [YEAR] UNION SELECT 'A','FEB',20, 2011 UNION SELECT 'B','JAN',10, 2011 UNION SELECT 'A','FEB',20, 2010 UNION SELECT 'B','JAN',10, 2010 UNION SELECT 'B','FEB',20,2011 UNION SELECT 'B','SEP',30,2011 UNION SELECT 'C','JAN',10,2011 UNION SELECT 'C','JUNE',20,2011 UNION SELECT 'C','JULY',30,2011 UNION SELECT 'C','SEP',40,2011) AS A PIVOT (SUM([PAID]) FOR [MONTH] IN ([JAN],[FEB],[MARCH],[APRIL],[MAY],[JUNE],[JULY],[AUG],[SEP],[OCT],[NOV],[DEC])) p
+2
source

Source: https://habr.com/ru/post/895806/

More articles:


All Articles