[SQL Server] Selecting/Deleting rows with the same column information

---

• Selecting/Deleting rows with the same column information
• Dynamic Pivot Table
• How to merge two sql select statement results I tried it but it not get please see it in detailed explination with my query
• Problem with joining two select query statements.

Selecting/Deleting rows with the same column information Posted: 26 Apr 2013 07:38 AM PDT I need to delete some rows from our system that have the same transaction id based on an accounting date. Our primary key for the table is MtrNo, MtrSfx, TrnxID, RecCode. Here's some example data:ProdDate | AcctDate | MtrNo |MtrSfx | TrnxID | RecCode------------------------------------------------------2/1/2011 | 8/1/2011 | 365 |A | 8301 | RR2/1/2011 | 2/1/2013 | 365 |A | 8301 | RR2/1/2011 | 2/1/2011 | 365 |A | 8301 | OR3/1/2011 | 9/1/2011 | 365 |A | 8302 | RR3/1/2011 | 3/1/2013 | 365 |A | 8302 | RR3/1/2011 | 3/1/2011 | 365 |A | 8302 | ORI want to be able to delete all the most recent rows based on AcctDate that have an RR RecCode. So in the above table, I'd want to delete:2/1/2011 | 2/1/2013 | 365 |A | 8301 | RR3/1/2011 | 3/1/2013 | 365 |A | 8302 | RRIt seems to be simple but I'm not very good with T-SQL. I would assume I need a sub-query that uses max, group by, and count.Would something like the query below work?DELETE FROM TblWHERE (SELECT MtrNo, MtrSfx, TrnxID, RecCode, MAX(AcctDate)FROM TblWHERE RecCode = 'RR'GROUP BY MtrNo, MtrSfx, TrnxID, RecCodeHAVING COUNT(TrnxID) > 1)The select seems to give me the right results but I don't know how the delete statement works and if it would just delete the columns I bring back.Any help would be greatly appreciated!

Dynamic Pivot Table Posted: 26 Apr 2013 05:37 AM PDT [img]http://farm9.staticflickr.com/8546/8684300282_1697647437_o.png[/img]I'm trying to pivot the data so that all of the PostClickConversions are aggregated for each ActivityTagID (by Date & AdID) as a column so the result set will look like this:[img]http://farm9.staticflickr.com/8545/8683197697_c30fdbba0b_o.png[/img]My attempt:[quote] DECLARE @PivotColumnHeaders VARCHAR(MAX) SELECT @PivotColumnHeaders = COALESCE( @PivotColumnHeaders + ',[' + cast([ActivityTagID] as varchar) + ']', '[' + cast([ActivityTagID] as varchar)+ ']' )FROM dbo.ConversionsTest DECLARE @PivotTableSQL NVARCHAR(MAX) SET @PivotTableSQL = N' SELECT * FROM ( select [date],[AdId],[ActivityTagID],[PostClickConversions]from dbo.ConversionsTest ) AS PivotData PIVOT ( sum([PostClickConversions]) FOR [ActivityTagID] IN ( ' + @PivotColumnHeaders + ' ) ) AS PivotTable ' EXECUTE(@PivotTableSQL)[/quote]The error message yields:[quote]Msg 8156, Level 16, State 1, Line 14The column '280378' was specified multiple times for 'PivotTable'.[/quote]HELP!!:w00t:

How to merge two sql select statement results I tried it but it not get please see it in detailed explination with my query Posted: 25 Apr 2013 07:14 PM PDT Hi All,My Query Goes Like This, Select TT.ID,TT.NAME,ROUND((CAST((Sum(TJA.MINS)) as FLOAT)/60),0) AS HOURS From MYTABLE1 as TT INNER JOIN MYTABLE2 as TB On TT.ID=TB.ID INNER JOIN MYTABLE3 as TH On TB.BID=TH.BID INNER JOIN MYTABLE4 as TJA On TJA.HID=TH.HID Where TJA.JID=41 group by TT.ID,TT.NAME UNION Select TJA.ID,TT.NAME,ROUND((CAST((Sum(TJA.MINS)) as FLOAT)/60),0) AS HOURS From MYTABLE1 as TT INNER JOIN MYTABLE2 as TJA On TT.ID=TJA.ID wHERE TJA.JID=41 group by TJA.ID,TT.NAMEThe First SQL Statement Result was like below one,ID NAME HOURS1 AAA 02 BBB 103 CCC 04 DDD 0The Second SQL Statement Result was like below one,ID NAME HOURS1 AAA 202 BBB 03 CCC 04 DDD 0After writing the The above UNION Statement i get like below one ID NAME HOURS1 AAA 01 AAA 202 BBB 02 BBB 103 CCC 04 DDD 0It was wrong I want to get result like below one ID NAME HOURS1 AAA 202 BBB 103 CCC 04 DDD 0Please give solution to me Thanks In Advance,VenkiDesai.

Problem with joining two select query statements. Posted: 25 Apr 2013 05:00 PM PDT Hi All,I Have Two Select Query Statement Result sets I want to combine those two sets.The Below One Is my [b]first query resultant set[/b],ID NAME HOURS1 AAA 02 BBB 103 CCC 04 DDD 0The Below One Is my [b]Second query resultant set[/b],ID NAME HOURS1 AAA 202 BBB 03 CCC 04 DDD 0After Joining two results i.e., My First Query & Second Query i want to get like below one,ID NAME HOURS1 AAA 202 BBB 103 CCC 04 DDD 0But i am not getting the result.After Joining two results i am getting the final one like below but it was wrongID NAME HOURS1 AAA 01 AAA 202 BBB 02 BBB 103 CCC 04 DDD 0For the Joining two select queries am using Below Query Select TT.ID,TT.NAME,ROUND((CAST((Sum(TJA.MINS)) as FLOAT)/60),0) AS HOURS From MYTABLE1 as TT INNER JOIN MYTABLE2 as TB On TT.ID=TB.ID INNER JOIN MYTABLE3 as TH On TB.BID=TH.BID INNER JOIN MYTABLE4 as TJA On TJA.HID=TH.HID Where TJA.JID=41 group by TT.ID,TT.NAME UNION Select TJA.ID,TT.NAME,ROUND((CAST((Sum(TJA.MINS)) as FLOAT)/60),0) AS HOURS From MYTABLE1 as TT INNER JOIN MYTABLE2 as TJA On TT.ID=TJA.ID wHERE TJA.JID=41 group by TJA.ID,TT.NAMEPlease give the solution for me.Thanks In Advance,Venki Desai.

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server Newbies To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610