Select Query- How to avoid the data duplication? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Query- How to avoid the data duplication?

Author

Topic

paramu
Posting Yak Master

151 Posts

Posted - 2010-05-31 : 05:28:28

I have a following query, selecting the datas from tables, correct manner but the problem is duplication with rest of fields as per the previous conditions of datas availability.

My Query:-

Select i.itm_code,i.itm_description,i.unit_measure,i.proj_code,i.opg_stk_qty,null as pj_avail_qy,g.grn_no,g.grn_date,mt.mt_no as mtgrn_no,mt.mt_date as mtgrn_date,k.iss_no,k.iss_date,mi.mt_no,mi.mt_date,null as pj_bln_qy,je.depart_name from Purchase_Ledger..Item_Master i
left join goods g on g.itm_code=i.itm_code and g.fm_proj_code in( select j.proj_code from Project_Ledger..Project_Master j where j.depart_name='PROCUREMENT H.Q') and g.grn_date>='2010/05/01' and g.grn_date<='2010/05/31'
left join Material_Transfer mt on mt.itm_code=i.itm_code and mt.proj_code in( select j.proj_code from Project_Ledger..Project_Master j where j.depart_name='PROCUREMENT H.Q') and mt.mt_date>='2010/05/01' and mt.mt_date<='2010/05/31'
left join Issue_Master k on k.itm_code=i.itm_code and k.proj_code in( select j.proj_code from Project_Ledger..Project_Master j where j.depart_name='PROCUREMENT H.Q') and k.iss_date>='2010/05/01' and k.iss_date<='2010/05/31'
left join Material_Transfer mi on mi.itm_code=i.itm_code and mi.fm_proj_code in( select j.proj_code from Project_Ledger..Project_Master j where j.depart_name='PROCUREMENT H.Q') and mi.mt_date>='2010/05/01' and mi.mt_date<='2010/05/31'
left join Project_Ledger..Project_Master je on je.proj_code=i.proj_code order by i.itm_description,LEN(i.itm_description)

In Details : The above query gives the result as like the below.
---------------------------------------------------------------------Itm_code,Itm_description,unit_measure,proj_code,opg_stk_qty,pj_avail_qy,grn_no,grn_date,mtgrn_no,mtgrn_date,iss_no,iss_date,mt_no,mt_date,pj_bln_qy,depart_name
---------------------------------------------------------------------
JUG1 JUG NOS 50.000 0 5 03/05/2010 163 13/05/2010 1 25/05/2010 163 13/05/2010
JUG1 JUG NOS 50.000 0 5 03/05/2010 163 13/05/2010 1 25/05/2010 164 26/05/2010
JUG1 JUG NOS 50.000 0 5 03/05/2010 163 13/05/2010 1 25/05/2010 165 30/05/2010

But It has to be like the following manner

JUG1 JUG NOS 50.000 0 5 03/05/2010 163 13/05/2010 1 25/05/2010 163 13/05/2010
----------------------------------------------------------------- 164 26/05/2010
----------------------------------------------------------------- 165 30/05/2010

Hence how to alter the above query to get like this manner
Any Ideas will be appreciated…

Thanks

Paramu @ PARANTHAMAN

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-31 : 05:48:06

You should do that in your front end because that is nothing T-SQL was made for.

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-05-31 : 06:11:25

You could do that, but I agree with Fred doing it in front end is better.

You can INSERT the results into temporary table, with additional IDENTITY column, and then do


SELECT CASE WHEN T_This.Itm_code = T_Previous.Itm_code
            THEN '----'
            ELSE T_This.Itm_code    -- Needs CONVERT if not Varchar datatype
            END AS [Itm_code]
FROM #TEMP AS T_This
     LEFT OUTER JOIN #TEMP AS T_Previous
         ON T_Previous.ID = T_This.ID-1
ORDER BY T_This.ID

but that means that all columns have to be converted to VARCHAR which means that no downstream process has access to the original datatype.

Another way would be to force the values to NULL if they are the same as previous row. That retained datatype, but assumes that front-end displays "blank" or somesuch. Also means you can no longer distinguish between NULL and "repeated value"


SELECT NullIf(T_This.Itm_code, T_Previous.Itm_code) AS [Itm_code]
FROM #TEMP AS T_This
     LEFT OUTER JOIN #TEMP AS T_Previous
         ON T_Previous.ID = T_This.ID-1
ORDER BY T_This.ID

paramu
Posting Yak Master

151 Posts

Posted - 2010-05-31 : 07:59:19

Thanks..It helps me....[both]

Paramu @ PARANTHAMAN

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources