Need help with the SELECT statement.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help with the SELECT statement.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2009-12-10 : 23:34:11

--SQL 2005.
I am converting Access db to SQL Server and need some help how to construct the query
to desire results below. Thank you in advance.

IF OBJECT_ID('Tempdb.dbo.#LoanComp', 'u') IS NOT NULL
DROP TABLE #LoanComp
GO
CREATE TABLE #LoanComp
(
CompId INT NULL,
PurchPerf BIT NULL,
PurchNonPerf BIT NULL,
PurchRePerf BIT NULL,
PurchSemiPerf BIT NULL
)
GO

INSERT INTO #LoanComp VALUES (8, 1, 0, 1, 1)
INSERT INTO #LoanComp VALUES (9, 1, 1, 0, 0)
INSERT INTO #LoanComp VALUES (11, 0, 0, 0, 0)
INSERT INTO #LoanComp VALUES (17, 1, 1, 1, 1)
INSERT INTO #LoanComp VALUES (40, 0, 1, 1, 1);
GO

SELECT *
FROM #LoanComp;
GO

CompId PurchPerf PurchNonPerf PurchRePerf PurchSemiPerf
----------- --------- ------------ ----------- -------------
8 1 0 1 1
9 1 1 0 0
11 0 0 0 0
17 1 1 1 1
40 0 1 1 1

-- How can I convert 1 to corresponding the businessrules below and concatenate with
-- other purchase types also.

-- Business Rules:
1 = Purchase-Performing else 0 = ' '
1 = Purchase-Non-Performing else 0 = ' '
1 = Purchase-RE-Performing else 0 = ' '
1 = Purchase-Semi-Performingelse 0 = ' '

-- Desire results:
CompId PurchaseType
------ ---------
8 Purchase-Performing, Purchase-RE-Performing, Purchase-Semi-Performing
9 Purchase-Performing, Purchase-Non-Performing
11
17 Purchase-Performing,Purchase-Non-Performing, Purchase-RE-Performing, Purchase-Semi-Performing
40 Purchase-non-Performing, Purchase-RE-Performing, Purchase-Semi-Performing

--Testing...

SELECT CompId,
CASE
WHEN PurchPerf = 1 THEN 'Purchase-Performing'
WHEN PurchNonPerf = 1 THEN 'Purchase-Non-Performing'
WHEN PurchRePerf = 1 THEN 'Purchase-RE-Performing'
WHEN PurchSemiPerf = 1 THEN 'Purchase-Semi-Performing'
END AS 'PurchaseType'
FROM #LoanComp;

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-11 : 01:08:23

Can you expect this??

SELECT CompId,
CASE WHEN PurchPerf = 1 THEN 'Purchase-Performing' else '' end,
CASE WHEN PurchNonPerf = 1 THEN 'Purchase-Non-Performing' else '' end,
CASE WHEN PurchRePerf = 1 THEN 'Purchase-RE-Performing' else '' end,
CASE WHEN PurchSemiPerf = 1 THEN 'Purchase-Semi-Performing' else '' end
FROM #LoanComp

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-11 : 10:18:58

Not saying this is the BEST way, but it is A way...

Select CompId,
(case when PurchPerf = 1 then 'PurchPerf' else '' end + case when PurchNonPerf = 1 then ' ,PurchPerf' else '' end + case when PurchRePerf = 1 then ' ,PurchRePerf' else '' end + case when PurchSemiPerf = 1 then ' ,PurchSemiPerf' else '' end ) as String
From #LoanComp

You just need to create a condition to remove the first value if Left(String, 1) = ','

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-12-11 : 12:29:49

[code];With CTE(CID,Rule,Stat)
AS
(
SELECT CompId,Rule,Stat
FROM
(SELECT CompId,
PurchPerf AS Purchase-Performing ,
PurchNonPerf AS Purchase-Non-Performing ,
PurchRePerf AS Purchase-RE-Performing,
PurchSemiPerf AS Purchase-Semi-Performing
FROM #LoanComp )t
UNPIVOT (Stat FOR Rule IN ('Purchase-Performing','Purchase-Non-Performing','Purchase-RE-Performing','Purchase-Semi-Performing'))u
)
SELECT t.CID,LEFT(t.u,LEN(t.u)-1) AS PurchaseType
FROM (SELECT DISTINCT CID FROM CTE) t
CROSS APPLY(SELECT CASE WHEN Stat=1 THEN Rule ELSE '' END + ','
FROM CTE
WHERE CID= t.CID
FOR XML PATH(''))t(u)
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources