January 23, 2013 at 12:41 pm
Hi
I was given a T-SQL assigment that at first seemed trivial until I took a closer look to it and now I am stuck. I need to write a query that looks at two or more tables and returns a list of records with the records from the other tables as column with the resulting tables. Below is a more visual explanation of what I am looking for:
Contact table:
ContactID Name
--------------------- --------------
1 John Doe
2 Jane Doe
Incident table:
IncidentID ContactID ProgramName
-------------------- ----------------- ---------------
1 1 ProgramOne
2 1 ProgramTwo
3 2 ProgramOne
Outcome table:
OutcomeID IncidentID Date Amount
------------------ ------------------- ---------------------- ---------------
1 1 1/2/2011 100
2 1 1/6/2011 200
3 2 2/2/2012 455
4 2 2/5/2012 232
5 3 3/1/2012 151
DesiredResult table:
ContactID Name IncidentID OutcomeID Date Amount OutcomeID Date Amount
--------------------- -------------- -------------- ------------ -------------- ----------- --------------- -------------- -----------
1 John Doe 1 1 1/2/2011 100 2 1/6/2011 200
1 John Doe 2 3 2/2/2012 455 4 2/5/2012 232
2 Jane Doe 2 5 3/1/2012 151 NULL NULL NULL
Please notice that the result is pivoted on the incidentID.
Any help you can provide me is deeply appreciated.
Hector
January 23, 2013 at 1:06 pm
Hey Hector, welcome to the forums.
If you can format your data like you'd see in the first link in my signature, you'll find a number of folks here would be much more apt to give you tested code to work with. Right now we'd have to turn that into a sample set ourselves and that's more time then a lot of us volunteers are able to spend on some things.
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 23, 2013 at 1:30 pm
Thanks checking out my question. Can you make out the tables now?
Hector
January 23, 2013 at 3:41 pm
sanchez.hector.m (1/23/2013)
Thanks checking out my question. Can you make out the tables now?Hector
I see you didn't bother to read the link. The data is non-consumable in a test format for our own systems to provide code against. We'd have to turn them into INSERT statements and the like.
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, 2013 at 6:30 pm
What Evil Kraig is trying to explain to you is that to get better, quicker help on this forum you need to provide DDL and sample data in consumable form, like the following:
CREATE TABLE #Contacts
(ContactID INT, Name VARCHAR(100))
INSERT INTO #Contacts
SELECT 1, 'John Doe' UNION ALL SELECT 2, 'Jane Doe'
CREATE TABLE #Incidents
(IncidentID INT, ContactID INT, ProgramName VARCHAR(100))
INSERT INTO #Incidents
SELECT 1, 1, 'ProgramOne' UNION ALL SELECT 2, 1, 'ProgramTwo' UNION ALL SELECT 3, 2, 'ProgramOne'
CREATE TABLE #Outcomes
(OutcomeID INT, IncidentID INT, [Date] DATE, Amount INT)
INSERT INTO #Outcomes
SELECT 1, 1, '1/2/2011', 100
UNION ALL SELECT 2, 1, '1/6/2011', 200
UNION ALL SELECT 3, 2, '2/2/2012', 455
UNION ALL SELECT 4, 2, '2/5/2012', 232
UNION ALL SELECT 5, 3, '3/1/2012', 151
With that, there are a couple of ways to address this:
;WITH JoinedTables AS (
SELECT a.ContactID, Name, a.IncidentID, OutcomeID, [Date], Amount
,rn=ROW_NUMBER() OVER (PARTITION BY a.IncidentID ORDER BY c.OutcomeID)
FROM #Incidents a
INNER JOIN #Contacts b ON a.ContactID = b.ContactID
INNER JOIN #Outcomes c ON a.IncidentID = c.IncidentID
)
SELECT ContactID, Name, IncidentID
,OutcomeID=MAX(CASE WHEN rn=1 THEN OutcomeID END)
,[Date]=MAX(CASE WHEN rn=1 THEN [Date] END)
,Amount=MAX(CASE WHEN rn=1 THEN Amount END)
,OutcomeID=MAX(CASE WHEN rn=2 THEN OutcomeID END)
,[Date]=MAX(CASE WHEN rn=2 THEN [Date] END)
,Amount=MAX(CASE WHEN rn=2 THEN Amount END)
FROM JoinedTables
GROUP BY ContactID, Name, IncidentID
-- Or with Dynamic SQL if possibly more than 2 OutcomeIDs
DECLARE @SQL NVARCHAR(MAX) =
';WITH JoinedTables AS (
SELECT a.ContactID, Name, a.IncidentID, OutcomeID, [Date], Amount
,rn=ROW_NUMBER() OVER (PARTITION BY a.IncidentID ORDER BY c.OutcomeID)
FROM #Incidents a
INNER JOIN #Contacts b ON a.ContactID = b.ContactID
INNER JOIN #Outcomes c ON a.IncidentID = c.IncidentID
)
SELECT ContactID, Name, IncidentID'
,@SQL1 NVARCHAR(MAX) = ''
;WITH JoinedTables AS (
SELECT a.ContactID, Name, a.IncidentID, OutcomeID, [Date], Amount
,rn=ROW_NUMBER() OVER (PARTITION BY a.IncidentID ORDER BY c.OutcomeID)
FROM #Incidents a
INNER JOIN #Contacts b ON a.ContactID = b.ContactID
INNER JOIN #Outcomes c ON a.IncidentID = c.IncidentID
),
MaxOutcomes AS (
SELECT m=MAX(Outcomes)
FROM (
SELECT Outcomes=COUNT(*)
FROM JoinedTables
GROUP BY ContactID, Name, IncidentID) a),
Tally (n) AS (
SELECT TOP (SELECT m FROM MaxOutcomes)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns)
SELECT @SQL1 = @SQL1 +
',OutcomeID=MAX(CASE WHEN rn=' + CAST(n AS VARCHAR) + ' THEN OutcomeID END) ' +
',[Date]=MAX(CASE WHEN rn=' + CAST(n AS VARCHAR) + ' THEN [Date] END) ' +
',Amount=MAX(CASE WHEN rn=' + CAST(n AS VARCHAR) + ' THEN Amount END) '
FROM Tally
SELECT @SQL = @SQL + @SQL1 + '
FROM JoinedTables
GROUP BY ContactID, Name, IncidentID'
EXEC (@SQL)
DROP TABLE #Outcomes
DROP TABLE #Incidents
DROP TABLE #Contacts
The Dynamic SQL version would be simplified if you had the JoinedTables code encapsulated in a VIEW.
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply