SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Pivot help? ...or other approach?


Pivot help? ...or other approach?

Author
Message
inevercheckthis2002
inevercheckthis2002
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 474
This is the type of data that I'm working with:


USE TestDB
GO

CREATE TABLE testtable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
CompanyNumber nvarchar(50),
UserNumber nvarchar(50),
Birthdate datetime,
TestDate datetime,
TestCode nvarchar(50),
Score nvarchar(50),
FailFlag nvarchar(50)
);



Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16633 Visits: 19098
And what do you need?


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
inevercheckthis2002
inevercheckthis2002
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 474
Sorry - apparently posting from work is a problem.

This is the type of data that I'm working with:


USE TestDB
GO

CREATE TABLE testtable
(
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
CompanyNumber nvarchar(50),
UserNumber nvarchar(50),
Birthdate datetime,
TestDate datetime,
TestCode nvarchar(50),
Score nvarchar(50),
FailFlag nvarchar(50)
);

INSERT INTO testtable
SELECT
'M101', '123123', '1990-04-22', '2014-07-07', 'TestA', '11', NULL UNION ALL SELECT
'M101', '123123', '1990-04-22', '2014-07-07', 'TestB', '4.1', NULL UNION ALL SELECT
'M101', '123123', '1990-04-22', '2014-07-07', 'TestC', '76', NULL UNION ALL SELECT
'M101', '123123', '1990-04-22', '2014-07-07', 'TestD', '14', NULL UNION ALL SELECT
'M625', '689228', '1984-09-13', '2014-07-07', 'TestA', '11', NULL UNION ALL SELECT
'M625', '689228', '1984-09-13', '2014-07-07', 'TestB', '4.0', NULL UNION ALL SELECT
'M625', '689228', '1984-09-13', '2014-07-07', 'TestC', '80', NULL UNION ALL SELECT
'M625', '689228', '1984-09-13', '2014-07-07', 'TestD', '47', 'Fail' UNION ALL SELECT
'M425', '585629', '1988-06-06', '2014-07-07', 'TestA', '15', 'Fail' UNION ALL SELECT
'M425', '585629', '1988-06-06', '2014-07-07', 'TestB', '4.1', NULL UNION ALL SELECT
'M425', '585629', '1988-06-06', '2014-07-07', 'TestC', '53', NULL UNION ALL SELECT
'M425', '585629', '1988-06-06', '2014-07-07', 'TestD', '36', NULL UNION ALL SELECT
'M829', '282220', '1985-03-13', '2014-07-07', 'TestA', '10', NULL UNION ALL SELECT
'M829', '282220', '1985-03-13', '2014-07-07', 'TestB', '3.8', NULL UNION ALL SELECT
'M829', '282220', '1985-03-13', '2014-07-07', 'TestC', '60', NULL UNION ALL SELECT
'M829', '282220', '1985-03-13', '2014-07-07', 'TestD', '12', NULL



I would like to formulate a SQL query to achieve this result, with one row per ComapnyNumber:

ComapnyNumber UserNumber Birthdate TestDate TestA TestB TestC TestD
------------- ---------- ---------- ---------- ----- ----- ----- -----
M101 123123 1990-04-22 2014-07-07 11 4.1 76 14
M625 689228 1984-09-13 2014-07-07 11 4.0 80 37
M425 585629 1988-06-06 2014-07-07 15 4.1 53 36
M829 282220 1985-03-13 2014-07-07 10 3.8 60 12



I can do a pivot:

SELECT CompanyNumber, TestA, TestB, TestC, TestD
FROM
(SELECT CompanyNumber, TestCode, Score
FROM dbo.testtable) AS S
PIVOT (
max(Score)
for TestCode in ([TestA], [TestB], [TestC], [TestD])
)
AS T


which yields:

CompanyNumber UserNumber Birthdate TestDate TestA TestB TestC TestD
------------- ---------- ----------------------- ----------------------- ----- ----- ----- -----
M101 123123 1990-04-22 00:00:00.000 2014-07-07 00:00:00.000 11 4.1 76 14
M425 585629 1988-06-06 00:00:00.000 2014-07-07 00:00:00.000 15 4.1 53 36
M625 689228 1984-09-13 00:00:00.000 2014-07-07 00:00:00.000 11 4.0 80 47
M829 282220 1985-03-13 00:00:00.000 2014-07-07 00:00:00.000 10 3.8 60 12


Which is what I want, but... there are additional considerations.

I need to exclude cases where TestA, TestB or TestC have the FailFlag set to 'Fail', but include all TestD results, whether NULL or 'Fail'

They are actually a battery of tests and I need to return the results of the battery.
So, when TestA, TestB or TestC have the FailFlag set to 'Fail' - I want to exclude the results for each test in the battery.

With the data above, I would want this result, because TestA had failed for CompanyNumber M425.

ComapnyNumber UserNumber Birthdate TestDate TestA TestB TestC TestD
------------- ---------- ---------- ---------- ----- ----- ----- -----
M101 123123 1990-04-22 2014-07-07 11 4.1 76 14
M625 689228 1984-09-13 2014-07-07 11 4.0 80 37
M829 282220 1985-03-13 2014-07-07 10 3.8 60 12



If I do this Pivot:

SELECT CompanyNumber, UserNumber, Birthdate, TestDate, FailFlag, TestA, TestB, TestC, TestD
FROM
(SELECT CompanyNumber, UserNumber, Birthdate, TestDate, FailFlag, TestCode, Score
FROM dbo.testtable) AS S
PIVOT (
max(Score)
for TestCode in ([TestA], [TestB], [TestC], [TestD])
)
AS T


I get this:

CompanyNumber UserNumber Birthdate TestDate FailFlag TestA TestB TestC TestD
------------- ---------- ----------------------- ----------------------- -------- ----- ----- ----- -----
M101 123123 1990-04-22 00:00:00.000 2014-07-07 00:00:00.000 NULL 11 4.1 76 14
M425 585629 1988-06-06 00:00:00.000 2014-07-07 00:00:00.000 NULL NULL 4.1 53 36
M425 585629 1988-06-06 00:00:00.000 2014-07-07 00:00:00.000 Fail 15 NULL NULL NULL
M625 689228 1984-09-13 00:00:00.000 2014-07-07 00:00:00.000 NULL 11 4.0 80 NULL
M625 689228 1984-09-13 00:00:00.000 2014-07-07 00:00:00.000 Fail NULL NULL NULL 47
M829 282220 1985-03-13 00:00:00.000 2014-07-07 00:00:00.000 NULL 10 3.8 60 12



I'm not sure where to go from here, or even if I took a good approach to the problem.
Any advice is most appreciated!
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16633 Visits: 19098
I came up with 2 solutions, but I changed the pivot to a cross tabs approach. I prefer cross tabs because they give more flexibility and with more than one pivot, you get better performance.


WITH CTE AS(
SELECT CompanyNumber,
UserNumber,
Birthdate,
TestDate,
MAX(CASE WHEN TestCode = 'TestA' AND ISNULL( FailFlag, '') <> 'Fail' THEN Score END) TestA,
MAX(CASE WHEN TestCode = 'TestB' AND ISNULL( FailFlag, '') <> 'Fail' THEN Score END) TestB,
MAX(CASE WHEN TestCode = 'TestC' AND ISNULL( FailFlag, '') <> 'Fail' THEN Score END) TestC,
MAX(CASE WHEN TestCode = 'TestD' THEN Score END) TestD
FROM testtable
GROUP BY CompanyNumber,
UserNumber,
Birthdate,
TestDate
)
SELECT *
FROM CTE
WHERE TestA IS NOT NULL
AND TestB IS NOT NULL
AND TestC IS NOT NULL

SELECT CompanyNumber,
UserNumber,
Birthdate,
TestDate,
MAX(CASE WHEN TestCode = 'TestA' THEN Score END) TestA,
MAX(CASE WHEN TestCode = 'TestB' THEN Score END) TestB,
MAX(CASE WHEN TestCode = 'TestC' THEN Score END) TestC,
MAX(CASE WHEN TestCode = 'TestD' THEN Score END) TestD
FROM testtable t
WHERE NOT EXISTS( SELECT *
FROM testtable x
WHERE x.CompanyNumber = t.CompanyNumber
AND x.UserNumber = t.UserNumber
AND x.TestDate = t.TestDate
AND (x.FailFlag = 'Fail'AND x.TestCode <> 'TestD'))
GROUP BY CompanyNumber,
UserNumber,
Birthdate,
TestDate




Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
inevercheckthis2002
inevercheckthis2002
SSC-Enthusiastic
SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)SSC-Enthusiastic (173 reputation)

Group: General Forum Members
Points: 173 Visits: 474
Thanks.
..and by that I mean: "thank you very much!"

Who are you and why are you so helpful???
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16633 Visits: 19098
I'm Batman shhh.... No, really, I'm a billionaire and philanthropist called Bruce Wayne. :-D

I just found that helping in here keeps me learning and practicing to become better each day. And I like to pay it forward, I've learned a lot in this site.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Ed Wagner
Ed Wagner
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16691 Visits: 10070
Luis Cazares (7/9/2014)

I just found that helping in here keeps me learning and practicing to become better each day. And I like to pay it forward, I've learned a lot in this site.

Well said, Luis.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search