October 3, 2011 at 1:49 am
Hi,
I would like some help creating a T-sql statement that can produce the following results:
select r.Request, r.city, s.Service, c.Complaint
from request r inner join service s on r.requestid = s.requestid
inner join complain c on r.requestid = c.requestid
Request, city, Service, Complaint
123 - Arcadia - abc - def
123 - New Hall - abc - xyz
123 - Sacramento - mno - xyz
desired results:
Request, city, Service, Complaint
123 - Arcadia - abc - def
123 - New Hall - - xyz
123 - Sacramento - mno -
Thanks in advance!
October 3, 2011 at 1:21 pm
This is more of a display issue and would be easier handled in a UI like reporting services that allows you to hide duplicates. T-SQL is really designed to return results, not format them.
The first thing you'd have to know is what is the ordering as you haven't defined that. Then you'd probably want to store the data in a temporary table and then delete the data that is duplicated in the next row.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 3, 2011 at 3:17 pm
I understand that this is definitely better served using something like Crystal Reports. I know that in SQL 2005 it is much easier to do since the creation of functions.
I want to come up with a T-SQL solution that can be easily imported into Excel for a different kind of analysis. Any help would be appreciated.
Thanks!
October 4, 2011 at 12:07 am
Here is the problem, your query has no order by so that your result set, before eliminating dups, instead of looking like this:
Request, city, Service, Complaint
123 - Arcadia - abc - def
123 - New Hall - abc - xyz
123 - Sacramento - mno - xyz
it could look like this:
Request, city, Service, Complaint
123 - New Hall - abc - xyz
123 - Sacramento - mno - xyz
123 - Arcadia - abc - def
Again, this would most likely be handled better in a UI than SQL. If you insist in doing it using SQL then you need to help us help you. First, we will need the DDL for your tables, sample data for the tables, expected results based on the sample data.
Please read the first article I reference below in my signature block regarding asking for help on how you should post the information requested. Be sure to let us know of any ordering requirements for the data as well.
October 4, 2011 at 6:29 pm
I am posting the tsql solution to the problem I am having using SQL Server 2008; however, I am having a difficult time recreating it in 2K because the lack of functions in 2K.
Hope this helps to illustrate the issue better. Thanks!
--Creates sample table1 #name
select 1 nameid, 'john' name
into #name
union select 2, 'mary'
union select 3, 'tom'
--Creates sample table2 #anote
select 1 tabID, 1 nameid, 'a first note' noteis
INTO #anote
union select 2, 1, 'a second note'
union select 3, 2, 'a 1 note'
--Creates sample table3 #bnote
select 1 tabID, 2 nameid, 'b 1 note' noteis
INTO #bnote
union select 2, 2, 'b 2 note'
union select 3, 1, 'b first note'
union select 4, 3, 'b tom note'
-- List the notes if only left outer join is used.
SELECT
n.name,
a.noteis,
b.noteis
FROM #name n
LEFT OUTER JOIN #anote a
ON n.nameid = a.nameid
LEFT OUTER JOIN #bnote b
ON n.nameid = b.nameid;
-- Utilize the ROW_NUMBER() function in SQL 2005 or later to get what I want in SQL 2K.
WITH fNote(nameid, noteis, ROW_NUM) AS
(SELECT nameid, noteis, ROW_NUM = ROW_NUMBER() OVER (PARTITION BY nameid ORDER BY tabID)
FROM #anote
),
sNote (nameid, noteis, ROW_NUM) AS
(SELECT nameid, noteis, ROW_NUM = ROW_NUMBER() OVER (PARTITION BY nameid ORDER BY tabID)
from #bnote )
SELECT
n.name,
note_from_first = ISNULL(f.noteis, ''),
note_from_second = ISNULL(s.noteis, '')
FROM (SELECT nameid, ROW_NUM FROM fNote -- Create a master table that includes all possible ROW_NUM.
UNION
SELECT nameid, ROW_NUM FROM sNote
) m
JOIN #namen
ON m.nameid = n.nameid
LEFT OUTER JOIN fNote f
ON n.nameid = f.nameid
AND m.ROW_NUM = f.ROW_NUM
LEFT OUTER JOIN sNote s
ON n.nameid = s.nameid
AND m.ROW_NUM = s.ROW_NUM;
October 5, 2011 at 8:47 am
I want to come up with a T-SQL solution that can be easily imported into Excel for a different kind of analysis. Any help would be appreciated.
I would like to mention that returning empty cells to Excel generally makes analysis more difficult due to sorting problems and the inability to use the columns for pivot tables.
--
JimFive
October 5, 2011 at 11:10 am
Hi,
I have posted a solution to my original question using sql 2008. Any help converting it to sql 2k would be greatly appreciated. The sample should illustrate the problem better.
Thanks!
October 21, 2011 at 2:24 am
There are some questions about your original example.
1. SELECT goes like this:
select r.Request, r.city, ...
from request r ...
which means these 2 values come from the same record.
But in your data sample you indicate this:
Request, city, ...
123 - Arcadia - ...
123 - New Hall - ...
How is it possible? Are these 2 different request?
2. What if same request is linked to 2 or more Services and 2 or more Complaints?
Is there any logic in which service to be paired with which complaint?
Or pairs s.Service, c.Complaint are meant to be totally random?
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply