Monthly Archives: January 2008

One-to-many comma separated with ‘FOR XML PATH’ in TSQL/2005

original post: One-to-many comma separated We have 2 tables one-to-many. How can we fetch parent table field, and the second field is its children comma separated ? In MSSQL 2000 we could use the following function. But in MSSQL 2005 … Continue reading

Posted in Uncategorized | Leave a comment

Simple Pivot in TSQL (SQL Server 2000)

read original post: Pivot in mssql2000 select    t.myid as furnisher,   t1.myprice as price1_year,   t2.myprice price2_year from (select distinct myid from mytable) t join (select myid,myprice,rownum = (select count(*) from mytable t2 where t2.myid = t1.myid and t2.id <= t1.id) from mytable … Continue reading

Posted in Uncategorized | 3 Comments

Matrix transformation in SQL

  Quote Matrix transformation in SQL Ok, this is a blog entry that I am going to preface with a warning.  This is best done in the user interface and not using the crazy SQL that I am going to … Continue reading

Posted in Uncategorized | Leave a comment

Looking at locks being held

  Quote Looking at locks being held I was looking at locks that were being held by a query today to try to help solve a locking problem so I went hunting in the dynamic management views.  Very nice stuff … Continue reading

Posted in Uncategorized | Leave a comment

Seeing progress of rollbacks

  Quote Seeing progress of rollbacks I was working on a procedure today that was taking hours to run due to a far larger amount of data than expected, so I kept stopping the query.  In 2000 you could see … Continue reading

Posted in Uncategorized | Leave a comment

Seeing the currently executing statement

  Quote Seeing the currently executing statement  I tried to sit down and cover every piece of each system table, and I just realized that that would be nuts.  There are so many neat things in these objects, and honestly … Continue reading

Posted in Uncategorized | Leave a comment

Using JOIN or EXISTS?

  Quote Using JOIN or EXISTS? A common thing I see is the question:  "How do I find parent rows that have no children?"  There are three answers commonly given, and while all of them work, only two of them … Continue reading

Posted in Uncategorized | Leave a comment