Monthly Archives: November 2009

Output xml and relational data in OPENXML

This question arose from a post in SQL server forum. Due to performance reason, it is required to use OPENXML to process a big xml documnet. But it also needs to return a specific xml document for each record.The source … Continue reading

Posted in XML, XQuery and XPath | Leave a comment

Pivot more than one columns

In a recent T-SQL forum post, an interest question was how to pivot two columns in a table.The raw data is like:[Time],[Type],[Value],[ID]N’21:00′,N’A’,60.24,1N’21:00′,N’B’,20.00,2N’22:00′,N’C’,21.33,3N’22:00′,N’D’,80.00,4 And it was required to pivot [Type] and [ID] as following: Time    A     IDA    B    IDB    C    IDC  … Continue reading

Posted in SQL server 2005 | Leave a comment

Stamp records with consecutive dates

For the following data table, the requirement is to assign/stamp the same unique number to the Group column if EndDate/StartDate is consecutive for the same Medicine.ID    Medicine  StartDate       EndDate        Group1     1              19900601      19940630       02     1              19900712      19940606       03     1              … Continue reading

Posted in SQL server | Leave a comment

Is the length of empty string always 0?

DECLARE @x nvarchar(max), @b varbinary(max), @v sql_variant; SELECT @x=”,@b=cast(” as varbinary(max)),@v=cast(” as sql_variant) — For those variable length data type, the length of empty string always returns 0 SELECT DATALENGTH(@x),DATALENGTH(@b),DATALENGTH(@v); GO — for untyped xml: declare @x xml; — You … Continue reading

Posted in SQL server 2005 | Leave a comment