CODEWITHSUNDEEP
sqlsql-server
user9273223
user9273223

Reputation:

SQL Columns to Rows- for a View

I have a view which has

ID       INQCLASS  INQDETAIL     Period      BAL
1233     GROSS      water     12-3-2017     233.32
1233     GROSS      ENergy    12-3-2017     122.00

ID,INQCLASS, Period is same. Except the INQDETAIL and BAL I want to combine this into a single row which displays water and energy Bal.

Any Suggestions would be helpful. Thank you

Upvotes: 2

Views: 69

Answers (4)

Raj
Raj

Reputation: 502

Try this:

SELECT *
FROM 
     (SELECT * FROM #temp) AS P
PIVOT
(
   max(bal) FOR INQDETAIL IN ([water], [ENergy])
) AS pv1

Upvotes: 0

Greg Viers
Greg Viers

Reputation: 3523

SELECT ID, 
    INQCLASS, 
    Period, 
    MAX(CASE WHEN INQDETAIL = 'water' then BAL else 0 end) as WaterBal,
    MAX(CASE WHEN INQDETAIL = 'ENergy' then BAL else 0 end) as ENergyBal
FROM View_Name
GROUP BY ID, INQLASS, Period

The case statement serves to show the BAL only when the condition is met. So with case alone, this would still return two rows for each item, but one would have a Waterbal value and no energybal value, and the other would be the reverse.

When you do GROUP BY, every field has to either be in the GROUP BY list (in this case, ID, INQCLASS, Period), or have an aggregate function like SUM, MAX, COUNT, etc. (in this case Waterbal and energyBal have aggregate functions).

The GROUP BY in this case collapses the common ID, INQLASS, Period into single rows, and then takes the largest (MAX) value for Waterbal and energyBal. Since one is always 0, it simply supplies the other one.

Upvotes: 2

Larry Beasley
Larry Beasley

Reputation: 334

A simple pivot table ought to do it. As long as you know Inqdetail values ahead of time:

select ID,
     INQCLASS, 
     [Period], 
     [Water] AS [Water Bal],
     [Energy] as [Energy Bal]
from
(
    select [ID], 
        [INQCLASS], 
        [INQDETAIL], 
        [Period], 
        [BAL] 
    from #util
) As Utilities
PIVOT
(
  SUM([BAL])
  FOR [inqdetail] IN ([Water],[Energy])
 ) AS Pvttbl

Upvotes: 0

Newbie92
Newbie92

Reputation: 27

Try something like this:

SELECT INQDETAIL
, PERIOD
, SUM(BAL) AS energy_Bal       
FROM your_view
WHERE INQDETAIL LIKE 'water'
GROUP BY INQDETAIL
, PERIOD;

Upvotes: 0

Related Questions