1

I am trying to get two values from rows in a table into a single row in a pivot table.

myTable has the following rows

ID1 ID2 Item Value SP
11 22 Item 1 10 10
11 22 Item 2 100 100
11 22 Item 3 49 55
12 22 Item 1 16 15
12 22 Item 2 99 100
12 22 Item 3 15 15

I have tried using this

SELECT *
FROM
(
    SELECT [ID1],
           [ID2],
           [Item],     
           [Value],
           [SP]
  
    FROM [dbo].[tblDetails]
) AS SourceTable PIVOT(MAX([Value]) FOR [Item] IN([Item1],
                                                  [Item2],
                                                  [Item3]])                                     
                                                   ) AS PivotTable

Without the SP it gives me the rows as expected.

ID1 ID2 Item 1 Item 2 Item 3
11 22 10 100 49
12 22 16 99 15

What I would like is:

ID1 ID2 Item 1 Item 1 SP Item 2 Item 2 SP Item 3 Item 3 SP
11 22 10 10 100 100 49 55
12 22 16 15 99 100 15 15

Is this possible?

2 Answers 2

4

Unfortunately PIVOT doesn't allow rotating multiple columns like you want, so you have a few options to get this done. The easiest being using an aggregate function along with a CASE expression.

if object_id('tblDetails') is not null
    drop table tblDetails

CREATE TABLE tblDetails
    ([ID1] int, [ID2] int, [Item] varchar(6), [Value] int, [SP] int)
;
    
INSERT INTO tblDetails
    ([ID1], [ID2], [Item], [Value], [SP])
VALUES
    (11, 22, 'Item 1', 10, 10),
    (11, 22, 'Item 2', 100, 100),
    (11, 22, 'Item 3', 49, 55),
    (12, 22, 'Item 1', 16, 15),
    (12, 22, 'Item 2', 99, 100),
    (12, 22, 'Item 3', 15, 15)
;


select 
    ID1,
    ID2,
    Item1 = max(case when Item = 'Item 1' then Value end),
    Item1SP = max(case when Item = 'Item 1' then SP end),
    Item2 = max(case when Item = 'Item 2' then Value end),
    Item2SP = max(case when Item = 'Item 2' then SP end),
    Item3 = max(case when Item = 'Item 3' then Value end),
    Item3SP = max(case when Item = 'Item 3' then SP end)
from tblDetails
group by ID1, ID2

This gives the result:

ID1 ID2 Item1 Item1SP Item2 Item2SP Item3 Item3SP
11 22 10 10 100 100 49 55
12 22 16 15 99 100 15 15

By using the CASE expression along with the aggregate you can convert multiple row values into columns.

If you want to use PIVOT to accomplish this, then you'd need to first UNPIVOT the two columns Value and SP, then apply the PIVOT function to that result. The query would look a little like this:

select 
    ID1, 
    ID2,
    Item1_value, Item1_SP, 
    Item2_value, Item2_SP, 
    Item3_value, Item3_SP
from 
(
    select ID1, 
        ID2, 
        new_col = concat(replace(Item, ' ', ''), '_', d.col), 
        d.val
    from tblDetails t
    cross apply 
    (
        select 'value', value union all
        select 'SP', SP
    ) d (col, val)
) un
pivot
(
    max(val)
    for new_col in (Item1_value, Item1_SP, Item2_value, Item2_SP, Item3_value, Item3_SP)
) piv;

Which gives the exact same result:

ID1 ID2 Item1_value Item1_SP Item2_value Item2_SP Item3_value Item3_SP
11 22 10 10 100 100 49 55
12 22 16 15 99 100 15 15
0

You can achieve the expected result by using two CTEs to PIVOT the source table on each column then joining the two CTEs to create a single result set.

NOTE: This only works when you have one row per ID1 - ID2 - Item *N* group, for example, if you have rows like below:

ID1 ID2 Item Value SP
11 22 Item 1 100 25
11 22 Item 1 90 50

These will return 100 for the Item 1 column and 50 for the Item 1 SP column when the CTEs are joined because the PIVOT is using MAX for both columns. If this scenario is not going to occur, or if this behaviour is what you're after then this solution will work.

See this fiddle for a working example of the code below.

CREATE TABLE DataTbl
(
    ID1 INT,
    ID2 INT,
    Item VARCHAR(25),
    VALUE INT,
    SP INT
)

INSERT INTO DataTbl (ID1, ID2, Item, Value, SP)
VALUES
(11, 22, 'Item 1', 10, 10),
(11, 22, 'Item 2', 100, 100),
(11, 22, 'Item 3', 49, 55),
(12, 22, 'Item 1', 16, 15),
(12, 22, 'Item 2', 99, 100),
(12, 22, 'Item 3', 15, 15)

;WITH CTE AS
(
SELECT ID1
  , ID2
  , p1.[Item 1]
  , p1.[Item 2]
  , p1.[Item 3]
FROM
(
    SELECT [ID1],
        [ID2],
        [Item],     
        [Value]
    FROM DataTbl
) source
PIVOT ( MAX([Value]) FOR [Item] IN ([Item 1], [Item 2], [Item 3]) ) p1
), CTE2 AS 
(
SELECT ID1
  , ID2
  , p2.[Item 1] AS [Item 1 SP]
  , p2.[Item 2] AS [Item 2 SP]
  , p2.[Item 3] AS [Item 3 SP]
FROM
(
    SELECT [ID1],
        [ID2],
        [Item],     
        [SP]
    FROM DataTbl
) source
PIVOT ( MAX([SP]) FOR [Item] IN ([Item 1], [Item 2], [Item 3]) ) p2
)

SELECT p1.ID1
  , p1.ID2
  , p1.[Item 1]
  , p2.[Item 1 SP]
  , p1.[Item 2]
  , p2.[Item 2 SP]
  , p1.[Item 3]
  , p2.[Item 3 SP]
FROM CTE p1
INNER JOIN CTE2 p2 ON p2.ID1 = p1.ID1 AND p2.ID2 = p1.ID2

Result:

ID1 ID2 Item 1 Item 1 SP Item 2 Item 2 SP Item 3 Item 3 SP
11 22 10 10 100 100 49 55
12 22 16 15 99 100 15 15
2
  • Thank you all for your answers. I tried a couple and they both did exactly what I wanted. Aug 24, 2021 at 16:52
  • Is there any advantage in performance of a particular solution? Aug 24, 2021 at 16:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.