Everything was working fine but got the requirement change.Idea is:

a. base table can have multiple values of ID based on different books but can have only two values for object...

Cool. Good one to know though I might use for temporary development only. 🙂

Coll. Congratulations to all the winners for their contribution to the SQL society. 🙂

george sibbald (1/2/2014)

---

Thomas Abraham (1/2/2014)

---

Got it wrong as I was using http://technet.microsoft.com/en-us/library/ms176118.aspx as a reference and it says, in part:

If a clustered index is dropped by using MOVE TO, any...

Hi Sean/Kurt, Highly appreciate your help on this. It was a good query. However some conditions changed and there was some extra requirements and I lost focus in between so...

I'm getting the required data from the following query.

WITH cteMaster AS(

SELECT *,

ROW_NUMBER() OVER(PARTITION BY ID, Book, Party ORDER BY [Type]) rn

FROM #Master

WHERE [Type] IN ('ABC', 'DEF')

),

ctenew AS(

SELECT * FROM cteMaster

where...

Luis Cazares (1/6/2014)

---

sqlnaive (1/6/2014)

---

It's basically for OLAP environment so no worruies about using nolock. Your query seems good. But what do you think about the performance from the bulk (which...

There seems to be a catch in your query Luiz.

Say in any case the data does not have any Attribute at all (say Attr2 in our example). In this...

Luiz, One thing to add here. The master table has around 2 billion records and Details have around 3 billion records.

How will be the performance of your query ?

It's basically for OLAP environment so no worruies about using nolock. Your query seems good. But what do you think about the performance from the bulk (which i mentioned) perspective...

Thanks for this query Luiz. Ket me check this out.

Hi Chris, so far i have written this query:

;with cte_1

as(SELECT ID, Book, Party, count(*) as SumCount FROM table_master nolock

GROUP BY ID, Book, Party having count(*) = 1)

insert into table_final

select tm.ID,...

+10 🙂

Happy 2014...

I'll try using blank value rather than NULL in txt file.

Nice question on 2014. Learnt something today about the new version. Thank you Dr. Diana. 🙂