CODEWITHSUNDEEP
pythonsql-serverexcelpython-3.xpivot-table
rickri
rickri

Reputation: 43

Pivot SQL table based on multiple column values

My output from SQL is as follows:

------------------------------------
| 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:

--------------------------------
| 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.

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'

Upvotes: 0

Views: 553

Answers (2)

Ramji
Ramji

Reputation: 385

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.

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

SQL :

I have written a dynamic sql by using pivot function

 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)

Upvotes: 1

SQL_M
SQL_M

Reputation: 2475

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

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

Upvotes: 0

Related Questions