January 24, 2011 at 1:05 am
I have table like this. CREATE TABLE CRD_TEST (
CUST_ID INT NULL,
CRD_1 CHAR(2) NULL,
BID_1 CHAR(4) NULL,
CRD_2 CHAR(2) NULL,
BID_2 CHAR(4) NULL,
CRD_3 CHAR(2) NULL,
BID_3 CHAR(4) NULL,
CRD_4 CHAR(2) NULL,
BID_4 CHAR(4) NULL,
CRD_5 CHAR(2) NULL,
BID_5 CHAR(4) NULL,
CRD_6 CHAR(2) NULL,
BID_6 CHAR(4) NULL,
CRD_7 CHAR(2) NULL,
BID_7 CHAR(4) NULL,
CRD_8 CHAR(2) NULL,
BID_8 CHAR(4) NULL,
CRD_9 CHAR(2) NULL,
BID_9 CHAR(4) NULL,
CRD_10 CHAR(2) NULL,
BID_10 CHAR(4) NULL
)
GO
INSERT CRD_TEST VALUES (12345,'DR','BID1','DR','BID2','DR','BID3','DR','BID4','DR','BID5','DR','BID6','DR','BID7','CR','CID1','CR','DID1','CR','EID1')
GO
INSERT CRD_TEST VALUES (12346,'DR','BID1','DR','BID2','DR','BID3','DR','BID4','DR','BID5','DR','BID6','DR','BID7','DR','BID8','CR','AID1','CR','BID1')
GO
INSERT CRD_TEST VALUES (12347,'DR','BID1','DR','BID2','DR','BID3','DR','BID4','DR','BID5','DR','BID6','DR','BID7','DR','BID8','DR','BID9','CR','AID1')
GO
I need to create view that hold customer credit card bank information based on the CRD_X column.
My view looks like this...
12345, CID1, DID1, EID1
12346, AID1, BID1
12347, AID1
Can you shed some light?
January 24, 2011 at 3:10 am
What exactly is your question?
A little more information will certainly help... E.g. posting your expected result based on your sample data including some info whether you want to have it as a comma separated list or a table or whatever...
January 24, 2011 at 5:18 am
Thanks Lutz. At last, i need to create a UDF by passing CUST_ID
The table contains CRD_X column related to BID_X column.
If any CRD_X contains value "CR" then i need to list BID_X column value.
If any CRD_X contains "DR", ignore it.
In the 1st row, CR values are with CID1, DID1, EID1
In the last row, CR values are with AID1
January 24, 2011 at 9:19 am
To be clear here, because your data doesn't support your result directly, you want all the data in the list that's NOT defaulted to bid1/bid2/bid3...?
Nevermind, sorry, you answered this above, I just misread it. Get back to you on that.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 24, 2011 at 10:05 am
The For XML bit is slightly modified by me, but mostly is borrowed from here:
http://www.sqlservercentral.com/articles/FOR+XML+PATH/70203/
This code should do what you're looking for. First it normalizes the data so you can use a where clause properly. Then it rebuilds the data into the format you've described:
IF OBJECT_ID( 'tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
-- First, a normalization of the data.
SELECT
CRD.Cust_ID,
CRD.Bidposition,
CRD.CrdValue,
Bid.BidValue
INTO
#tmp -- Modify this so it's a real table from now on somewhere in your system.
FROM
(SELECT
Cust_ID,
RIGHT( BidPosition, LEN( BidPosition) - 4) AS Bidposition,
CRDValue
FROM
CRD_Test AS ct
UNPIVOT
(CRDValue FOR BidPosition IN
( CRD_1, CRD_2, CRD_3, CRD_4, CRD_5, CRD_6, CRD_7, CRD_8, CRD_9, CRD_10)
) AS upvt
) AS CRD
JOIN
(SELECT
Cust_ID,
RIGHT( BidPosition, LEN( BidPosition) - 4) AS Bidposition,
BidValue
FROM
CRD_Test AS ct
UNPIVOT
(BidValue FOR BidPosition IN
( Bid_1, Bid_2, Bid_3, Bid_4, Bid_5, Bid_6, Bid_7, Bid_8, Bid_9, Bid_10)
) AS upvt
) AS Bid
ON
CRD.Cust_id = bid.Cust_id
AND CRD.BidPosition = Bid.BidPosition
-- Now, generate your non-normalized delimited list
SELECT DISTINCT
t.Cust_ID,
STUFF(
( SELECT ',' + t2.BidValue FROM #tmp AS t2 WHERE t2.CRDValue = 'CR' AND t2.Cust_ID = t.Cust_ID FOR XML PATH( ''))
, 1, 1, '') AS Bids
from
#tmp AS t
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 24, 2011 at 10:08 am
Here's something to get you started.
Step 1 is to transform the denormalized table back into a normalized structure. Since we already touching each and every row multiple times we can apply the filter to include only relevant rows.
Step2 depends on your required output. Unfortunately, there is no information whether you need it as a comma separated list or as separate columns.
The former can be achieved using the FOR XML PATH() approach. The latter by using ROW_NUMBER (assuming fixed column names).
Edit: I storngly recommend to reevaluate the table design. A normalized table will be much more useful...
;
WITH cte AS
(
SELECT
CUST_ID,
'1' AS CRD_grp,
CRD_1 AS CRD_val,
BID_1 AS BID_val
FROM CRD_TEST
WHERE CRD_1 <> 'DR'
UNION ALL
SELECT CUST_ID,'8',CRD_8, BID_8 FROM CRD_TEST WHERE CRD_8 <> 'DR'
UNION ALL
SELECT CUST_ID,'9',CRD_9, BID_9 FROM CRD_TEST WHERE CRD_9 <> 'DR'
)
SELECT *
FROM cte
January 24, 2011 at 6:57 pm
moorthy.jy (1/24/2011)
Thanks Lutz. At last, i need to create a UDF by passing CUST_IDThe table contains CRD_X column related to BID_X column.
If any CRD_X contains value "CR" then i need to list BID_X column value.
If any CRD_X contains "DR", ignore it.
In the 1st row, CR values are with CID1, DID1, EID1
In the last row, CR values are with AID1
My belief is that your sending a bad view after a bad table. What will you do with the values from the view? It'll be just as difficult as what you currently have in the table.
Here's the code I would use to build the VIEW in a much more flexible manner...
CREATE VIEW Crd_Test_Unpivoted AS
WITH
cteUnpivot AS
(
SELECT ct.Cust_ID, ca.Col, ca.Crd, ca.Bid
FROM Crd_Test ct
CROSS APPLY
(
SELECT 1, Crd_1, Bid_1 UNION ALL
SELECT 2, Crd_2, Bid_2 UNION ALL
SELECT 3, Crd_3, Bid_3 UNION ALL
SELECT 4, Crd_4, Bid_4 UNION ALL
SELECT 5, Crd_5, Bid_5 UNION ALL
SELECT 6, Crd_6, Bid_6 UNION ALL
SELECT 7, Crd_7, Bid_7 UNION ALL
SELECT 8, Crd_8, Bid_8 UNION ALL
SELECT 9, Crd_9, Bid_9 UNION ALL
SELECT 10, Crd_10, Bid_10
) ca (Col, Crd, Bid)
)
SELECT Cust_ID, Col, Crd, Bid
FROM cteUnpivot
;
GO
From there, you can do incredibly simple things like what you ask and still keep the output normalized...
SELECT Cust_ID, Col, Crd, Bid
FROM dbo.Crd_Test_Unpivoted
WHERE Cust_ID = 12345
AND Crd = 'CR'
;
... which produces the following output...
Cust_ID Col Crd Bid
----------- ----------- ---- ----
12345 8 CR CID1
12345 9 CR DID1
12345 10 CR EID1
... or you can get the output you asked for (which I still believe is a perpetuation of the original denormalized problem)...
SELECT upvt1.Cust_ID,
Bids =
STUFF(CAST(
(
SELECT ',' + upvt2.Bid
FROM Crd_Test_Unpivoted upvt2
WHERE upvt2.Cust_ID = upvt1.Cust_ID
AND upvt2.Crd = 'CR'
ORDER BY upvt2.Col
FOR XML PATH('')
) AS VARCHAR(MAX)),1,1,'')
FROM Crd_Test_Unpivoted upvt1
WHERE Crd = 'CR'
GROUP BY upvt1.Cust_ID
;
... and that provides the following output kind of like you requested...
Cust_ID Bids
----------- --------------
12345 CID1,DID1,EID1
12346 AID1,Bid1
12347 AID1
But, that's a pretty "ugly" output unless you're feeding a good and patient GUI programmer. Again, I would take the advice of everyone and normalize that table to look more like the output of the VIEW I created maybe leaving out the COL column.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 25, 2011 at 4:58 am
Thank you all for your support and time. As suggested, i will ask application guys to re-design the table structure. Thanks again.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply