July 24, 2012 at 7:22 am
CREATE TABLE [ODS_Non_Rtl_Cr_Risk_Aggregation](
[EC_DRAWN] [numeric](18, 6) NULL,
[EC_UNDRAWN] [numeric](18, 6) NULL,
[EC_DRAWN_R] [numeric](18, 6) NULL,
[EC_UNDRAWN_R] [numeric](18, 6) NULL,
[EC_DRAWN_S] [numeric](18, 6) NULL,
[BIS_FLAG] [char](3) NULL
) ON [PRIMARY]
INSERT INTO [ODS_Non_Rtl_Cr_Risk_Aggregation]
([EC_DRAWN]
,[EC_UNDRAWN]
,[EC_DRAWN_R]
,[EC_UNDRAWN_R]
,[EC_DRAWN_S]
,[BIS_FLAG])
VALUES (10.25,10.35,10.25,10.35,20.35,'X')
INSERT INTO [ODS_Non_Rtl_Cr_Risk_Aggregation]
([EC_DRAWN]
,[EC_UNDRAWN]
,[EC_DRAWN_R]
,[EC_UNDRAWN_R]
,[EC_DRAWN_S]
,[BIS_FLAG])
VALUES (10.25,10.35,10.25,10.35,20.35,'Y')
select * from [ODS_Non_Rtl_Cr_Risk_Aggregation]
Required Output:
Value
EC_drawn_x 10.25
ec_drawn_y 10.25
ec_undrawn_x 10.35
ec_undrawn_y 10.35
EC_drawn_r_x 10.25
ec_drawn_r_y 10.25
ec_undrawn_r_x 10.35
ec_undrawn_r_y 10.35
Ec_drawn_s_x 20.35
ec_drawn_s_y 20.35
**[ODS_Non_Rtl_Cr_Risk_Aggregation] table contains only two rows
and the values for bis_flag will be 'X' and 'Y'always
Please help me in this regard
July 24, 2012 at 7:38 am
You haven't even tried, have you?
Shouldn't be too hard based on your previous post
http://www.sqlservercentral.com/Forums/Topic1332742-392-1.aspx
July 24, 2012 at 7:22 pm
While BrainDonor's (love that name) suggestion will work, I find the UNPIVOT syntax so unwieldy (especially on double and triple UNPIVOTs) that I like to steer people towards the CROSS APPLY VALUES approach whenever I can:
CREATE TABLE #ODS_Non_Rtl_Cr_Risk_Aggregation#(
[EC_DRAWN] [numeric](18, 6) NULL,
[EC_UNDRAWN] [numeric](18, 6) NULL,
[EC_DRAWN_R] [numeric](18, 6) NULL,
[EC_UNDRAWN_R] [numeric](18, 6) NULL,
[EC_DRAWN_S] [numeric](18, 6) NULL,
[BIS_FLAG] [char](3) NULL
) ON [PRIMARY]
INSERT INTO #ODS_Non_Rtl_Cr_Risk_Aggregation#
([EC_DRAWN]
,[EC_UNDRAWN]
,[EC_DRAWN_R]
,[EC_UNDRAWN_R]
,[EC_DRAWN_S]
,[BIS_FLAG])
VALUES (10.25,10.35,10.25,10.35,20.35,'X')
INSERT INTO #ODS_Non_Rtl_Cr_Risk_Aggregation#
([EC_DRAWN]
,[EC_UNDRAWN]
,[EC_DRAWN_R]
,[EC_UNDRAWN_R]
,[EC_DRAWN_S]
,[BIS_FLAG])
VALUES (10.25,10.35,10.25,10.35,20.35,'Y')
SELECT [column], [value]
FROM #ODS_Non_Rtl_Cr_Risk_Aggregation#
CROSS APPLY (
VALUES ('EC_DRAWN_X', CASE WHEN BIS_FLAG = 'X' THEN EC_DRAWN ELSE NULL END)
,('EC_DRAWN_Y', CASE WHEN BIS_FLAG = 'Y' THEN EC_DRAWN ELSE NULL END)
,('EC_UNDRAWN_X', CASE WHEN BIS_FLAG = 'X' THEN EC_UNDRAWN ELSE NULL END)
,('EC_UNDRAWN_Y', CASE WHEN BIS_FLAG = 'Y' THEN EC_UNDRAWN ELSE NULL END)
,('EC_DRAWN_R_X', CASE WHEN BIS_FLAG = 'X' THEN EC_DRAWN_R ELSE NULL END)
,('EC_DRAWN_R_Y', CASE WHEN BIS_FLAG = 'Y' THEN EC_DRAWN_R ELSE NULL END)
,('EC_UNDRAWN_R_X', CASE WHEN BIS_FLAG = 'X' THEN EC_UNDRAWN_R ELSE NULL END)
,('EC_UNDRAWN_R_Y', CASE WHEN BIS_FLAG = 'Y' THEN EC_UNDRAWN_R ELSE NULL END)
,('EC_DRAWN_S_X', CASE WHEN BIS_FLAG = 'X' THEN EC_DRAWN_S ELSE NULL END)
,('EC_DRAWN_S_Y', CASE WHEN BIS_FLAG = 'Y' THEN EC_DRAWN_S ELSE NULL END)
) a([column], [value])
WHERE [value] IS NOT NULL
DROP TABLE #ODS_Non_Rtl_Cr_Risk_Aggregation#
You may even find that CROSS APPLY VALUES is more efficient than UNPIVOT (to the tune of about 15%).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 24, 2012 at 10:47 pm
thanks so much
July 25, 2012 at 2:06 am
BrainDonor (7/25/2012)
A nice solution dwain - it would never have crossed my mind to try that.
Actually, it's a technique that I don't think has widespread knowledge. I hit upon it quite by accident when exploring VALUES sets in SQL 2008, then found some writings on it after Googling "CROSS APPLY VALUES UNPIVOT."
I have an article being published here on SSC on 02 Aug showing some performance stats for it. You might want to watch for it. 🙂
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply