CODEWITHSUNDEEP
sqlsql-serverpivot
Cesar
Cesar

Reputation: 527

Columns to Rows SQL

I'm trying to tranform a single row with columns to multiple rows.

Here is my select

     select 
'MOE' as TYPE,
SUM(sdl.xx_MOE) as [TOTAL MOE] ,
sdl.xx_PU_MOE,
'MOD' as TYPE,
SUM(sdl.xx_MOD) as [TOTAL MOD],
sdL.xx_PU_MOD,
'MOU' as TYPE,
SUM(sdl.xx_MOU) as [TOTAL MOU],
sdl.xx_PU_MOU
from SaleDocumentLine sdl inner join 
Deal d on sdl.DealId = d.Id 
where d.id = 'CC1800679' and sdl.LineType = 2
group by xx_PU_MOU, xx_PU_MOE, xx_PU_MOD

Result : Result

I want to achieve something like this :

---------------------------------
|TYPE       | TOTAL | UNIT PRICE|
---------------------------------
|MOE        | 136   |  34       |
---------------------------------
|MOD        | 22    |  34       |
---------------------------------
|MOU        | 122   |  34       |
---------------------------------

I think I doable with pivot but as I never worked with it, it's kinda obscure for me.

Thanks.

Upvotes: 0

Views: 73

Answers (3)

Sreenu131
Sreenu131

Reputation: 2516

Try this below script

;WITH CTE([Type1],TotalMoe,xx_PU_MOE,[Type2],TotalMoD,xx_PU_MOD,[Type3],TotalMoU,xx_PU_MOU)
AS
(
SELECT 'MOE',13.600000000,34.00000000,'MOD',22.00000000,34.00000000,'MOU',122.00000000,34.00000000
)
SELECT   [Type]
        ,TOTAL
        ,UNIT_PRICE 
FROM CTE
CROSS APPLY (VALUES([Type1],TotalMoe,xx_PU_MOE),
                    ([Type2],TotalMoD,xx_PU_MOD),
                    ([Type3],TotalMou,xx_PU_MOU))D ([Type],TOTAL, UNIT_PRICE)

Result

Type    TOTAL           UNIT_PRICE
-------------------------------------
MOE     13.600000000    34.00000000
MOD     22.000000000    34.00000000
MOU     122.000000000   34.00000000

Upvotes: 0

Nishant Gupta
Nishant Gupta

Reputation: 3656

WITH CTE AS
(
  SELECT *
  from SaleDocumentLine sdl inner join 
  Deal d on sdl.DealId = d.Id 
  where d.id = 'CC1800679' and sdl.LineType = 2
)
SELECT 'MOE' as TYPE,
       SUM(sdl.xx_MOE) as TOTAL,
       xx_PU_MOE 'UNIT PRICE'
FROM CTE
GROUP BY xx_PU_MOE
 UNION ALL 
SELECT 'MOD' as TYPE,
       SUM(sdl.xx_MOD) as TOTAL,
       xx_PU_MOD 'UNIT PRICE'
FROM CTE
GROUP BY xx_PU_MOD
 UNION ALL 
SELECT 'MOU' as TYPE,
       SUM(sdl.xx_MOU) as TOTAL,
       xx_PU_MOU 'UNIT PRICE'
FROM CTE
GROUP BY xx_PU_MOU;

Upvotes: 1

Thom A
Thom A

Reputation: 95561

This is a bit of guess work without any sample data, however, this should be enough to get you started; provided I've guessed correctly.

I can't see (at least from what we have) that you have a table with the names of the types, thus I've used a CTE containing the values needed:

WITH [Types] AS (
    SELECT [Type]
    FROM (VALUES('MOE'),('MOD'),('MOU')) V([Type]))
SELECT T.[Type],
       SUM(CASE T.[Type] WHEN 'MOE' THEN sdl.xx_MOE
                         WHEN 'MOD' THEN sdl.xx_MOD
                         WHEN 'MOU' THEN sdl.xx_MOU END) AS Total
FROM SaleDocumentLine sdl
     INNER JOIN Deal d ON sdl.DealId = d.Id 
     CROSS JOIN [Types] T
WHERE d.id = 'CC1800679'
  AND sdl.LineType = 2
GROUP BY T.[Type];

Upvotes: 2

Related Questions