sql server – Get all details by date without aggregate functions with dynamic pivot

I have the following table and I’m trying to put the (Fecha de pago) rows as a column, and I found similar questions but they are using aggregate functions like:

Dynamic pivot: sum of sales per month

Dynamic dates Pivot

SQL Server dynamic PIVOT query?

Passing column names dynamically to UNPIVOT //This is similar but in reverse

Fracción Importación    Valor Comercial Importación Fecha de pago   Fraccion    Valor Comercial
85045099                4185787.29                  2015            85045099    307032.000000
84819099                419688356.99                2015            84819099    398684.000000
84212199                11248672.39                 2015            84212199    24028.000000
83082001                149868.94                   2015            83082001    146.000000
73202001                30344893.96                 2016            73202001    425610.000000
48081001                8201767.45                  2016            48081001    29.000000
85364103                100958.22                   2017            85364103    341099.000000
84831003                6971766.61                  2017            84831003    3074.000000

and I’m trying to get this goal

Fracción Importación    Valor Comercial Importación 2015        Valor Comercial 2016        Valor Comercial     2017        Valor Comer
85045099                4185787.29                  85045099    307032.000000   73202001     425610.000000      85364103    341099.000000   
84819099                419688356.99                84819099    398684.000000   48081001     29.000000          84831003    3074.000000
84212199                11248672.39                 84212199    24028.000000    null        null                null        null
83082001                149868.94                   83082001    146.000000      null        null                null        null

Following the path:

  1. I created a table where I store the data that I want to pivot
  2. I stored the data with select * into #table from QUERYTHATIUSETOGETINFO
  3. I create the pivot

This is what pivot looks like obviously I don’t want the max value but the pivot just let me run it like this

declare @cols as nvarchar(max), @query as nvarchar(max)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME((Fecha de pago)) 
                from #reportTable
        FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

set @query = 'select * from #reportTable 
 pivot
 (
    max((Fraccion))
    for (Fecha de pago) in ('+ @cols +')
 ) u
 '
exec sp_executesql @query;

And I get this result but it shows with the max value

If I use unpivot I get the following error:

Msg 207, Level 16, State 1, Line 114
Invalid column name '2015'.
Msg 207, Level 16, State 1, Line 114
Invalid column name '2016'.
Msg 207, Level 16, State 1, Line 114
Invalid column name '2017'.
.
.
.
The column name "Fraccion" specified in the UNPIVOT operator conflicts with the existing column name in the UNPIVOT argument.

So, after all the context, Is there a way to do this with pivot or unpivot?? or there’s another stuff to implement it?

sql server – Help with PIVOT query

I have a table with below structure :

CREATE TABLE (dbo).(AUDIT_SCHEMA_VERSION)(
    (SCHEMA_VER_MAJOR) (int) NOT NULL,
    (SCHEMA_VER_MINOR) (int) NOT NULL,
    (SCHEMA_VER_SUB) (int) NOT NULL,
    (SCHEMA_VER_DATE) (datetime) NOT NULL,
    (SCHEMA_VER_REMARK) (varchar)(250) NULL
);

some sample data (seems problem with sqlfiddle.. so putting some sample data):

INSERT INTO (AUDIT_SCHEMA_VERSION)((SCHEMA_VER_MAJOR),(SCHEMA_VER_MINOR),(SCHEMA_VER_SUB),(SCHEMA_VER_DATE),(SCHEMA_VER_REMARK))
VALUES(1,6,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO (AUDIT_SCHEMA_VERSION)((SCHEMA_VER_MAJOR),(SCHEMA_VER_MINOR),(SCHEMA_VER_SUB),(SCHEMA_VER_DATE),(SCHEMA_VER_REMARK))
VALUES(1,6,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO (AUDIT_SCHEMA_VERSION)((SCHEMA_VER_MAJOR),(SCHEMA_VER_MINOR),(SCHEMA_VER_SUB),(SCHEMA_VER_DATE),(SCHEMA_VER_REMARK))
VALUES(1,7,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO (AUDIT_SCHEMA_VERSION)((SCHEMA_VER_MAJOR),(SCHEMA_VER_MINOR),(SCHEMA_VER_SUB),(SCHEMA_VER_DATE),(SCHEMA_VER_REMARK))
VALUES(1,10,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO (AUDIT_SCHEMA_VERSION)((SCHEMA_VER_MAJOR),(SCHEMA_VER_MINOR),(SCHEMA_VER_SUB),(SCHEMA_VER_DATE),(SCHEMA_VER_REMARK))
VALUES(1,12,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO (AUDIT_SCHEMA_VERSION)((SCHEMA_VER_MAJOR),(SCHEMA_VER_MINOR),(SCHEMA_VER_SUB),(SCHEMA_VER_DATE),(SCHEMA_VER_REMARK))
VALUES(1,12,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO (AUDIT_SCHEMA_VERSION)((SCHEMA_VER_MAJOR),(SCHEMA_VER_MINOR),(SCHEMA_VER_SUB),(SCHEMA_VER_DATE),(SCHEMA_VER_REMARK))
VALUES(1,16,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO (AUDIT_SCHEMA_VERSION)((SCHEMA_VER_MAJOR),(SCHEMA_VER_MINOR),(SCHEMA_VER_SUB),(SCHEMA_VER_DATE),(SCHEMA_VER_REMARK))
VALUES(1,16,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO (AUDIT_SCHEMA_VERSION)((SCHEMA_VER_MAJOR),(SCHEMA_VER_MINOR),(SCHEMA_VER_SUB),(SCHEMA_VER_DATE),(SCHEMA_VER_REMARK))
VALUES(1,16,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO (AUDIT_SCHEMA_VERSION)((SCHEMA_VER_MAJOR),(SCHEMA_VER_MINOR),(SCHEMA_VER_SUB),(SCHEMA_VER_DATE),(SCHEMA_VER_REMARK))
VALUES(1,16,13,CAST('20140417 18:10:44.100' as DATETIME),'Stored procedure build')
INSERT INTO (AUDIT_SCHEMA_VERSION)((SCHEMA_VER_MAJOR),(SCHEMA_VER_MINOR),(SCHEMA_VER_SUB),(SCHEMA_VER_DATE),(SCHEMA_VER_REMARK))
VALUES(2,5,0,CAST('20140417 18:14:14.157' as DATETIME),'Stored procedure build')
INSERT INTO (AUDIT_SCHEMA_VERSION)((SCHEMA_VER_MAJOR),(SCHEMA_VER_MINOR),(SCHEMA_VER_SUB),(SCHEMA_VER_DATE),(SCHEMA_VER_REMARK))
VALUES(2,6,0,CAST('20140417 18:14:23.327' as DATETIME),'Stored procedure build')
INSERT INTO (AUDIT_SCHEMA_VERSION)((SCHEMA_VER_MAJOR),(SCHEMA_VER_MINOR),(SCHEMA_VER_SUB),(SCHEMA_VER_DATE),(SCHEMA_VER_REMARK))
VALUES(2,7,0,CAST('20140417 18:14:32.270' as DATETIME),'Stored procedure build')
INSERT INTO (AUDIT_SCHEMA_VERSION)((SCHEMA_VER_MAJOR),(SCHEMA_VER_MINOR),(SCHEMA_VER_SUB),(SCHEMA_VER_DATE),(SCHEMA_VER_REMARK))
VALUES(2,8,0,CAST('20141209 09:38:40.700' as DATETIME),'Stored procedure build')
INSERT INTO (AUDIT_SCHEMA_VERSION)((SCHEMA_VER_MAJOR),(SCHEMA_VER_MINOR),(SCHEMA_VER_SUB),(SCHEMA_VER_DATE),(SCHEMA_VER_REMARK))
VALUES(2,9,0,CAST('20141209 09:43:04.237' as DATETIME),'Stored procedure build')
INSERT INTO (AUDIT_SCHEMA_VERSION)((SCHEMA_VER_MAJOR),(SCHEMA_VER_MINOR),(SCHEMA_VER_SUB),(SCHEMA_VER_DATE),(SCHEMA_VER_REMARK))
VALUES(2,10,0,CAST('20141209 09:45:19.893' as DATETIME),'Stored procedure build')
INSERT INTO (AUDIT_SCHEMA_VERSION)((SCHEMA_VER_MAJOR),(SCHEMA_VER_MINOR),(SCHEMA_VER_SUB),(SCHEMA_VER_DATE),(SCHEMA_VER_REMARK))
VALUES(2,13,0,CAST('20150323 14:54:30.847' as DATETIME),'Stored procedure build')
INSERT INTO (AUDIT_SCHEMA_VERSION)((SCHEMA_VER_MAJOR),(SCHEMA_VER_MINOR),(SCHEMA_VER_SUB),(SCHEMA_VER_DATE),(SCHEMA_VER_REMARK))
VALUES(1,10,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO (AUDIT_SCHEMA_VERSION)((SCHEMA_VER_MAJOR),(SCHEMA_VER_MINOR),(SCHEMA_VER_SUB),(SCHEMA_VER_DATE),(SCHEMA_VER_REMARK))
VALUES(1,16,14,CAST('20140417 18:11:07.977' as DATETIME),'Stored procedure build')
INSERT INTO (AUDIT_SCHEMA_VERSION)((SCHEMA_VER_MAJOR),(SCHEMA_VER_MINOR),(SCHEMA_VER_SUB),(SCHEMA_VER_DATE),(SCHEMA_VER_REMARK))
VALUES(1,16,15,CAST('20140417 18:11:13.130' as DATETIME),'Stored procedure build')
INSERT INTO (AUDIT_SCHEMA_VERSION)((SCHEMA_VER_MAJOR),(SCHEMA_VER_MINOR),(SCHEMA_VER_SUB),(SCHEMA_VER_DATE),(SCHEMA_VER_REMARK))
VALUES(2,2,0,CAST('20140417 18:12:11.200' as DATETIME),'Stored procedure build')
INSERT INTO (AUDIT_SCHEMA_VERSION)((SCHEMA_VER_MAJOR),(SCHEMA_VER_MINOR),(SCHEMA_VER_SUB),(SCHEMA_VER_DATE),(SCHEMA_VER_REMARK))
VALUES(2,3,0,CAST('20140417 18:12:33.330' as DATETIME),'Stored procedure build')
INSERT INTO (AUDIT_SCHEMA_VERSION)((SCHEMA_VER_MAJOR),(SCHEMA_VER_MINOR),(SCHEMA_VER_SUB),(SCHEMA_VER_DATE),(SCHEMA_VER_REMARK))
VALUES(2,4,0,CAST('20140417 18:12:48.803' as DATETIME),'Stored procedure build')
INSERT INTO (AUDIT_SCHEMA_VERSION)((SCHEMA_VER_MAJOR),(SCHEMA_VER_MINOR),(SCHEMA_VER_SUB),(SCHEMA_VER_DATE),(SCHEMA_VER_REMARK))
VALUES(1,13,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO (AUDIT_SCHEMA_VERSION)((SCHEMA_VER_MAJOR),(SCHEMA_VER_MINOR),(SCHEMA_VER_SUB),(SCHEMA_VER_DATE),(SCHEMA_VER_REMARK))
VALUES(1,16,13,CAST('20130405 04:41:25.000' as DATETIME),'Stored procedure build')
INSERT INTO (AUDIT_SCHEMA_VERSION)((SCHEMA_VER_MAJOR),(SCHEMA_VER_MINOR),(SCHEMA_VER_SUB),(SCHEMA_VER_DATE),(SCHEMA_VER_REMARK))
VALUES(2,11,0,CAST('20141209 09:45:58.993' as DATETIME),'Stored procedure build')
INSERT INTO (AUDIT_SCHEMA_VERSION)((SCHEMA_VER_MAJOR),(SCHEMA_VER_MINOR),(SCHEMA_VER_SUB),(SCHEMA_VER_DATE),(SCHEMA_VER_REMARK))
VALUES(2,12,0,CAST('20141209 09:46:50.070' as DATETIME),'Stored procedure build');

Here is the SQLFiddle with some sample data.

Can someone with a T-sql expertise guide me on how to achieve the final result ? I know that PIVOT (with dynamic columns) will be the right approach, but cant figure it out.

Expected results :

enter image description here

So far, I have below :

select row_number() over (
        partition by CONVERT(varchar(10), SCHEMA_VER_DATE, 110) order by SCHEMA_VER_DATE 
        ) as rownum
    ,CONVERT(varchar(10), SCHEMA_VER_DATE, 110) as UPG_DATE
    ,CONVERT(varchar(1), SCHEMA_VER_MAJOR) + '.' + CONVERT(varchar(2), SCHEMA_VER_MINOR) + '.' + CONVERT(varchar(2), SCHEMA_VER_SUB) as SCHEMA_VER
from audit_schema_version
where SCHEMA_VER_REMARK like 'Stored procedure build'
order by UPGRADE_DATE 

enter image description here

SQL server IN Clause usage with Pivot table

I have a problem with pivoting a table on MSSQL.
The main problem is, when I want to fetch row values in an inner query (with IN clause) I get syntax errors but when I type the values statically, it works.

So the question is, how can I use IN clause inside PIVOT clause?

CREATE OR ALTER FUNCTION (SCHEMA).(get_timeseries_by_campaign_id_and_period) (
    @campaign_id INT,
    @start_date DATE NULL,
    @end_date DATE NULL)
RETURNS TABLE AS RETURN 
WITH timeseries_data AS
  (SELECT CASE
              WHEN t.data_type = 'int' THEN ti.datetime
              WHEN t.data_type = 'real' THEN tr.datetime
              WHEN t.data_type = 'text' THEN tt.datetime
              ELSE 'No Data'
          END AS timeseries_date,
          c.campaign_id,
          t.timeseries_id,
          t.data_type,
          CASE
              WHEN t.data_type = 'int' THEN ti.value
              WHEN t.data_type = 'real' THEN tr.value
              WHEN t.data_type = 'text' THEN tt.value
              ELSE 'No Data'
          END AS timeseries_value
   FROM (DATABASE).(SCHEMA).(hub_campaign) c
   INNER JOIN (DATABASE).(SCHEMA).(measurement) m ON c.campaign_id = @campaign_id AND m.campaign_id = c.campaign_id
   INNER JOIN (DATABASE).(SCHEMA).(timeseries) t ON t.measurement_id = m.measurement_id
   INNER JOIN (SCHEMA).(timeseries_view) tv ON tv.campaign_bk = c.campaign_bk
   LEFT OUTER JOIN (DATABASE).(SCHEMA).(timeseries_data_int) AS ti ON t.timeseries_id = ti.timeseries_id 
        AND COALESCE(@start_date, GETDATE() - 1) <= ti.datetime AND ti.datetime < COALESCE(@end_date, GETDATE())
   LEFT OUTER JOIN (DATABASE).(SCHEMA).(timeseries_data_real) AS tr ON t.timeseries_id = tr.timeseries_id
        AND COALESCE(@start_date, GETDATE() - 1) <= tr.datetime AND tr.datetime < COALESCE(@end_date, GETDATE())
   LEFT OUTER JOIN (DATABASE).(SCHEMA).(timeseries_data_text) AS tt ON t.timeseries_id = tt.timeseries_id
        AND COALESCE(@start_date, GETDATE() - 1) <= tt.datetime AND tt.datetime < COALESCE(@end_date, GETDATE())
   ),
timeseries_ids AS (
    SELECT CONCAT('(', timeseries_id, ')') as timeseries_id FROM timeseries_data
)
SELECT * FROM (
    SELECT timeseries_date, timeseries_id,  timeseries_value, data_type
    FROM timeseries_data
) tmp PIVOT (
    -- AVG(timeseries_value) FOR tmp.timeseries_id IN (SELECT timeseries_id FROM timeseries_ids)
    AVG(timeseries_value) FOR tmp.timeseries_id IN ((1), (7), (8), (9), (10), (11), (12), (13), (16), (14), (15), (17), (22), (26), (23), (25), (27))
)as pvt;


SELECT * FROM (SCHEMA).(get_timeseries_by_campaign_id_and_period)(1, '2021-05-29 18:00:00.00', '2021-05-30 08:00:00.00');

3d – Rotating around pivot so target is 25% away from top of screen

I have a camera which rotates around a pivot (green dot) that is always a constant X units away from that pivot. I want to find the pitch to rotate around that green dot so that the target (red square) is always 25% away from the top of the screen.

Example image:
Green dot is centered in the screen and red dot is 25% away from the top of the screen

This is used for a lock on camera. I’ve solved for yaw and everything but I don’t really know what math would be used to achieve this. Is it based on field of view? Does perspective have something to do with this? Any help would be appreciated.

edit: Was asked to provide the code so here it is:

local DISTANCE = 8

local function angleBetweenPointsXZ(a, b)
    local deltaZ = b.X - a.X
    local deltaX = b.Z - a.Z

    return math.atan2(deltaZ, deltaX)
end

local function onUpdate()
    local yaw = angleBetweenPointsXZ(pivot.Position, target.Position) + math.pi
    local pitch = 0

    camera.CFrame = CFrame.new(pivot.Position)
        * CFrame.Angles(0, yaw, 0)
        * CFrame.Angles(pitch, 0, 0)
        * CFrame.new(0, 0, DISTANCE)
end

I was hesitant to include code because this is being done in Roblox. If you aren’t familiar, a CFrame is just a vector for position and a rotation matrix. So that last piece where we set the camera’s CFrame, it starts at the pivot’s position with no rotation, applies a rotation around global Y for yaw, applies a rotation around resulting local X for pitch and than moves a DISTANCE away from the pivot in the negative lookVector direction. In this code I am trying to solve for the pitch variable.

postgresql – Postgres pivot email rows into multiple columns per id

I have a table “emails”:

id |  email
-----------
1  | a@a.a
2  | g@g.g
2  | d@d.d
4  | c@c.c
2  | k@k.k
4  | o@o.o

and i need this result:

id |  email | email  |  email
------------------------------
1  | a@a.a  |       |
2  | g@g.g  | d@d.d | k@k.k
4  | c@c.c  | o@o.o |

the number of columns could expand furture, in fact in the real database i have ids with 10 e-mails already.
Every id, email pair is unique in the table.

i will need to join the resulted table with another table “users” on id

im using the latest version of prostgres 13.3

Google Sheet suddenly “Unable to load file” if I expand my pivot table on desktop

Possibly the strangest and most confounding issue I’ve ever run into with Google sheets, has just begun today.

I have a spreadsheet I’ve been building for a couple of months now. I work in it basically every weekday. Last time was Friday. I went into it today on my Windows machine using the latest Google Chrome, as I normally do, and added some more data to it. Then I went to my summary pivot table and tried to expand the new row (for this week) that had been generated by the new data I had added to the source sheet.

Suddenly I was greeted with a popup saying “Unable to load file. Try to load it again or send an error report.”. From that point on the sheet was unloadable, completely. As if had just spontaneously self-destructed. I tried every troubleshooting step I could think of or find on the web including:

  • Incognito Mode
  • Clearing Cache
  • Force Refresh
  • Make a copy of the document
  • Share the document to another account and open it there
  • Try from another browser (Firefox)
  • Toggle online/offline mode
  • Try from a different computer (Mac using Chrome)

I got absolutely nowhere, same issue every time. I couldn’t even get to the version history list because the error would come up before I could get to the menu. But once (and only once) I managed to interrupt the loading at the exact right moment, and was able to load an older version of the file (From Thursday) instead. It loaded perfectly fine! All seemed good. Until again… I added some more data and tried to open the new pivot table row again. BOOM right back to the same place.

But here’s where things take a turn for the weird. I thought that I just might get lucky and be able to access and save the sheet from my iPhone which has Google Drive on it. Sure enough I could open the document, and it gave me no grief at all! Can see everything, open & close the various pivot rows, etc. All seems fine. Then I went back to my desktop, reloaded, and voila! The sheet suddenly loaded fine. Again, until I tried to open a pivot table row… at which point, same error all over again.

However, I noticed that on my phone, I could now see the expanded row just fine, so I tried collapsing it on the phone, and reloading on Desktop. Once again, the sheet loaded just fine.

So whatever is going on, appears to be connected to the pivot table, specifically expanding ANY row. If a row is expanded, the sheet will never load on a desktop computer. But it will work just fine on iOS.

I’m mystified as to what could be going on. This sheet has never given me problems, until now. It’s a critical sheet to my work and I’ve put a good chunk of effort into the pivot tables and formatting. I would be most upset if I were to lose it. At the moment it seems the sheet is in fact intact and fine, since the phone can access it. But…

How do I fix this so my sheet works normally again on a Desktop browser?

google sheets – Keeping manually entered data side-by-side with pivot table data — row misalignment issue

I have a Pivot Table (columns 1 and 2), and I also have manually inserted data (columns 3 and 4):

Table1

But when more data gets added to the pivot table, the manually entered columns won’t follow “their” row, so the rows become misaligned like this:

Table2

I would need the table to behave so that when new data gets added to the pivot table, the manually inserted rows keep up with “their” row, like this:

Table3

I’ve looked around, tried to make it work, but couldn’t find anything. Any help?

2D – How to make a gun muzzle aim at the mouse when the rotation pivot isn’t on the muzzle?

I’m making a simple 2D shooter game in pygame and I’ve come across an issue when making the character aim its gun at the mouse.

To make the gun aim up and down, I’ve set it so that the gun uses the angle between the x axis and the mouse to rotate itself on its handle. It works just fine but the issue is that since the muzzle of the gun is above the pivot, the muzzle never aims directly at the mouse but slightly higher.

Drawing of the situation

My question is, is there a way to compensate for the offset of the muzzle while keeping the rotation pivot on the handle of the gun?

algorithms – Quicksort: Probability of an element being compared to fewer than $k$ pivot elements

Assume we want to use quicksort on some array $s$ with length $n$ consisting of only $n$ distinct elements.
Let $S_{(1)},S_{(2)},dots,S_{(n)}$ be the sorted order of the elements in $S$.
Furthermore, let $d(i)$ be the number of elements that $S_{(i)}$ is compared to.
Finally, the pivot element is chosen uniformly at random.

How do I then compute the probability that $d(i) < k$ for some $k in mathbb{N}$?

google sheets – Help Please? Pivot Table doesn’t seem to be correct way to fix? Add rows based on data type and return value?

I’ve added a new sheet (“Erik Help”) with two options for you. However, here I will only post the option that matches your exact request. In B1:

=ArrayFormula({"ID","Type","Value";QUERY(SPLIT(FLATTEN(FILTER(Sheet1!A2:A,Sheet1!A2:A<>"")&"|"&Sheet1!B1:E1&"|"&FILTER(Sheet1!B2:E,Sheet1!A2:A<>"")),"|"),"Select * Where Col3 Is Not Null")})

This formula creates the three headers (which you can change as you like), and then does the following:

1.) concatenate all non-blank entries in A2:A, a pipe symbol, each header in B1:E1, another pipe symbol and each element of the B2:E grid where A2:A is not blank. This will form a grid of results the same size as B2:E where A2:A is not blank, each in the format X|Y|Z.

2.) this grid of results is FLATTENed into one column.

3.) that column of results is split into three columns at the pipe symbols.

4.) QUERY weeds out any entries where the value is null.