基于多个列值的Pivot SQL表

My output from SQL is as follows:

我的SQL输出如下:

------------------------------------
| Name | Identifier | Date  | Value |
-------------------------------------
|  A   |    Bid     | XX/XX |   10  |
-------------------------------------
|  A   |    Ask     | XX/XX |   11  |
-------------------------------------
|  B   |    Bid     | YY/YY |   20  |
-------------------------------------
|  B   |    Ask     | YY/YY |   21  |
-------------------------------------

My desired output preferably directly from SQL or with the help of Python or Excel is as follows:

我希望的输出最好直接来自SQL或Python或Excel的帮助如下:

--------------------------------
| Name | Date  |  Bid  |  Ask  |
--------------------------------
|  A   | XX/XX |   10  |   11  |
--------------------------------
|  B   | YY/YY |   20  |   21  |
--------------------------------

What is the best way to accomplish this in either SQL, Python or Excel? My problem is that the next step in which I wish to use this data only handles inputs that are in the form of the "desired output" table.

在SQL,Python或Excel中实现此目的的最佳方法是什么?我的问题是,我希望使用此数据的下一步仅处理“所需输出”表格形式的输入。

EDIT: The original query is as follows:

编辑:原始查询如下:

SELECT * FROM table where Name (LIKE 'A' or LIKE 'B') and Date between 'AA/AA' and 'ZZ/ZZ'

2 个解决方案

#1

You can achieve the desired output using pivot. It is a functionality which can be found in all data analysis framework like excel, SQL etc.

您可以使用pivot实现所需的输出。它是一种功能,可以在所有数据分析框架中找到,如excel,SQL等。

For Excel, you can follow this link to acheive the desired result : http://www.excel-easy.com/data-analysis/pivot-tables.html

对于Excel,您可以点击此链接以获得所需的结果:http://www.excel-easy.com/data-analysis/pivot-tables.html

SQL :

I have written a dynamic sql by using pivot function

我使用pivot函数编写了一个动态sql

 create table tbl1 ( name varchar(100),Identifier  varchar(100), Date_val varchar(100), Value  int);

    INSERT INTO tbl1 values ('A','Bid','XX/XX',10),('A','Ask','XX/XX',11),('b','Bid','YY/YY',20),
    ('b','Ask','YY/YY',21)

    DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)


    select @cols = STUFF((SELECT ',' + QUOTENAME(Identifier) 
                        from tbl1
                        group by Identifier
                        order by Identifier
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')



    set @query = 'SELECT name,Date_val,' + @cols + ' from 
                 (
                    select  name,Identifier, Date_val, value
                    from tbl1
                ) x
                pivot 
                (
                    sum(value)
                    for Identifier in (' + @cols + ')
                ) p '

    execute (@query)

#2

In Python Pandas you could use PD.melt and specify the columns you want to keep the same. The others will get pivoted.

在Python Pandas中,您可以使用PD.melt并指定要保持相同的列。其他人将获得调整。

For more info: https://pandas.pydata.org/pandas-docs/stable/generated/pandas.melt.html

欲了解更多信息:https://pandas.pydata.org/pandas-docs/stable/generated/pandas.melt.html

#1