May 26, 2011 at 2:25 pm
Hi all, I am in a strange situation.
Table1 --> Please see below few records (fields separated by -)
1. Name1 - Dec 2011- Audittype1 - SourceCode1 - Value
2. Name2 - Dec 2011- Audittype1 - SourceCode1 - Value
3. Name2 - Dec 2011- Audittype2 - SourceCode2 - Value
4. Name3 - Dec 2011- Audittype1 - SourceCode1 - Value
5. Name3 - Dec 2011- Audittype2 - SourceCode2 - Value
When I query, I want the resultset to be shown like below
1. Name1 - Dec 2011- Audittype1 - SourceCode1 - Value
2. Name1 - Dec 2011- Audittype2 - SourceCode2 - NULL (This row should be inserted because Name1 did not have AuditType2 and SourceCode2 entry in the original set)
3. Name2 - Dec 2011- Audittype1 - SourceCode1 - Value
4. Name2 - Dec 2011- Audittype2 - SourceCode2 - Value
5. Name3 - Dec 2011- Audittype1 - SourceCode1 - Value
6. Name3 - Dec 2011- Audittype2 - SourceCode2 - Value
How can I address this? Either by SQL query or in the report. Any help is appreciated. Hope the above is clear enough, if not, please feel free to ask me.
Thanks
May 27, 2011 at 4:01 am
Please find the work around which is using Table variables and CTE:-
Hope this will give you the idea what are you looking for.
Create Table TestTable1
(srNo int not null identity(1,1),
EName varchar(10),
DT varchar(10),
AuditType varchar(20),
SourceType varchar(20),
Val varchar(20)
)
Insert into TestTable1 (EName,DT,AuditType,SourceType,Val)
values('Name1','Dec 2011','Audittype1','SourceCode1','Value')
Insert into TestTable1 (EName,DT,AuditType,SourceType,Val)
values('Name2','Dec 2011','Audittype1','SourceCode1','Value')
Insert into TestTable1 (EName,DT,AuditType,SourceType,Val)
values('Name2','Dec 2011','Audittype2','SourceCode2','Value')
Insert into TestTable1 (EName,DT,AuditType,SourceType,Val)
values('Name3','Dec 2011','Audittype1','SourceCode1','Value')
Insert into TestTable1 (EName,DT,AuditType,SourceType,Val)
values('Name3','Dec 2011','Audittype2','SourceCode2','Value')
Select * from TestTable1
srNo EName DT AuditType SourceType Val
----- ---------- ---------- ----------- ----------- -------
1 Name1 Dec 2011 Audittype1 SourceCode1 Value
2 Name2 Dec 2011 Audittype1 SourceCode1 Value
3 Name2 Dec 2011 Audittype2 SourceCode2 Value
4 Name3 Dec 2011 Audittype1 SourceCode1 Value
5 Name3 Dec 2011 Audittype2 SourceCode2 Value
---------------------------------------------------
Declare @Name as Table(EName varchar(20))
Insert into @Name
Select distinct(EName) from TestTable1
Declare @Atype as Table(AuditType varchar(20))
Insert into @Atype
Select distinct(AuditType) from TestTable1;
With CTE (EName,AuditType) AS
(
Select * from @Name cross join @Atype
)
Select C.ENAme,A.DT,C.AuditType,A.SourceType,A.val
from TestTable1 as A right outer join CTE As C
ON A.Ename=C.EName
and C.Audittype =A.Audittype
--------------------------------------
ENAme DT AuditType SourceType val
------- ---------- ---------- ------------ -----
Name1 Dec 2011 Audittype1 SourceCode1 Value
Name1 NULL Audittype2 NULL NULL
Name2 Dec 2011 Audittype1 SourceCode1 Value
Name2 Dec 2011 Audittype2 SourceCode2 Value
Name3 Dec 2011 Audittype1 SourceCode1 Value
Name3 Dec 2011 Audittype2 SourceCode2 Value
May 27, 2011 at 4:24 am
Send Table Definitions is there FK and AuditType is another table ?
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 31, 2011 at 8:02 am
Thanks all. I managed to use left join with the "Master" table. I got it to work.
Once again thanks for the help.
September 8, 2011 at 9:58 am
I lost the code due to db refresh. Now I have to start all over again and worst part is I don't remember how I fixed this. Dang. Oh yeh, I don't have a backup.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply