Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

Unpivot Select?

My attached code works fine and returns my data like this:

AmtRequested     FunderAmtNet     AnnuitantAmtAdj     Profit
613743.05             593867.82      340500.00          253367.8200

I need it unpivoted?  So I see the data this way...

FundsType                Amt
AmtRequested        613743.05
FunderAmtNet         593867.82
AnnuitantAmtAdj    340500.00    
Profit                       253367.8200

SELECT	Sum(u.funderAmtRequested) AS AmtRequested,
		Sum(u.FunderAmtNet) as FunderAmtNet, 
		Sum(u.AnnuitantAmtAdj) as AnnuitantAmtAdj, 
		Sum(u.Profit) as Profit 
			
FROM	CRMPROD_01.dbo.vP_UnionPWDealsEventsSub u INNER JOIN 
		CRMPROD_01.dbo.P_Funders f ON u.FunderID = f.FunderID INNER JOIN
		CRMPROD_01.dbo.leads l ON u.LeadID = l.id
WHERE	(NOT (l.last_name LIKE 'Test%'))

Open in new window

0
lrbrister
Asked:
lrbrister
  • 5
  • 3
  • 2
2 Solutions
 
rajeshprasathCommented:
SELECT FundsType, Amt
FROM
(
SELECT      Sum(u.funderAmtRequested) AS AmtRequested,
            Sum(u.FunderAmtNet) as FunderAmtNet,
            Sum(u.AnnuitantAmtAdj) as AnnuitantAmtAdj,
            Sum(u.Profit) as Profit
                  
FROM      CRMPROD_01.dbo.vP_UnionPWDealsEventsSub u INNER JOIN
            CRMPROD_01.dbo.P_Funders f ON u.FunderID = f.FunderID INNER JOIN
            CRMPROD_01.dbo.leads l ON u.LeadID = l.id
WHERE      (NOT (l.last_name LIKE 'Test%'))
) p
UNPIVOT
(
FundsType IN (AmtRequested, FunderAmtNet, AnnuitantAmtAdj, Profit )
) AS UNPVT
0
 
lrbristerAuthor Commented:
rajeshprasath:
Getting an error on
 
Msg 156, Level 15, State 1, Line 16
Incorrect syntax near the keyword 'IN'.
...
UNPIVOT
(
FundsType IN (AmtRequested, FunderAmtNet, AnnuitantAmtAdj, Profit )
) AS UNPVT
0
 
ralmadaCommented:
correct syntax should be:
SELECT FundsType, Amt
FROM
(
SELECT      Sum(u.funderAmtRequested) AS AmtRequested,
            Sum(u.FunderAmtNet) as FunderAmtNet, 
            Sum(u.AnnuitantAmtAdj) as AnnuitantAmtAdj, 
            Sum(u.Profit) as Profit 
                  
FROM      CRMPROD_01.dbo.vP_UnionPWDealsEventsSub u INNER JOIN 
            CRMPROD_01.dbo.P_Funders f ON u.FunderID = f.FunderID INNER JOIN
            CRMPROD_01.dbo.leads l ON u.LeadID = l.id
WHERE      (NOT (l.last_name LIKE 'Test%'))
) p
UNPIVOT
(
Amt for FundsType IN (AmtRequested, FunderAmtNet, AnnuitantAmtAdj, Profit )
) AS UNPVT

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
rajeshprasathCommented:
sorry, try this, it will work.....

SELECT FundsType, Amt
FROM
(
SELECT      Sum(u.funderAmtRequested) AS AmtRequested,
            Sum(u.FunderAmtNet) as FunderAmtNet,
            Sum(u.AnnuitantAmtAdj) as AnnuitantAmtAdj,
            Sum(u.Profit) as Profit
                 
FROM      CRMPROD_01.dbo.vP_UnionPWDealsEventsSub u INNER JOIN
            CRMPROD_01.dbo.P_Funders f ON u.FunderID = f.FunderID INNER JOIN
            CRMPROD_01.dbo.leads l ON u.LeadID = l.id
WHERE      (NOT (l.last_name LIKE 'Test%'))
) p
UNPIVOT
(
Amt for FundsType IN (AmtRequested, FunderAmtNet, AnnuitantAmtAdj, Profit )
) AS UNPVT
0
 
lrbristerAuthor Commented:
ralmada:
Perfect
0
 
rajeshprasathCommented:
Msg 8167, Level 16, State 1, Line 1
The type of column "Profit" conflicts with the type of other columns specified in the UNPIVOT list.

if you still get the above error,

just convert the profit column to numeric with scale 2.
0
 
lrbristerAuthor Commented:
rajeshprasath:
My apologies...
I should have given you a chance to correct it before I accpted a different answer.
0
 
lrbristerAuthor Commented:
Allright...
Now I'm in a pickle...
I thought I accepted ralmada: answer...which was correct.
Just a few minutes later rajeshprasath: posted his corrected answer...which I thought was too late.
You guys mind splitting the points and someone just yell at me?
0
 
ralmadaCommented:
I would suggest:
400 points rajeshprashath
100 points ralmada
 
0
 
lrbristerAuthor Commented:
I'm cool with that. I'll post that shortly
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now