July 24, 2009 at 6:04 am
Hi All,
From the below example, can you guy tell me why my case function is not working as per the script. Pl correct me where i am wrong.
Here, i should use PIVOT function because i want "fldTCN" column values as column name. If the "fldCDate" has value i have keep that otherwise i have to mention "N/A" against the "fldCDate" in which the result is coming as NULL.
IF EXISTS(SELECT 1 FROM sys.objects WHERE [name] = 'ACM')
BEGIN
DROP TABLE ACM
END
IF EXISTS(SELECT 1 FROM sys.objects WHERE [name] = 'TCE')
BEGIN
DROP TABLE TCE
END
CREATE TABLE TCE (fldID int PRIMARY KEY, fldTCN varchar(8),fldUName varchar(8))
CREATE TABLE ACM (fldName varchar(8),fldID int FOREIGN KEY REFERENCES TCE(fldID),fldCDate varchar(1010))
INSERT INTO TCE
SELECT 10,'1.1','Sample1' UNION ALL
SELECT 20,'2.2','Sample2' UNION ALL
SELECT 30,'3.3','Sample3' UNION ALL
SELECT 40,'4.4','Sample4' UNION ALL
SELECT 50,'5.5','Sample5'
INSERT INTO ACM
SELECT '1.1',10,'2009-07-07' UNION ALL
SELECT '2.2',20,NULL UNION ALL
SELECT '3.3',30,'2009-07-07' UNION ALL
SELECT '4.4',40,NULL
SELECT Row,fldID,fldUName,[1.1] AS [1.1],[2.2] AS [2.2],[3.3] AS [3.3],[4.4] AS [4.4]
FROM(SELECT DENSE_RANK() OVER(ORDER BY T.fldID) AS Row,
T.fldID,
fldUName,
fldTCN,
CASE WHEN fldCDate IS NULL THEN 'N/A'
ELSE fldCDate END AS CDate
FROM TCE AS T
INNER JOIN ACM AS A
ON (T.fldID = A.fldID)) P
PIVOT
(
MAX(fldTCN)
FOR fldTCN IN ([1.1],[2.2],[3.3],[4.4])
) AS Pvt
Appreciate Your Help!!!
---
July 24, 2009 at 8:40 am
I have no experience with the PIVOT statement, but I did have one question for you. What do you get if you take the case statement out and just pull the fldCDate? Are you getting NULLs?
July 24, 2009 at 12:21 pm
you need to make sure you have the right data types coming out of the CASE. Case will try to make its best guess which data type to use, and implicitly conviert the OTHER types to the one it picks, so you want to help it along.
If I had to guess - flcDate is a datetime and 'N/A' is clearly a string, so you might ned up with some issues just because of that.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
July 26, 2009 at 12:25 am
I want my result set should be like below.
RowfldIDfldUName1.12.2 3.3 4.4
110 Sample1 2009-07-07 NULL NULL NULL
220 Sample2 NULL N/A NULL NULL
330 Sample3 NULL NULL 2009-07-07 NULL
440 Sample4NULL NULL NULL N/A
If the fldCDate has value according to the condition it shows the date otherwise 'N/A' should come.
If i change the fldCDate as DATETIME, i am getting the same result[Not getting the expected result].
How can i achieve my expected result.
July 26, 2009 at 9:37 am
Your CASE statement is fine. The problem is in your PIVOT statement. You have PIVOT
(
MAX(fldTCN)
FOR fldTCN IN ([1.1],[2.2],[3.3],[4.4])
) AS Pvt
This gives you the max of the field that you are pivoting on, where what you want is the max of the date field. I made this simple change and got the results you were looking for. PIVOT
(
MAX(CDate)
FOR fldTCN IN ([1.1],[2.2],[3.3],[4.4])
) AS Pvt
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply