CODEWITHSUNDEEP
sql-server
user2307049
user2307049

Reputation: 13

How to show the duplicate values in pivot table in SQL Server

I having table call Specf and I have a view View1 and using these two I need to generate required output.

SPECf:

P1 
P2
P3
P4
P5

View1:

Product SPESET      SPECf          Value 
A1      ABCSET       P1         C1
A1      ABCSET       P2         C2
A1      XYZSET       P3         C2
A2      ABXSET       P1         C1
A2      ABXSET       P4         C4
A2      ABXSET       P2         C1
A3      CDESET       P5         C2

Output required:

Product SPESET      SPE_P1   SPE_P2    SPE_P3    SPE_P4    SPE_P5 
A1      ABCSET       C1         C2       C2       null       null
A1      ABCSET       C1         C2       C2       null       null
A1      XYZSET       C1         C2       C2       null       null
A2      ABXSET       C1         C1       null      c4        null
A2      ABXSET       C1         C1       null      c4        null
A2      ABXSET       C1         C1       null      c4        null
A3      CDESET       null      null      null     null       c2

Upvotes: 0

Views: 97

Answers (1)

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

select V2.Product,
       V2.SPESET,
       P.P1 SPE_P1,
       P.P2 SPE_P2,
       P.P3 SPE_P3,
       P.P4 SPE_P4,
       P.P5 SPE_P5 
from  (
      select V1.Product, V1.SPECf, V1.Value
      from View1 as V1
      ) as V1
pivot (
      max(V1.Value) 
      for V1.SPECf in (P1, P2, P3, P4, P5)
      ) as P
inner join View1 as V2
  on P.Product = V2.Product

Upvotes: 1

Related Questions