CODEWITHSUNDEEP
sqlsql-servert-sql
Abhijit Das
Abhijit Das

Reputation: 39

How do I get actual result using PIVOT in SQL?

I am using a query and get the result. But I don't need this result, I need another result. I know the query is wrong for the result, which I wanted to show. I am confused to query how the result comes out and if there is any another way for this result.

declare @temp table(
  ProductId int,
  Caption nvarchar(max),
  Value nvarchar(max)
)

insert into @temp values (6830,'Stone Cut','Full Cut')
insert into @temp values (6830,'Stone Cut','Single Cut')
insert into @temp values (6830,'Gem Type','Diamond')
insert into @temp values (6830,'Gem Type','Diamond')
insert into @temp values (6830,'Total Diamond Weight','0.34')

insert into @temp values (6831,'Stone Cut','Full Cut')
insert into @temp values (6831,'Stone Cut','Single Cut')
insert into @temp values (6831,'Gem Type','Diamond')
insert into @temp values (6831,'Gem Type','Diamond')
insert into @temp values (6831,'Total Diamond Weight','0.35')

select ProductId
  , (case when [StoneCut] is null then '' else [StoneCut] end) as [StoneCut]
  , (case when [GemType] is null then '' else [GemType] end) as [GemType]
  , (case when [TotalDiamondWeight] is null then '' else [TotalDiamondWeight] end) as [TotalDiamondWeight]
from
(
  select ProductId, Caption, Value
  from @temp
) x
pivot
(
  max(Value)
  for Caption in([StoneCut], [GemType], [TotalDiamondWeight])
)p;

It shows one row like

---------------------------------------------------
ProductId | StoneCut | GemType | TotalDiamondWeight
---------------------------------------------------
 6830     |Single Cut| Diamond | 0.34

But I want this output : 

---------------------------------------------------
ProductId | StoneCut | GemType | TotalDiamondWeight
---------------------------------------------------
 6830     |Full Cut  | Diamond | 0.34
 6830     |Single Cut| Diamond |
 6831     |Full Cut  | Diamond | 0.34
 6831     |Single Cut| Diamond |

Upvotes: 3

Views: 97

Answers (4)

PSK
PSK

Reputation: 17943

Can you try this by adding a unique row to the set using Row_number().

declare @temp table(
  ProductId int,
  Caption nvarchar(max),
  Value nvarchar(max)
)

insert into @temp values (6830,'Stone Cut','Full Cut')
insert into @temp values (6830,'Stone Cut','Single Cut')
insert into @temp values (6830,'Gem Type','Diamond')
insert into @temp values (6830,'Gem Type','Diamond')
insert into @temp values (6830,'Total Diamond Weight','0.34')

insert into @temp values (6831,'Stone Cut','Full Cut')
insert into @temp values (6831,'Stone Cut','Single Cut')
insert into @temp values (6831,'Gem Type','Diamond')
insert into @temp values (6831,'Gem Type','Diamond')
insert into @temp values (6831,'Total Diamond Weight','0.35')

   SELECT productid, 
       [Stone Cut], 
      [Gem Type], 
      [Total Diamond Weight]
    FROM (SELECT * 
        FROM   (SELECT t.*, 
                       Row_number() 
                         OVER ( 
                           partition BY productid,T.caption 
                           ORDER BY (SELECT 1)) RN 
               FROM   @temp t)x 
               PIVOT (Max(value) 
                     FOR caption IN([Stone Cut], 
                                    [Gem Type], 
                                    [Total Diamond Weight]))p
             ) t1 
    ORDER BY productid

You will get the desired output.

Output:

+-----------+------------+----------+----------------------+
| productid | Stone Cut  | Gem Type | Total Diamond Weight |
+-----------+------------+----------+----------------------+
| 6830      | Full Cut   | Diamond  | 0.34                 |
+-----------+------------+----------+----------------------+
| 6830      | Single Cut | Diamond  | NULL                 |
+-----------+------------+----------+----------------------+
| 6831      | Full Cut   | Diamond  | 0.35                 |
+-----------+------------+----------+----------------------+
| 6831      | Single Cut | Diamond  | NULL                 |
+-----------+------------+----------+----------------------+

Upvotes: 1

StepUp
StepUp

Reputation: 38154

You've got one row because ProductId is always the same for each row. As you use operator MAX(), then you've got a row which has MAX() of VALUE. However, if you want to have other rows, then just differentiate these rows:

SELECT 
  p.ProductId
, p.TotalDiamondWeight
, p.GemType
, p.StoneCut
FROM
(
    SELECT ProductId,
           Caption,
           Value,
           ROW_NUMBER() OVER (PARTITION BY Caption ORDER BY Value) RN
    FROM @temp
) x
PIVOT
(
    MAX(Value)
    FOR Caption IN ([StoneCut], [GemType], [TotalDiamondWeight])
) p;

Upvotes: 2

forpas
forpas

Reputation: 164139

It seems like you have to get 2 groupings of your table and combine them with UNION:

select
  ProductId,
  min(case when Caption = 'StoneCut' then Value end) StoneCut,
  min(case when Caption = 'GemType' then Value end) GemType,
  min(case when Caption = 'TotalDiamondWeight' then Value end) TotalDiamondWeight
from temp
group by ProductId
union 
select
  ProductId,
  max(case when Caption = 'StoneCut' then Value end) StoneCut,
  max(case when Caption = 'GemType' then Value end) GemType,
  max(case when Caption = 'TotalDiamondWeight' then Value end) TotalDiamondWeight
from temp
group by ProductId

See the demo

Upvotes: 0

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30625

does this answers your question?

select * from (
select 
 ProductId, 
 StoneCut, 
 max(GemType) over (partition by ProductId) GemType,
 max(TotalDiamondWeight) over (partition by ProductId) TotalDiamondWeight
from
(
  select 
    ProductId, 
    IIF(Caption = 'StoneCut', Value, '') StoneCut,
    IIF(Caption = 'GemType', Value, '') GemType,
    IIF(Caption = 'TotalDiamondWeight', Value, '') TotalDiamondWeight
  from @temp
) t
)t2
where StoneCut != ''

Fiddle

Upvotes: 0

Related Questions