How to have multiple columns on Pivot tables?

Question

I have a table in my sql server called, VendorItemPricing.

select * from VendorItemPricing  where ItemID = 122

And this query will give me the below results.

ItemID    VendorName    VendorPrice    UpdatedDate                ObsoleteItem
122       HP            215.13         2015-05-15 11:55:49.983    0
122       IBM           264.41         2015-05-15 11:56:04.990    0
122       Microsoft     257.65         2015-05-15 11:56:23.963    0

I want to have HP, Apple, ... on the columns insted of rows, So I used Pivot tables

Select ItemID,
[HP] As HPUpdatedDate,
[Apple] As AppleUpdatedDate,
[Microsoft] As MicrosoftUpdatedDate,
[IBM] As IBMUpdatedDate from (
select Itemid,Vendorname,UpdatedDate from VendorItemPricing where ItemID = 122)A
PIVOT(
MAX(UpdatedDate) FOR Vendorname IN ([HP],[Apple],Microsoft,IBM)
)P

Above query results like:

ItemID  HPUpdatedDate               AppleUpdatedDate    MicrosoftUpdatedDate        IBMUpdatedDate
122     2015-05-15 11:55:49.983     NULL                2015-05-15 11:56:23.963     2015-05-15 11:56:04.990

and

Select ItemID,
[HP] As HPPrice ,
[Apple] As ApplePrice,
[Microsoft] As MicrosoftPrice,
[IBM] As IBMPrice from (
select Itemid,Vendorname,VendorPrice from VendorItemPricing where ItemID = 122)A
PIVOT(
MAX(VendorPrice) FOR Vendorname IN ([HP],[Apple],Microsoft,IBM)
)P

Above query results like:

ItemID  HPPrice     ApplePrice      MicrosoftPrice  IBMPrice
122     215.13      NULL            257.65          264.41

and

Select ItemID,
[HP] As HPObsoleteItem,
[Apple] As AppleObsoleteItem,
[Microsoft] As MicrosoftObsoleteItem,
[IBM] As IBMObsoleteItem  from (
select Itemid,Vendorname,CAST(ObsoleteItem AS TINYINT) AS INTColumn from VendorItemPricing where ItemID = 122)A
PIVOT(
MAX(INTColumn) FOR Vendorname IN ([HP],[Apple],Microsoft,IBM)
)P

Above query results like:

ItemID      HPObsoleteItem      AppleObsoleteItem       MicrosoftObsoleteItem   IBMObsoleteItem
122         0                   NULL                    0                       0

Is it possible to have results like below? Sorry for the lengthy question. Any help will be much appreciated.

ItemID  HPUpdatedDate               AppleUpdatedDate    MicrosoftUpdatedDate        IBMUpdatedDate              HPPrice     ApplePrice  MicrosoftPrice  IBMPrice        HPObsoleteItem      AppleObsoleteItem   MicrosoftObsoleteItem   IBMObsoleteItem
122     2015-05-15 11:55:49.983     NULL                2015-05-15 11:56:23.963     2015-05-15 11:56:04.990     215.13      NULL        257.65          264.41          0                   NULL                0                       0

possible duplicate of [SQL Server Pivot Table with multiple column aggregates](http://stackoverflow.com/questions/14694691/sql-server-pivot-table-with-multiple-column-aggregates) — Tab Alleman, May 18 '15 at 17:45

score 0 · Accepted Answer · answered May 18 '15 at 19:36

You could use either a Subquery or CTE and then join all three queries based on ItemID to get all the neccessary fields:

;WITH q1
AS (
    SELECT ItemID
        ,[HP] AS HPUpdatedDate
        ,[Apple] AS AppleUpdatedDate
        ,[Microsoft] AS MicrosoftUpdatedDate
        ,[IBM] AS IBMUpdatedDate
    FROM (
        SELECT Itemid
            ,Vendorname
            ,UpdatedDate
        FROM VendorItemPricing
        WHERE ItemID = 122
        ) A
    PIVOT(MAX(UpdatedDate) FOR Vendorname IN (
                [HP]
                ,[Apple]
                ,Microsoft
                ,IBM
                )) P
    )
    ,q2
AS (
    SELECT ItemID
        ,[HP] AS HPPrice
        ,[Apple] AS ApplePrice
        ,[Microsoft] AS MicrosoftPrice
        ,[IBM] AS IBMPrice
    FROM (
        SELECT Itemid
            ,Vendorname
            ,VendorPrice
        FROM VendorItemPricing
        WHERE ItemID = 122
        ) A
    PIVOT(MAX(VendorPrice) FOR Vendorname IN (
                [HP]
                ,[Apple]
                ,Microsoft
                ,IBM
                )) P
    )
    ,q3
AS (
    SELECT ItemID
        ,[HP] AS HPObsoleteItem
        ,[Apple] AS AppleObsoleteItem
        ,[Microsoft] AS MicrosoftObsoleteItem
        ,[IBM] AS IBMObsoleteItem
    FROM (
        SELECT Itemid
            ,Vendorname
            ,CAST(ObsoleteItem AS TINYINT) AS INTColumn
        FROM VendorItemPricing
        WHERE ItemID = 122
        ) A
    PIVOT(MAX(INTColumn) FOR Vendorname IN (
                [HP]
                ,[Apple]
                ,Microsoft
                ,IBM
                )) P
    )
SELECT q1.ItemID
    ,q1.HPUpdatedDate
    ,q1.AppleUpdatedDate
    ,q1.MicrosoftUpdatedDate
    ,q1.IBMUpdatedDate
    ,q2.HPPrice
    ,q2.ApplePrice
    ,q2.MicrosoftPrice
    ,q2.IBMPrice
    ,q3.HPObsoleteItem
    ,q3.AppleObsoleteItem
    ,q3.MicrosoftObsoleteItem
    ,q3.IBMObsoleteItem
FROM q1
JOIN q2 ON q1.ItemID = q2.ItemID
JOIN q3 ON q1.ItemID = q3.ItemID

How to have multiple columns on Pivot tables?

1 Answers1