CODEWITHSUNDEEP
sqlt-sqlpivot
Roboman1723
Roboman1723

Reputation: 103

What am I doing wrong here with my PIVOT function?

I'm pretty new to SQL and am trying to get the query to work below using the PIVOT() function. Below is what I've tried based on what I've read online. Can someone help me out here?

Current SQL query:

SELECT TOP (1000) *
FROM
(SELECT CVE, SERV_WS_RATING, RATING
FROM dummyTable
) AS sourceTable PIVOT([RATING] FOR [SERV_WS_RATING] IN ([SERVER],[WORKSTATION])) AS pivotTable

Current Output:

CVE Server/Workstation Rating Rating
CVE-1999-0001 Server P1
CVE-1999-0001 Workstation P1
CVE-1999-0002 Server P3
CVE-1999-0002 Workstation P3
CVE-1999-0003 Server P5
CVE-1999-0003 Workstation P5

Desired Output:

CVE Server Rating Workstation Rating
CVE-1999-0001 P1 P1
CVE-1999-0002 P3 P3
CVE-1999-0003 P5 P5

Upvotes: 0

Views: 46

Answers (1)

SteveC
SteveC

Reputation: 6015

One alternative is conditional aggregation

select CVE, 
       max(case when SERV_WS_RATING='Server' then RATING else null end) [Server Rating],
       max(case when SERV_WS_RATING='Workstation' then RATING else null end) [Workstation Rating]       
from dummyTable
group by CVE
order by CVE;

Upvotes: 1

Related Questions