November 4, 2016 at 8:03 am
Thank you for help.
What should i do to get two columns with below expected output.
Client fields value ClientClient
----------- -------- ------------------ ---------
111 Decision accept NULL
111 Status deposited 111
112 decision accept NULL
113 Decision accept NULL
114 Decision accept NULL
114 Status contract 114
115 Decision Conditional Accept Null
115 Status deposited 115
116 Decision Conditional Accept NULL
117 Decision Conditional Accept NULL
118 Status contract 118
118 Decision Conditional Accept NULL
November 4, 2016 at 8:16 am
Repeatedly asking the same question is not going to speed up replies.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 4, 2016 at 8:25 am
Eirikur Eiriksson (11/4/2016)
sgmunson (11/4/2016)
And here's an alternative that achieves the desired output (however conflicting with the original post that may be...), and shows the performance characteristics, as I'm guessing this sample data would likely be much more extensive in the real world, and may well have other values not shown here...
CREATE TABLE #TEST_DATA (
Client int,
Fields varchar(30),
Value varchar(30)
);
CREATE NONCLUSTERED INDEX IX_TESTDATA_Client_INCLUDE_Fields_Value ON #TEST_DATA
(
Client ASC
)
INCLUDE (Fields, Value);
CREATE NONCLUSTERED INDEX IX_TESTDATA_Fields_Value_INCLUDE_Client ON #TEST_DATA
(
Fields ASC,
Value ASC
)
INCLUDE (Client);
INSERT INTO #TEST_DATA (Client, Fields, Value)
VALUES(111, 'Decision', 'accept'),
(111, 'Status', 'deposited'),
(112, 'decision', 'accept'),
(113, 'Decision', 'accept'),
(114, 'Decision', 'accept'),
(114, 'Status', 'contract'),
(115, 'Decision', 'Conditional Accept'),
(115, 'Status', 'deposited'),
(116, 'Decision', 'Conditional Accept'),
(117, 'Decision', 'Conditional Accept'),
(118, 'Status', 'contract'),
(118, 'Decision', 'Conditional Accept');
DBCC FREEPROCCACHE;
SET STATISTICS IO, TIME ON;
WITH ALL_DATA AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY Client ORDER BY CASE Fields WHEN 'Decision' THEN 1 WHEN 'Status' THEN 2 ELSE 9999 END, Value) AS RN
FROM #TEST_DATA
)
SELECT A1.Client, A1.Fields, A1.Value,
CASE
WHEN (
(A1.Fields = 'Decision' AND A1.Value IN ('accept', 'Conditional Accept'))
AND
(A2.Fields = 'Status' AND A2.Value IN ('contract', 'deposited'))
)
OR
(
(A2.Fields = 'Decision' AND A2.Value IN ('accept', 'Conditional Accept'))
AND
(A1.Fields = 'Status' AND A1.Value IN ('contract', 'deposited'))
) THEN A1.Client
ELSE NULL
END AS NewCol
FROM ALL_DATA AS A1
LEFT OUTER JOIN ALL_DATA AS A2
ON A1.Client = A2.Client
AND A1.RN = 2
AND A2.RN = 1
ORDER BY A1.Client, A1.Fields;
SET STATISTICS TIME, IO OFF;
DROP TABLE #TEST_DATA;
Nice in the terms of only doing 2 scans but the down side is that the execution plan has two sort operators, on for each scan which are quite costly in the absence of a POC index for the ROW_NUMBER function.
😎
Well, the 2 scans is only because of the indexes I created, which may or may not exist in the real world. As it turns out, I can dispense with the first two, as they don't get used. The sorts could probably be avoided if there was some other field in the source table that would clearly indicate the order in which the data was entered, and said field was indexed. Alternatively, I could take out the ORDER BY... Here's my final version:
SET NOCOUNT ON;
CREATE TABLE #TEST_DATA (
Client int,
Fields varchar(30),
Value varchar(30)
);
CREATE CLUSTERED INDEX IX_TESTDATA_Value_Client ON #TEST_DATA
(
Value ASC,
Client ASC
);
INSERT INTO #TEST_DATA (Client, Fields, Value)
VALUES(111, 'Decision', 'accept'),
(111, 'Status', 'deposited'),
(112, 'Decision', 'accept'),
(113, 'Decision', 'accept'),
(114, 'Decision', 'accept'),
(114, 'Status', 'contract'),
(115, 'Decision', 'Conditional Accept'),
(115, 'Status', 'deposited'),
(116, 'Decision', 'Conditional Accept'),
(117, 'Decision', 'Conditional Accept'),
(118, 'Status', 'contract'),
(118, 'Decision', 'Conditional Accept');
CREATE TABLE #ALL_DATA (
RN bigint,
Client int,
Fields varchar(30),
Value varchar(30)
);
CREATE CLUSTERED INDEX IX_ALLDATA_Client_Fields ON #ALL_DATA
(
Client ASC,
Fields ASC
);
DBCC FREEPROCCACHE;
SET STATISTICS IO, TIME ON;
--WITH ALL_DATA AS (
INSERT INTO #ALL_DATA (RN, Client, Fields, Value)
SELECT ROW_NUMBER() OVER(PARTITION BY Client ORDER BY CASE Fields WHEN 'Decision' THEN 1 WHEN 'Status' THEN 2 ELSE 9999 END, Value) AS RN,
Client,
Fields,
Value
FROM #TEST_DATA
--)
SELECT A1.Client, A1.Fields, A1.Value,
CASE
WHEN (
(A1.Fields = 'Decision' AND A1.Value IN ('accept', 'Conditional Accept'))
AND
(A2.Fields = 'Status' AND A2.Value IN ('contract', 'deposited'))
)
OR
(
(A2.Fields = 'Decision' AND A2.Value IN ('accept', 'Conditional Accept'))
AND
(A1.Fields = 'Status' AND A1.Value IN ('contract', 'deposited'))
) THEN A1.Client
ELSE NULL
END AS NewCol
FROM #ALL_DATA AS A1
LEFT OUTER JOIN #ALL_DATA AS A2
ON A1.Client = A2.Client
AND A1.RN = 2
AND A2.RN = 1
ORDER BY A1.Client, A1.Fields;
SET STATISTICS TIME, IO OFF;
DROP TABLE #TEST_DATA;
DROP TABLE #ALL_DATA;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 4, 2016 at 9:23 am
sindhupavani123 (11/4/2016)
Thank you all for the help.What should i do if i want two columns with below output?
Client fields value ClientClient
----------- -------- ------------------ ---------
111 Decision accept NULL
111 Status deposited 111
112 decision accept NULL
113 Decision accept NULL
114 Decision accept NULL
114 Status contract 114
115 Decision Conditional Accept Null
115 Status deposited 115
116 Decision Conditional Accept NULL
117 Decision Conditional Accept NULL
118 Status contract 118
118 Decision Conditional Accept NULL
It's impossible to be sure where your columns begin and end using this format.
Instead, modify the sample data setup provided by Eirikur, until it exactly matches what you expect in your output. This should get you started:
;WITH SAMPLE_DATA(Client, fields, value) AS
( SELECT X.Client, X.fields, X.value
FROM ( VALUES
(111,'Decision','accept')
,(111,'Status','deposited')
,(112,'decision','accept')
,(113,'Decision','accept')
,(114,'Decision','accept')
,(114,'Status','contract')
,(115,'Decision','Conditional Accept')
,(115,'Status','deposited')
,(116,'Decision','Conditional Accept')
,(117,'Decision','Conditional Accept')
,(118,'Status','contract')
,(118,'Decision','Conditional Accept')
) X (Client, fields, value)
)
SELECT *
FROM SAMPLE_DATA b
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 8, 2016 at 7:27 am
Thank you all for your replies.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply