If you use the grouping() functions, you can do this without the temp table.

SELECT

CASE WHEN Grouping(ID2) = 1 THEN ID END AS ID

, CASE WHEN Grouping(Val) =...

It's probably easier to change your aggregate to a distinct count.

Count(DISTINCT Staff_Seq_Num)

You may also need to use another field if that doesn't give you the correct counts.

Drew

Rob Titerle (8/4/2011)

---

This is a fundamentally simple problem, but I don't see what is likely the obvious solution. Any help pointing out the obvious to me would be greatly...

I assume that you actually want where the name attribute is "FirstAttributeName" since there is nothing named "FirstAttribute" in your sample.

This should give you what you want.

SELECT per.value('(Data/item[@name="FirstAttributeName"])[1]', 'varchar(50)') AS...

DateDiff will always produce an integer result. You need to move your addition outside of the DateDiff function.

CASE Type WHEN 'HALF' THEN DATEDIFF (d, StartDate, EndDate) + 0.5

You're thinking...

In its most basic form, think of it as AGGREGATE FACT BY DIMENSION....

In most cases where there is no obvious aggregation, you'll want to use Min() or Max(). If there is only one value corresponding to each pivot value, they'll give...

I would amend this to say that it works on the...

mfink (7/26/2011)

---

A completed version of the problem and solution

SELECT

c.value('(//AnalyticsReport/Report/Title/ProfileName)[1]','varchar(100)') as WebPage,

c.value('(PrimaryKey)[1]', 'varchar(30)') AS Date

, c.value('(DrilldownId)[2]', 'varchar(25)') AS Region

, c.value('(Cell/Content/Value)[1]', 'int') AS Counts

FROM @x.nodes('/AnalyticsReport/Report/Table/Row')...

All of the valid formats require some kind of separator between the various components except for 112 which doesn't include the time components. Your conversion is failing, because your...

Since we don't have information on your base tables or your views, it's hard to say what the best approach might be. If your views are simple selects on...

If you're going to be working with XML, you need to learn XPath. There is a tutorial at W3Schools http://www.w3schools.com/xpath/. XPath is very easy.

Drew

What's the current node?

What's the relative path from the current node to ProfileName?

You're using...

See if you can figure it out for yourself. You just need to determine the relative path from your current...

You can't have any whitespace before the <?xml version="1.0" ?>.

Drew