通过将行动态转换为列来获取给定天数的数据 [英] Get data for a given number of days by converting rows to column dynamically

问题描述

这是我之前的问题的后续操作：获取最近10个日期的记录

This is a follow-up to my previous question: Get records for last 10 dates

我必须通过传递storeId来生成商店所有书籍的报告以及最近 N 个日期的售出数量（如果有）。

I have to generate reports for all books of a store along with sold count (if any) for the last N dates, by passing storeId.

   BOOK               Book Sold                   Store
 ------------        --------------------        ---------------- 
 Id  Name  SID     Id Bid Count Date            SID  Name
  1   ABC   1      1   1   20  11/12/2015        1    MNA
  2   DEF   1      2   1   30  12/12/2015        2    KLK
  3   DF2   2      3   2   20  11/12/2015        3    KJH
  4   DF3   3      4   3   10  13/12/2015
  5   GHB   3      5   4    5  14/12/2015

该数字用户提供的 N 天。这是storeId -1,2&的最近4个日期的预期输出。 3。

The number of days N is supplied by the user. This is the expected output for the last 4 dates for storeId -1,2 & 3.

 BookName  11/12/2015 12/12/2015  13/12/2015  14/12/2015
  ABC         20        30         --             --    
  DEF         20        --         --             -- 
  DF2         --        --         10             -- 
  DF3         --        --         --              5
  GHB         --        --         --             --

如果用户通过的 5 大于数据最后5天应生成，起始日期为2015年12月14日。

If the user passes 5 than data for the last 5 days shall be generated, starting date as 14/12/2015.

我正在使用Postgres 9.3。

I am using Postgres 9.3.

推荐答案

不具有交叉表功能的交叉表：

Cross table without crosstab function:

SELECT
SUM(CASE book.Date ='11/11/2015' THEN book.Count ELSE 0 END) AS '11/11/2015',
SUM(CASE book.Date ='15/11/2015' THEN book.Count ELSE 0 END) AS '15/11/2015',
SUM(CASE book.Date ='17/11/2015' THEN book.Count ELSE 0 END) AS '17/11/2015'
FROM
store,
book
WHERE
store.Id = booksold.Bid
AND store.Id IN (1,2)
GROUP BY
book.Name
ORDER BY
book.id ASC;

这篇关于通过将行动态转换为列来获取给定天数的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！