Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Pivot help? ...or other approach? Expand / Collapse
Author
Message
Posted Tuesday, July 8, 2014 1:53 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:13 AM
Points: 80, Visits: 341
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)
);


Post #1590497
Posted Tuesday, July 8, 2014 2:01 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:29 PM
Points: 3,513, Visits: 7,565
And what do you need?


Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1590506
Posted Tuesday, July 8, 2014 3:27 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:13 AM
Points: 80, Visits: 341
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!
Post #1590549
Posted Tuesday, July 8, 2014 4:05 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:29 PM
Points: 3,513, Visits: 7,565
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1590553
Posted Wednesday, July 9, 2014 6:35 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 9:13 AM
Points: 80, Visits: 341
Thanks.
..and by that I mean: "thank you very much!"

Who are you and why are you so helpful???

Post #1590703
Posted Wednesday, July 9, 2014 8:59 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:29 PM
Points: 3,513, Visits: 7,565
I'm Batman shhh.... No, really, I'm a billionaire and philanthropist called Bruce Wayne.

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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1590799
Posted Wednesday, July 9, 2014 9:06 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:43 PM
Points: 4,171, Visits: 3,201
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
Post #1590805
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse